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:
bIncludeAccount = (Trim(txtAccount.Text) <> "")
bIncludeType = (Trim(pklType.Text) <> "")
bIncludeStatus = (Trim(pklStatus.Text) <> "")
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.
sSql = "SELECT ACCOUNTID AS USERFIELD1, ACCOUNT AND USERFIELD2, " & _
"TYPE AS USERFIELD3, STATUS AS USERFIELD4, " & _
"MAINPHONE AS USERFIELD5, WEBADDRESS AS USERFIELD6 " & BuildFilter
As you can see, there is no where clause yet. This will be built by another function I added called BuildFilter.
sWhere = ""
If bIncludeAccount Then
sWhere = AddToWhereClause("ACCOUNT", GetSQLConditionClause(cmbAccount.ItemIndex, txtAccount.Text), sWhere)
If bIncludeType Then
sWhere = AddToWhereClause("TYPE", GetSqlConditionClause(cmbType.ItemIndex, pklType.Text), sWhere)
If bInlcudeStatus Then
sWhere = AddToWhereClause("STATUS", GetSqlConditionClause(cmbStatus.ItemIndex, pklStatus.Text), sWhere)
BuildFilter = sWhere
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.
dgSearchResults.SQL = SearchAccount
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:
Application.BasicFunctions.ShowDetails "Account", dgSearchResults.GetCurrentField("UserField1")
frmTestAccountLookup.ModalResult = mrOK
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!