Showing results for 
Search instead for 
Do you mean 

Refining your searches with Query Builder

by valeria_miller on ‎01-17-2011 10:09 AM

When a basic lookup does not return the results you want, you can improve your searches by creating your own groups in Sage SalesLogix. By using the Query Builder, you will be able to refine your criteria by selecting data from multiple tables based on one or more conditions. Any field in the database can be used as filter criteria including both custom and core database fields. Once you saved your group, you will also be able to use this group in many areas throughout the application. For example, you can create dashboards from groups, and perform mail merges, or add group members as targets to your marketing campaigns.

 

To use Query Builder effectively, you must understand the Sage SalesLogix database structure and relational database concepts, such as data relationships and joins. This will come handy when you want to query from multiple areas such as “show me all active customers in Arizona who have purchased Product XYZ in the last 30 days.”  In this case, you will need to understand the structure in the Account table to filter by active customers, the Address table to narrow down results to just AZ customers as well as the Products table for the Products Name and History data. You can create a new group by clicking on the Add Group menu from the Groups area (upper right corner from a list view in the Web Client).  You can define the following tabs:

 

    * Properties tab - Use to define the name and description of the group.

    * Conditions tab - Use to define the conditional statement(s) used to select records.

    * Layout tab – Use to define which data appears in the List view.

    * Sorting tab – Use to define how the data is sorted.

 

Conditions are applied in the order they appear in the grid; however, you can use the Move Up and Move Down buttons to change the order.  It is recommended that all groups you create should contain at least one condition so they do not affect system performance.

 

When you want to refine by date ranges, Sage SalesLogix provides you with operators such as” within last xxx days” or “within next xxx days”. In our example above, we can simply use the operator such as within the last 30 days. But if you wanted to create a group with a specific date range such as “show me all customers in AZ that have purchased Product XYZ between November  14-31, 2010”, you might be a little confused as I was at first. It seemed I could only create one query, either using the start date timeframe or the ending date timeframe. That is true but that’s exactly what you need to do. You will simply add the two conditions such as the Purchased Date >= 11/14/2010 and Purchased Date <= 11/31/2010 and Sage SalesLogix will evaluate it correctly.

 

Once you created your group successfully you can share it with other users or export the results into Excel.

Comments
by Gold Super Contributor RJLedger
on ‎01-17-2011 11:19 AM

Good post... 

 

However, there are lots of situations where the SQL generated by the Query Builder is a "bit ugly". Also, there are many times where the query builder cannot be used to build the group you really want.

 

Unfortunately, there is no interface/hook to be able to modify/replace the actual SQL in a group. This is something that has been an issue for 11+ years.

 

There is one place where you can "tweak" the sSQL a little bit - CalculatedFields. Calc fields are actually a way to "inject" SQL into the group SQL.   

 

Here's something that I had saved away in my kb. Although it says "LAN", it applied to Web as well:

 

 

Using a Calculated Field as a "pseudo-custom property" on a table/entity in the SalesLogix "LAN" client
This example:
 A - Add New Calculated Field
 B - Set Name/Alias (RJL1/RJL1)
 C - Uses Opportunity Table
 
 D - Is a Numeric Calc Type
 
 E - Use the Closeprobability field
 
 F - Calculation:
      CASE
        WHEN 'Opportunity.Closeprobability' <= 80 Then 'False'
        ELSE 'True'
      END
 
 G - The result when looking via the SlxProfiler 
  Client SQL:
  SELECT RJL1,  A1.SECCODEID,  fx_RowAccess() fx_RowAccess 
  FROM OPPORTUNITY A1  WHERE A1.OPPORTUNITYID = [DBSTR_STR | DBTYPE_BYREF, "OxxxA000000"]
  
  Executed SQL on the BOTTOM of the provider:
  SELECT CASE
                WHEN A1.CLOSEPROBABILITY <= 80 THEN 'False'
                ELSE 'True'
                END RJL1, 
         A1.SECCODEID, NULL fx_RowAccess, A1.OPPORTUNITYID
  FROM OPPORTUNITY A1
  WHERE A1.OPPORTUNITYID = ...
  
Example of usage:
  On a label.Caption: (OPPORTUNITY:@RJL1)
Will return True/False string
  Also, setup a checkbox control:
Sub AXFormShow(Sender)
  checkbox1.checked = label1.Caprion
End Sub

 

Register Read Guidelines Request Partner or Employee Access

What's New in 8.1

Labels