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.
