#GPPT Free SQL Search across Multiple Fields Lookup Example Version 2

After three week’s work, I am thrilled to be releasing a second version of the Free SQL Search Lookups Example. Based on feedback from a number of users, the code for this example has been significantly improved and extended. It even now has an optional Automatic Selection mode for when there is only one record returned.

Please see the GP Power Tools Samples page for additional examples that you can download and use for free.

To give you an idea of how much it has changed, the old exported dbg.xml file was 321KB and the new one is 929KB. Read on to learn about all the changes made ….

Enhancements

Here is a list of the enhancements to the example code:

• The default search queries for customers and vendors now includes the Address 2 field. This means that if the first line is used for building, unit or shop information, the second line which could have the street name in it will be included in the search.
• The default queries for customers, vendors and items now will display the Hold or Inactive checkbox status.
• Customers and Vendors can now be searched using the Address table and Email addresses in the Internet Information table as well as the Master table.
• You can swap between Master, Address and Email search modes using the Goto Button on the SQL Results window.
• If selecting from the Address or Email search modes where the window has an Address ID field, both the Customer/Vendor ID and Address ID will be populated.
• The trigger scripts have been standardized so that after the initial setting of the windows and fields at the top of the script, all the code that follows is exactly the same for each of the supported objects (Customers, Vendors and Items).
• The number of records being returned is checked before displaying the data. If zero records are returned, you will be notified. For Customers and Vendors, you will be prompted if you wish to search Addresses or Emails.
• If the number of records returned exceeds a maximum setting (currently 100), you will be notified that only that number of records will be returned. This avoids performance issues caused by too much data being returned and displayed.
• There is an Auto mode which will automatically select a record for you when only one record is returned. By default, Auto mode is disabled on Maintenance windows and Transactions windows as it can prevent new records being added if the ID value entered is found by the search. Auto mode can be enabled with a master setting or individually, if desired.
• Additional triggers can be enabled to allow searching using the Customer Name, Vendor Name and Item Description fields on the Sales Order Processing (SOP), Invoicing (IVC) and Purchase Order Processing (POP) transaction windows and the Customer Maintenance, Vendor Maintenance and Item Maintenance windows.
• Dialog text and button text are stored as Messages which allow for translation, if desired.
• Miscellaneous fixes to quirky behavior.

Video Demonstration

Here is a quick video demonstration of the SQL Search Lookups in action:

GP Power Tools Example – SQL Search Lookups (Direct Link)

Note: If wanting to create a new record or perform an on-the-fly addition of a new record, just close the SQL Results window and press Tab to re-enter the same data. This will bypass the SQL Search Lookup functionality and allow the standard GP behavior to run. You can also press Ctrl-L or the lookup button to use the standard Lookup functionality.

What Makes Up The Project

There are now 73 triggers in the LOOKUP_SEARCH project:

• 13 Customer ID, 5 Customer ID cleared, 3 Customer Name. 21 total triggers.
• 16 Vendor ID, 3 Vendor ID cleared, 4 Vendor Name. 23 total triggers.
• 18 Item Number, 0 Item Number cleared, 11, Item Description. 29 total triggers.

There are 7 SQL Execute queries which can be edited to change the data returned and the columns searched on:

• Customer Master table, Customer Address table, Customer Internet Information table
• Vendor Master table, Vendor Address table, Vendor Internet Information table
• Item Master table

There are 11 Runtime Execute scripts used as SQL Gotos:

• Selecting Customer ID, Selecting Customer ID and Address ID and 3 swapping search mode options.
• Selecting Vendor ID, Selecting Vendor ID and Address ID and 3 swapping search mode options.
• Selecting Item ID option.

There is a Parameter List which is used to populate the search data into SQL queries. It also contains the following settings which can be changed, if desired.

• Maximum number of records returned by the SQL searches, currently 100 records.
• Master setting for Customer Auto Select when only one record is found.
• Master setting for Vendor Auto Select when only one record is found.
• Master setting for Item Auto Select when only one record is found.

Finally there are 7 Messages which have default text for US-English, but can have additional languages added for your systems as needed.

How to Customize

This entire project is an example which means you can modify it or add to it in any way you wish.

Adding Other Windows

If you want to add the search functionality to other windows you can add new triggers for those new windows. Just make sure you select the checkboxes on the Actions tab and cut and paste the scripts for the Script tab. Then edit the top of the script where it sets up the field names and references (pointers) it needs for the rest of the scripts.

Below is an excerpt from a trigger showing the code that needs to be edited. See notes below for hints on how to modify the code.

{ Create References to the form, window and fields used so same script to be used for all triggers }
l_dict = DYNAMICS;
l_setfield = technicalname(field 'Customer Number');
l_tabfield = technicalname(field 'Address Code');
{l_prefield = "'(L) Old Customer ID'";
}l_addrsetfield = technicalname(field 'Address Code');
l_addrtabfield = technicalname(field 'Salesperson ID');
assign l_formref as reference to form RM_Sales_Entry;
if isopen(form(l_formref)) then
	assign l_windowref as reference to window RM_Sales_Entry of form RM_Sales_Entry;
	assign l_fieldref as reference to 'Customer Number' of window RM_Sales_Entry of form RM_Sales_Entry;
