#GPPT Free SQL Search across Multiple Fields Lookup Example

David Meego - Click for blog homepageWith the completion of development and release of Build 28 of GP Power Tools, there is now time to publish some of the great examples for the Developer Tools module. Please see the GP Power Tools Samples page for additional examples that you can download and use for free.

This example is amazing and every site with Microsoft Dynamics GP will want it installed. It does require the current Build 28.1 or later installed and the Developer Tools module of GP Power Tools (GPPT) registered. If you don’t have GP Power Tools installed or the Developer Tools module registered, feel free to install and start a free 30 day trial period, so you can test out this and the many other samples.

There are some other ISV solutions for helping you locate customers, vendors and items when entering the Customer ID, Vendor ID and Item Number respectively, but this solution is totally free to install and use, and is very fast and very customisable. To keep using it, you will need to have GP Power Tools – Developer Tools module registered and installed on every workstation.

SQL Search Lookup

The idea behind this customisation is that you can type any details you know into the Customer ID field and, if it is not an exact match to a Customer, GP Power Tools will display a list of customers where what you typed is contained (case insensitive) in any of the main fields on the customer record.

For example: Once installed, if you enter “ROB” into the Customer ID field with the Fabrikam sample data and press Tab, you will get the following SQL Results window displayed. Double click on the Customer you want and it will be returned to the Customer ID field on the calling window.

Note: You can still use Ctrl-L or the Lookup button to use the standard lookup. If you are trying to create a new Customer, just click OK or close the window and press tab to create the new Customer ID.

Customisable Search Queries

Below are lists of the main fields included for each object by default. If you wish to display additional data or change the fields included in the search, you can edit the SQL query used in the LOOKUP_CUST, LOOKUP_VEND or LOOKUP_ITEM SQL Execute scripts.

Customer search default fields:

Customer Number, Customer Name, Short Name, Contact Person, Address 1, Phone 1, Phone 2 and Phone 3.

Vendor search default fields:

Vendor ID, Vendor Name, Vendor Short Name, Vendor Contact, Address 1, Phone Number 1, Phone Number 2 and Phone 3.

Item search default fields:

Item Number, Item Description, Item Short Name, Item Generic Description and Item Class Code.

Note: The Item search also includes the QTY On Hand and QTY Available values as display only.

SQL Goto Scripts

The SQL Execute queries have been linked to the LOOKUP_CUST, LOOKUP_VEND and LOOKUP_ITEM SQL Goto scripts which are executed when double clicking on the SQL Results. You can add multiple SQL Goto scripts to SQL Execute queries, but the first one executes automatically when double clicking so you don’t have to use the Goto button.

These scripts return the Customer ID, Vendor ID, or Item Number (respectively) to the calling window field which has been previously stored into GPPT Memory Variables.

Trigger Registration and Scripts

A trigger has been added to execute before the original change script on the Customer Number, Vendor ID, or Item Number fields. There is one trigger for each field on each window where you want the SQL Search Lookups to work. The script for the trigger stores where to return the data into GPPT Memory Variables (to be used by the SQL Goto scripts) and then sets the characters you typed into the LOOKUP_SEARCH Parameter List and calls the SQL Results window to execute the appropriate SQL Execute script.

There are multiple Triggers named LOOKUP_CUST_XY, LOOKUP_VEND_XY and LOOKUP_ITEM_XY, where XY is a unique descriptor. The X number used to specify the type of window (Cards, Inquiry, Transaction module) and the Y number is just a sequence number to differentiate between the triggers.

These triggers can be duplicated and the scripts copied and adjusted to add the SQL Search Lookup functionality to any other window in any product. You can use Ctrl-R to specify a Find & Replace and Ctrl-B to repeat the changes. Use this to change the names for the window and form for both the script references and the setting of the Memory Variables of where to return the data to.

Note: When adding triggers, ensure that the Issue Reject Script and Keep Focus on Field checkboxes on the Actions tab have been enabled. For Item Number triggers, If you receive a warning message about “Item Number Cannot be changed”, also enable the Restore Field Value checkbox. These fields will be reset when you change the field details on the Resource tab and will need to be changed back.

Supplied with the example are triggers to add the functionality to

  • Customers: 13 Windows in core Dynamics.dic
  • Vendors: 16 Windows in core Dynamics.dic
  • Items: 18 Windows in core Dynamics.dic

You can add as many additional triggers as you desire to add the functionality to additional windows in core Dynamics.dic or to any window in a 3rd party (Microsoft or ISV) dictionary.

Installation

All the resources for the SQL Search Lookup functionality are contained in the LOOKUP_SEARCH project which can be opened from the Project Setup window once the project is installed. If you make changes or add triggers for new windows, you can export and import using this window once the project exists in your system.

Installing the sample code for the first time uses the Configuration Export/Import window as the Project does not exist in your system yet. Just download the configuration settings file from the link at the bottom of this article. Extract the contents of the zipped archive, open Configuration Export/Import and select the configuration settings file. Click Import and click OK on the Import Settings File window when it is displayed.

Please share this information and provide feedback as every Microsoft Dynamics GP site should have this functionality.

Enjoy

David

Download here: GP Power Tools Settings Lookup_search.dbg.zip

This article was originally posted on http://www.winthropdc.com/blog.

7 thoughts on “#GPPT Free SQL Search across Multiple Fields Lookup Example

      • I’ve been trying to get my customers to start on the Navigation Lists and search from there. I was trying to determine if this added additional fields. I think I will consider it for new customers but leave the existing ones using the Navigation Lists. 🙂

        Liked by 1 person

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.