Building a query-based lookup in SalesLogix Lan / Using SLX_Common to build query strings

I’ve previously posed about a number of useful functions available to you within the SLX_Common script in the LAN version of SalesLogix.  I’d like to go a little further in-depth about a couple of functions which are helpful for creating new SQL Query strings.

In a recent project, I needed to build a query interface similar to what you find in standard opportunity products view.  The SLX_Common script holds a couple of functions that I used in building this interface.  In this posting, I’m going to go through building a simple account lookup view using this functionality.

Before I get to any code, we need to build a view containing our search fields and a grid to hold the search results. This view will not be bound in anyway, so I’m going to create this as a manage view. 


 First of all, I’m going to get rid of the right panel and the associated buttons.  In addition, I’m going to add a datagrid as well as three textboxes to hold search values, and three combo boxes to hold operators for each field.  For the purposes of this post, I’ll use the Account, Type and Status fields as the lookup fields.  Finally, I’ve added a Search button.


 For Type and Status, I actually set them up as picklists, with their corresponding lists attached.  The comboboxes have had their Items property populated with standard operator values (Starts With, Contains, Equal To, Not Equal To, Less Than or Equal To, Greater Than or Equal To, Less Than, Greater Than).  While some of these operators don’t really make sense for a Alpha-numeric comparison, I’ve included them in this example.  The available operators and their order are important, which you will see a little later.

For the Grid itself, we need to include some columns.  Since this grid will not actually be bound, it doesn’t really matter which fields we use, but we do need to provide fields so that we can apply formatting for fields that would need it, such as dates, boolean values, phone numbers, etc…  I’ve used the userfield 1-6 fields in the account table, setting the caption property of each column to what I would like to display in the datagrid, along with any necessary formatting.


 As you can see, the accountid field is included as well.  I don’t want to actually show this field, so it will be necessary to go into the columns collection for the grid and make that column invisible.  While I am in there, I’m also going to set the other columns to read-only, as well as disabling the editor.

Now that our view is set up, we can start adding in come code to make this lookup work!

 The first bit of code I’m adding is to define a global variable for each search parameter, which will tell us which fields to include in the search.  To populate these global varables, I created a subroutine call ApplyFilterOption which I attach to the OnExit event of each of the parameter fields:

  option explicit
Dim bIncludeAccount
Dim bIncludeType
Dim bIncludeStats
Sub ApplyFilterOption(Sender)
bIncludeAccount = (Trim(txtAccount.Text) <> "")
bIncludeType = (Trim(pklType.Text) <> "")
bIncludeStatus = (Trim(pklStatus.Text) <> "")
End Sub

With this code, if a user enters a value in “Account”, for example, we’ll know to include “Account” in the where clause of our SQL Statement.

Now we’re ready to do the actual query.  I’ve added another couple of functions, to build the actual query string that will search the database.  First, I created a function called SearchAccount.  This function is simply a string consisting of a SQL Statement for querying the Account table.

Function SearchAccount
Dim sSql
End Function

As you can see, there is no where clause yet.  This will be built by another function I added called BuildFilter.

Function BuildFilter
Dim sWhere
sWhere = ""
If bIncludeAccount Then
sWhere = AddToWhereClause("ACCOUNT", GetSQLConditionClause(cmbAccount.ItemIndex, txtAccount.Text), sWhere)
End If
If bIncludeType Then
sWhere = AddToWhereClause("TYPE", GetSqlConditionClause(cmbType.ItemIndex, pklType.Text), sWhere)
End If
If bInlcudeStatus Then
sWhere = AddToWhereClause("STATUS", GetSqlConditionClause(cmbStatus.ItemIndex, pklStatus.Text), sWhere)
End If
BuildFilter = sWhere
End Function

In the BuildFilter Function, I am using two different functions from the SLX_Common Script; AddToWhereClause and GetSqlConditionClause.   The GetSqlCondition function takes two parameters; The index from the combo box (this is why the order of the operators within the items property of the combobox control is so important) and the value to search for.  This is called from within the AddToWhereClause function to append any search parameters from my view into the SQL statement I built.

All we need to do now is set the SQL property of the results grid to the results of the SearchAccount function.  Since we aliased the returned values in that statement with the same fields we used to define the grid, any formatting we set in the grid will carry forward. 

Sub cmdSearchClick(Sender)
dgSearchResults.SQL = SearchAccount
End Sub

 We should now be getting a result set in our grid.  The final step will be to navigate to whichever record from the results the user double-clicks, and to close the search view.  This can be done very easily in a doubleclick event on the grid:

Sub dgSearchResultsDblClick(Sender)
Application.BasicFunctions.ShowDetails "Account", dgSearchResults.GetCurrentField("UserField1")
frmTestAccountLookup.ModalResult = mrOK
End Sub

So There we go! We’ve just created a simple Account lookup view using a Manage view, and functionality from the SLX_Common script.’

I hope you found this informative.  There’s a lot more we could do with this view.  Right-click functionality would be nice to have, and you’ll probably need to disable the navigation functionality if there are no results returned. If you are implementing functionality similar to this, I’d encourage you to play with my code an try to figure out what else you can do to make this more complete of a solution.   Thanks for reading!


Jason Buss

Jason is a senior application developer with Customer FX.

1 Comment

  1. Oops! Forgot to mention that you need to include the System:SLX_Common script on the new manage view.


Submit a Comment

Your email address will not be published.

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!