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
   Fixing the Orphaning of Salesfusion Data when Converting Leads to Contacts in Infor CRM (Saleslogix)
We have had a couple of client recently come to other with the same problem-When you convert a Lead to a
Posted on Sep 19, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Official Infor CRM Acquisition FAQ
What to expect now that Saleslogix is Infor CRM. Like many of you, it will take some time (probably a
Posted on Sep 18, 2014 by Brianna Ojard to The Inbox
 
   ComboFix Kills Saleslogix Sync
ComboFix, a third party malware utility, will alter the Operating system so .qts and .que files will no l
Posted on Sep 12, 2014 by Brianna Ojard to SalesLogix Support
 
   How do I access the new Opportunity that I created in Saleslogix??
I had a user ask why they could not access a Salelogix opportunity when they clicked the hyperlink from t
Posted on Sep 04, 2014 by Dale Richter to SalesLogix Questions & Answers
 
   Retrieving Items from a Picklist via Javascript
Saleslogix is very light on how to use client side processes to do things in the Saleslogix web client.&n
Posted on Aug 29, 2014 by Kris Halsrud to Kris Halsrud'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