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
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!

What's This?
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.

Related Content
   Can I Bind an Attachment to a Specific Activity in Infor CRM version 7.54?
Recently I had a user ask if they could bind an attachment to a specific Activity within Infor CRM??
Posted on Jul 07, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (formerly SalesLogix) Administrator Locked Out
I thought I had seen all the issues affecting the datalink but this was a new one. We could not login t
Posted on Jun 18, 2015 by Mark Duthoy to SalesLogix Support
   How do I remove users from my Infor CRM system (formerly SalesLogix)?
I had an Administrator ask me for the steps they needed to take in order to remove a user from the Infor
Posted on May 08, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (Formerly Saleslogix) v8.1 update 03 and 05- Uncaught Error Scheduling Contact Processes
 In the Infor CRM (formerly Saleslogix) web client, under the Contact area you have the ability to s
Posted on May 07, 2015 by Kris Halsrud to Kris Halsrud's Blog
   Better Code Organization for Infor CRM (Saleslogix) Mobile Customizations
When customizing the Infor CRM (Saleslogix) Mobile client, it's common for the ApplicationModule to g
Posted on May 05, 2015 by Ryan Farley to Ryan Farley's Blog


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

All contents Copyright © 2015 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