Showing results for 
Search instead for 
Do you mean 

Using a SQL View in conjunction with Sage SalesLogix Groups #SalesLogix

by bstacy on ‎03-16-2012 09:15 AM

Real World Scenario: You have a need for each of your sales people to be able to identify all of their contacts which have had more than 25 history table entries (you could filter down further by history type if you wished). This is something that can be easily done via SQL query using an aggregate, but the Sage SalesLogix Query builder does not like aggregates. What to do? A properly built SQL View could allow you to easily get at this type of data and allow you to do so on an ongoing basis.

 

The SQL View itself

First the View needs to be created in SQL Management Studio*. Per MSDN, a SQL View can be thought of either as a virtual table or as a stored query. The example statement below will create a very basic view which returns a CONTACTID, and HISTTOTAL value representing the number of history records that exist for the associated CONTACTID calculated via a SQL aggregate function. The SQL used to create the View reads as follows:

 

This View does not have to be added to the Sage SalesLogix schema, via DB Manager for example, in order to reference it in a Group Select statement. This would only be necessary if one wanted the View to be a visible entity in Group Manager and would like to be able to add fields to a group layout, etc. In this example, the View will only be referenced via a SELECT statement within a Group condition.

 

The next step would be to create a new Contact group in the Sage SalesLogix client of your choice (LAN or Web). For the sake of simplicity, ensure that only fields from the Contact table exist in the layout. On the “Conditions” tab, drag the CONTACTID field down to the conditions area, and choose the following:

 

Operator: IN

Value Is: (Select ContactID from ContactHistoryCount where HistTotal >= 25)

 

Once created, this group will return all contact records that the logged user has access to and for which there are at least 25 History records. A more detailed scenario might require that the history records be of a specific type which could be filtered down further within the SELECT statement used to create the View itself.

 

* There are advantages and disadvantages to using SQL Views, it is suggested that you become familiar with both before filling your SalesLogix database with them. As with anything, there are generally accepted Best Practices for working with SQL Views. It is recommended that you take the time to understand these as well.

 

Comments
by Copper Super Contributor TomKaiser
on ‎10-07-2014 09:15 AM

Thanks for the helpful article.

 

Is there anything else that needs to be configured so the SQL view can be used in the layout of a group in SalesLogix web v8.1?

 

I tried the steps above, including creating the SalesLogix schema in the Database Manager (including selection of the key field), and creating a new entity in Application Architect based on the SQL view. I am able to create a local join from Contact.ContactID to myNewView.ContactID, but when I select the table in the Query Builder, the column displays: Sorry, an error occured.

 

Thanks,

 

Tom Kaiser

Register Read Guidelines Request Partner or Employee Access

What's New in 8.1

Labels