Dynamics GP Custom VBA Lookups

I've been asked several times to add custom lookups to user defined fields in Dynamics GP.

Clients want to utilize user defined or custom fields, but they want to have the values validated, and they want to allow the user to select a valid value from a list. Makes sense.

I briefly checked out the drop down and list box controls available in Modifier, but they are pretty limited, and aren't really oriented towards displaying dynamic result sets from a database query.

So I ventured out and created my own custom lookup window in VBA.

As a side note, my biggest gripe with the standard GP lookup windows is their lack of a simple, "real-time" search field. By "real-time", I mean that I want the lookup window to instantly filter the displayed records based on what I type in the lookup search box--as I type it. And I want to have the window simultaneously search multiple fields at the same time--not just one field. For standard GP lookup fields, Rockton SmartFill does an excellent job in this regard, but sadly, in my experience, few clients have been willing to pay for SmartFill.

I'm going to show an example of a lookup that displays Sales Prospect records. In this case, the client called prospects "End Users", so it is called the "End User Lookup" window.

I first created a custom User Form in VBA, and added a Search field at the top. Because VBA is pretty limited in terms of controls, I had to use a ListBox to display my results. Finally, I have a Select and Cancel button.



When the form loads, I utilize UserInfoGet to have it query the GP company database and retrieve all prospects. I store those prospects in a client-side disconnected ADO recordset so that they remain in memory. I then have a simple procedure that displays the recordset results.



When a user types in the Search box, I send the equivalent of a SQL WHERE clause to the ADO recordset Filter property. This dynamically changes the records available in the recordset, allowing me to quickly redisplay only matching records as the user types each character.



As you can see in this example, when I type "wav" in the search box, I am simultaneously searching on the ID, name, and city of the prospect records, narrowing my list down from 5,000 records to 10 records as fast as I can type the three letters. The search results change almost instantaneously as I type each character.

At first I was concerned that this type of dynamic, real-time searching would run into performance issues. But, to my surprise, it's very responsive. One reason is that as the user types each character in the search box, the number of records that meet the search criteria and need to be displayed gets smaller and smaller. The ADO Filter property is so fast that the search itself is instantaneous, leaving only the work of displaying the resulting records. So even if I started with 10,000 records, after I type just one word, I will have quickly narrowed down my result set to a much smaller number of records that can be displayed quickly in the listbox.

There are a few minor limitations with this solution, both of which are imposed by VBA. First, because I am limited to a list box instead of a ListView or Grid, I chose to use static lables for column headers, and fixed columns, and have therefore decided to create a separate user form for each lookup window that I need. You could invest some time to add code to make a single user form work for multiple lookups, but since I have only needed one or two for any given client, I didn't bother.

The second limitation is that the list box control doesn't have a sorting feature. Based on my experience, because the lookup is only used to select one record, and because the Search feature is so flexible and fast, there was never a requirement to sort records. But if you needed to sort, you could add some buttons at the top of each column and then use the ADO recordset Sort property.

Based on this VBA lookup, I have subsequently created a similar .NET class that I use for lookups with the VS Tools AddIns that I have been working on. For that class, I did spend the time to design it to work for multiple look up types, so I can now use it easily to lookup customers, addresses, inventory items, sales orders, and even records in custom tables. It also has a dynamic real-time search capability, but also has the benefit of better controls and capabilities offered by .NET. If anyone is interested in that, let me know and I'll try and find the time to create another post discussing it.

Below is the code behind the VBA User Form.


Private rsProspects As New ADODB.Recordset

Private Sub cmdCancel_Click()
Me.Hide
End Sub

Private Sub cmdSelect_Click()
CustomerAddressMaintenance.EndUserID.Value = ProspectList.Value
Me.Hide
End Sub

Private Sub ProspectList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If CustomerAddressMaintenance.IsLoaded Then
CustomerAddressMaintenance.EndUserID.Value = ProspectList.Value
ElseIf SalesUserDefinedFieldsEnt.IsLoaded Then
SalesUserDefinedFieldsEnt.UserDefined3.Value = ProspectList.Value
End If
Me.Hide
End Sub

Private Sub SearchFilter_Change()
Dim strFilter As String
strFilter = Trim(SearchFilter.Value)
If Trim(strFilter) <> "" Then
rsProspects.Filter = "CUSTNAME LIKE '%" & strFilter & "%' OR CITY LIKE '%" & strFilter & "%'"
Else
rsProspects.Filter = adFilterNone
End If
Call PopulateProspectList
End Sub

Private Sub UserForm_Activate()
SearchFilter.Value = ""
rsProspects.Filter = adFilterNone
Call QueryProspects
Call PopulateProspectList
SearchFilter.SetFocus
End Sub

Private Sub UserForm_Initialize()
ProspectList.ColumnCount = 5
ProspectList.ColumnWidths = "80;160;90;30;80"
End Sub

Private Sub PopulateProspectList()
lngRecords = rsProspects.recordCount
If lngRecords > 0 Then
ReDim strRecords(lngRecords - 1, 4)
recordCount = 0
While Not rsProspects.EOF
strRecords(recordCount, 0) = rsProspects.Fields("PROSPID").Value
strRecords(recordCount, 1) = rsProspects.Fields("CUSTNAME").Value
strRecords(recordCount, 2) = rsProspects.Fields("CITY").Value
strRecords(recordCount, 3) = rsProspects.Fields("STATE").Value
strRecords(recordCount, 4) = rsProspects.Fields("ZIP").Value
recordCount = recordCount + 1
rsProspects.MoveNext
Wend
ProspectList.List() = strRecords
Else
ProspectList.Clear
End If
End Sub

Private Sub QueryProspects()
'rsProspects.State
'0 = Closed
'1 = Opened
If rsProspects.State = 1 Then
rsProspects.Close
End If

Dim oConn As ADODB.Connection
Dim strSQL As String
Dim lngRecords As Long
Dim recordCount As Long
Dim strRecords() As String

Set oConn = UserInfoGet.CreateADOConnection
oConn.DefaultDatabase = UserInfoGet.IntercompanyID
oConn.CursorLocation = adUseClient
rsProspects.CursorLocation = adUseClient

strSQL = "SELECT RTRIM(PROSPID) AS PROSPID, RTRIM(CUSTNAME) AS CUSTNAME, RTRIM(CITY) AS CITY, RTRIM(STATE) AS STATE, RTRIM(ZIP) AS ZIP FROM SOP00200 ORDER BY PROSPID"
Set rsProspects = oConn.Execute(strSQL)
rsProspects.ActiveConnection = Nothing
oConn.Close
End Sub