Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

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
sSql = "SELECT ACCOUNTID AS USERFIELD1, ACCOUNT AND USERFIELD2, " & _
"TYPE AS USERFIELD3, STATUS AS USERFIELD4, " & _
"MAINPHONE AS USERFIELD5, WEBADDRESS AS USERFIELD6 " & BuildFilter
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
dgSearchResults.Refresh
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!

What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Mobile 3.0.3 for Saleslogix v7.5.4, v8.0.0 & v8.1.0 is now available!
Saleslogix Mobile v3.0.3 is now available in the customer portals. This update addresses all issues int
Posted on Apr 14, 2014 by Scott Weber to SalesLogix Product Blog
 
   Editing Filters in Saleslogix Web Client 8.0
Is it possible to add or remove Saleslogix filters from the List view in Accounts?
Posted on Apr 08, 2014 by SalesLogix Support to SalesLogix Questions & Answers
 
   Creating a Unique Saleslogix table ID in a SQL Function
A couple of months ago Ryan Farley wrote an article about how to use a SQL stored procedure to create a S
Posted on Mar 25, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix Integration with Google - WOW!
Saleslogix will be releasing their Google Integration this month and from what I have seen, it is fantast
Posted on Mar 18, 2014 by Scott Weber to SalesLogix Product Blog
 
   Using a Test Plan
So you have approved the Statement of Work (SOW) for some modifications and are awaiting the delivery of
Posted on Mar 13, 2014 by George Jensen to George Jensen's Blog
 
Comments

 

Twitter Trackbacks for Building a query-based lookup in SalesLogix Lan / Using SLX_Common to build query strings - Jason Buss' Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Building a query-based lookup in SalesLogix Lan / Using SLX_Common to build query strings - Jason Buss' Blog         [customerfx.com]        on Topsy.com

September 20, 2010 4:52 PM
 

Jason Buss said:

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

September 21, 2010 11:08 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register