Left join in SalesLogix HqlDataSource
Posted By: nicocrm on May 29th, 2012 in Saleslogix
No Gravatar

HqlDataSource is a very useful component when there is a need to customize the query used to populate a grid and you don’t want to go all the way down to a custom smartpart. It is not terribly intuitive to use though and has a few undocumented quirks. One of those is the way that joins are formed. Say you have the following 2 entities:

  • AccountProduct, with a 1-M link from Account to AccountProduct
  • Contact, with an optional M-1 link from AccountProduct to Contact

On the account level you may want to show all assets (AccountProduct records) associated with the account, with the associated contact if there is one (and a complex condition that makes it impossible to just use the 1-M link from Account to AccountProduct to populate the grid – for example, you may want to only display products of a certain family). Intuitively you would use the following columns in your HqlDataSource:

  • Product.Name
  • Contact.LastName

With a where clause such as: Account.Id=:AccountID and Product.Family = 'Nice'

Unfortunately this will cause a query with an inner join from AccountProduct to be sent, something like select c.lastname, p.name from accountproduct ap inner join product p on p.productid=ap.productid inner join contact c on c.contactid=ap.contactid ... – which will of course exclude any accountproduct record that does not have a contactid

The way around it is to play with the “From” property of the HqlDataSource control – this is the type of joins used in the query can be specified. It does require you to specify an alias for every entity used in the query, though! So for the example above, use a “From” clause of “AccountProduct as ap left join ap.Contact as c“, and use the field definitions in the columns:

  • ap.Product.Name (note how you have to now include the “ap” alias, instead of just the property name)
  • c.LastName

Often times the answer to HqlDataSource limitations can be found in the standard HQL documentation, the trick is then to figure out the right combination to get the control to output the correct HQL.


Adding a Custom Filter to SalesLogix Site – 2/100 Pitfalls of using App Architect
Posted By: AdiRawat on May 4th, 2012 in Saleslogix, Tricks
No Gravatar

Continuing my series on 100 Pitfalls of Using Sage Application Architect , today we are going to cover how to land into a pitfall of creating a broken custom Filter in App architect and how to go about fixing it !

Creating a custom filter for Saleslogix in App Architect is pretty simple and straight forward

-Browse to the Entity in App Architect >> Drop Down Filters >> Right Click “New Filter” >> In the Property box Browse and Select the Field you need to create a filter against. (In this case we are trying to create a filter against the Opportunity Products “Family” type)

In regular scenario it seems like, we are done as new filter will show up in the Group List when the site is deployed, but if you test it further the Custom filter breaks for the Groups which has some sort of condition in it.
For instance the custom filter breaks in the “Open Opportunity Group” for this case (group where opportunity Type= “Open”)

The cause of the custom filter breaking; is the DataPath property which is automatically populated by the App Architect, if you notice the stock filters DataPath property is always set to blank ,  and this how it is supposed to be.

Thankfully we can fix this issue by manually editing the source file generated by App Architect (not as bad as it sounds like), to do so:

-Close the App Architect

Browse to the Entity Folder either in your Virtual File System explorer or the Local File System Explorer whichever you use ,  the path is (\Model\Entity Model\SalesLogix Application Entities\<entityname>)  

-open up the CustomFiltername.Filter.xml  , in the XML file you will see property like dataPath=”OpportunityProduct:Product:….” , delete this property and save the file .

-Now if you open up the App Architect and browse to you filter again the DataPath  property will be Blank ,Voila !

Now you must go ahead Rebuild and Deploy   your site .

But….. this not the end of the battle , there is one Caveat to this as soon as you edit the custom filter.xml file you wont be able to View/Use  the filter in your group list view unless you add the Filtered Field to the Layout.
That means whatever group you may need to use this filter for you will need to manually edit the group to include the property used for the custom filter.
Once the field for the filter is added to the Group layout, you to be should be able to View and make use of the filter.

 

 

Now you have successfully created a custom filter in Saleslogix !