end if;

l_maint = false; 	{ This variable should be set to true for the Maintenance window for the field }
					{ When this variable is true, the Auto Selection functionality below is disabled }

l_auto = false; 	{ Set this variable to true, to enable Auto Selection when only one result returned }
					{ Auto Selection will be disabled when Maintenance mode above is enabled }
					{ There is a Master Auto Selection setting in the LOOKUP_SEARCH Parameter List }
					{ Using the Master Setting will turn on Auto Selection for all windows except Maintenance windows }
					{ However, it will prevent the adding of an item if it contains data that matches a single record }

Notes for adding triggers:

• When creating new triggers, after changing the information on the Resource Tab be sure to add the checkboxes as appropriate on the Actions Tab and cut & paste the script into the Script Tab.
• If adding a window in a third party product, make sure you change the l_dict value from the constant DYNAMICS (0) to the Dictionary ID of the third party product.
• The l_setfield is where the data should be returned to.
• The l_tabfield is where the focus should be left after returning the data, usually next field in the tab sequence.
• The optional l_prefield can be used to get around when the original code gives a message along the lines of “The ID field cannot be changed.” even though this is actually the first time it is being entered. It depends on how the check is implemented in the original code, sometimes there is a local “old” field, sometimes it uses the old() function. If the old() function is used, you can try setting the l_prefield to the same as the l_setfield. If that does not work try checking the Restore Field Value option on the Actions Tab. Test this twice or with a different search value as the custom code only runs every second time when the search value is the same (by design).
• The optional l_addrsetfield and l_addrtabfield are used if there is and Address ID field that can be set when using Address or Email searches. Comment them out with if there is no Address ID field on the window.
• The l_formref, l_windowref and l_fieldref reference variables need to be updated to point to the correct form, window and field. They are used to avoid having to make multiple changes to the script.
• The l_maint setting should be set to true for maintenance windows. It will disable the auto mode and makes the code behave better for windows that can create new records.
• The l_auto setting can be turned on without side effects for a window that does not allow on-the-fly adds, such as Inquiry windows. It can be turned on for individual transaction windows as desired. It can also be overridden with a master setting stored in the LOOKUP_SEARCH Parameter List.
• The Triggers with the A suffix can be added when original code runs when tabbing off the field when it is empty. For example: If you tab off the empty field and it becomes locked so you cannot enter a value, you need to add this trigger.
• The Triggers with the B suffix can be added if you want to be able to use an editable Customer/Vendor Name or Item Description to search from.
• Always use triggers for the same field type (Customer, Vendor, Item) as your templates.

Changing the SQL Queries

The SQL Execute scripts can be edited to change which fields are included in the returned data and which fields are included in the case insensitive contains search.

If you want to, you can adjust the where clause to filter records that are on Hold or marked as Inactive.

Increasing the Maximum Records returned

The default limit of 100 records returned from a SQL search query can be changed on the LOOKUP_SEARCH Parameter Lists.

Enabling Name and Description Searching

The triggers with the “B” suffix come disabled when you load the project, but can be quickly enabled using the Update Triggers button on the Project Setup window. These triggers do not work well with the Free Automatic Lookup Example and so are disabled by default. If you are not using this example, enabling the triggers allows searching using the Customer Name, Vendor Name or Item Description fields.

Enabling Auto Mode

Automatically selecting a record when only one record is returned in a search is called Auto Mode. Using Auto mode can prevent on-the-fly adding of new records if the ID entered is found by the search. Auto mode is always disabled on the maintenance windows. It is enabled by default on the inquiry windows as they don’t allow on-the-fly adds anyway. For the transaction windows Auto Mode can be enabled individually for each window by editing the trigger (change l_auto = true) or Auto Mode can turned on for all transaction windows per field type using the setting stored in the LOOKUP_SEARCH Parameter List.

Future Enhancements

Build 29 of GP Power Tools is currently being developed and it will contain some enhancements for the SQL Results window used to display the SQL Search Lookup data. These improvements include:

• Setting the focus to the first item in the list when the data is displayed.
• Allowing pressing the Enter key to run the first SQL Goto (same as double click).
• The addition of a Find feature which can search the data in the window for text.

These features will further enhance the SQL Search Lookup functionality once the new build is released.

Downloading and Installing

Download the example code, import using the Project Setup window (now possible for first time imports with Build 28.8 onwards), or use Configuration Export/Import window (for older builds):

• GP Power Tools Settings Lookup_search.dbg.zip

The code will be active on next login or after switching companies, or you can start the triggers manually from the Project Setup window.

More free customizations on the GP Power Tools Samples page:

• https://winthropdc.com/GPPT/Samples/

Feedback received so far from beta testers of this code has been very positive.

Enjoy

David

28-Apr-2022: Added note about closing the SQL Search Lookup window and pressing Tab to allow adding of new records.
27-May-2022: Added mention of Auto Mode into opening paragraph, so that it reminds users to turn it on, if desired.
03-Jun-2022: Updated project to fix issue with Item lookup SQL script on Binary Sort Order.

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