This example adds filtering on an amount and/or a document number to the Apply Sales Document window when the Find window is used. as well as sorting the documents by date. The additional functionality can make it much quicker to find the correct document for a payment to be applied to, by its amount or by any part of its document number.
This customization also demonstrates some very useful techniques including:
- using Virtual Fields instead of Modifier to add the extra fields we need,
- using Triggers to capture a table reference to a temporary table,
- applying a where clause to that temporary table,
- using a where clause that involves more than one table,
- adding virtual keys to the temporary table,
- and manipulating the contents of the temporary table.
You will need GP Power Tools Build 31 or later installed to have Virtual Fields functionality.
This example changes Find window, opened by pressing the Find button on the Apply Sales Document window.
Normally, the Find window allows the selection of a Document Type and a Document Number. When the Find button is clicked, the Scrolling window will be filled with the nearest record with a Document Number starting with the value entered. The Find button doesn’t work if a Document Number hasn’t been entered.
After this Project is installed and the triggers started, when the Find window is opened you will notice there are now two Virtual Fields added; Current Amount and Partial Document Number. When you click the Find button, the code will now allow the Document Number to be blank and will then apply a filter to the records returned to match the Current Amount (if entered), and the Partial Document No. (if entered) is contained in the Document Number. The Scrolling window will be filled with the filter applied and will start with the nearest record to the Document Number (if entered).
The example also adds the virtual drop down list to sort the scrolling window by Due Date, Discount Date or Document Date. This is in addition to the default Document Number order.
Below is a screenshot of the VIRTUAL_FIELD_APPLY project showing the components:
Trigger VIRTUAL_FIELD_APPLY_01
This trigger runs before the FORM PRE event and uses the Virtual Field Helper Functions to expand the Find window and add the two Virtual Fields needed for that window. Next it gets the position and size of the Customer Sort By field and uses that to position another Virtual Field for the Document Sort by drop down list. Finally, it populates the Virtual Field drop down list with items and sets the default sort by.
Note: Creating Virtual Fields must occur before the FORM PRE event. The commands will not work if called from a different event.
Trigger VIRTUAL_FIELD_APPLY_02
This trigger replaces the original code for the Find button with our updated code. It gets the Table Reference for the RM_OPEN_TEMP temporary table which is captured and stored by other triggers in this project. If the reference is not empty, the code will get the values from the two Virtual Fields (also uses references) and use them to build a SQL where clause to apply to the temporary table. Finally, it calls the original code to update the scrolling window and close the Find window.
Note: The RM_OPEN_TEMP temporary table does not have the Current Trx Amount field in the table populated, which means we had to update the where clause to look at the RM_OPEN (RM20101) table to find documents with the desired Current Trx Amount. The trick to making the where clause work with a subquery is that the table and columns in the subquery must be fully qualified. The easiest method to achieve this is to fully qualify the table (eg. TWO.dbo.RM20101) and define an alias for the table. Then each column can use the alias to qualify it.
Trigger VIRTUAL_FIELD_APPLY_03
This trigger runs after the FORM PRE event and enables the temporarily disabled VIRTUAL_FIELD_APPLY_04 trigger. It then uses Helper Functions to save a blank record to the temporary table and gets that first record. This will force the table reference to be captured immediately rather than waiting for the first time the scrolling window is filled.
Trigger VIRTUAL_FIELD_APPLY_04
This trigger runs after the RM_OPEN_TEMP table has read a record. When registered it is automatically disabled until it is enabled by the VIRTUAL_FIELD_APPLY_03 trigger when the form opens. Once fired, it will capture the table reference and store it as a Memory Parameter and then disable itself again. The script also creates 6 virtual keys based on dates for the table and stores their index numbers as Memory Parameters.
Trigger VIRTUAL_FIELD_APPLY_05
This trigger runs after the FORM POST event and deletes the Memory Parameters now that they are no longer needed.
Trigger VIRTUAL_FIELD_APPLY_06
This trigger runs after the script used to populate the temporary table and uses the previously captured table reference to update the table contents so that the Due Date, Discount Date, Document Date, Original Trx Amount and Current Trx Amount are populated from the RM_OPEN table.
Note: We could adjust the where clause used for filtering by the Current Trx Amount now that the temporary table column has been populated, but it is worth leaving “as is” to show the technique.
Trigger VIRTUAL_FIELD_APPLY_07
This trigger runs after the script used to fill the scrolling window and will check the Sort Documents By Virtual Field drop down list and, if necessary, fill the window again using one of the virtual keys previously created.
Trigger VIRTUAL_FIELD_APPLY_08
This trigger runs after the script used to fill the scrolling window when finding a specific document. It will check the Sort Documents By Virtual Field drop down list and, if necessary, fill the window again using one of the virtual keys previously created, ensuring that the document (if found) is displayed at the top.
Trigger VIRTUAL_FIELD_APPLY_09
This optional trigger runs each time the Find window is opened and can be used to clear the two Virtual Fields.
Note: This script has been set to run delayed to ensure it runs after Trigger VIRTUAL_FIELD_APPLY_02 has completed.
Runtime Execute Script VIRTUAL_FIELD_APPLY_01
This Runtime Execute script uses the 5261: VirtualFieldHandler custom Script Purpose to force the window to redisplay its contents when the Sort Documents By Virtual Field drop down list is changed.
Techniques Used
Capturing a Table Reference and Storing it as a Memory Parameter and cleaning up when closing the form is in the VIRTUAL_FIELD_APPLY_03 to VIRTUAL_FIELD_APPLY_05 triggers.
Saving a blank record and reading it to force the capture of the table reference to happen immediately is in the VIRTUAL_FIELD_APPLY_03 trigger.
Creating Virtual Keys so the scrolling window can be sorted into different orders in in the VIRTUAL_FIELD_APPLY_04 trigger.
Expanding a window and creating Virtual Fields on windows using Helper Functions is in the VIRTUAL_FIELD_APPLY_01 trigger.
Using the position of an existing field to determine the position to use when creating a Virtual Field is in the VIRTUAL_FIELD_APPLY_01 trigger.
Using Virtual Fields by getting their field references is in the VIRTUAL_FIELD_APPLY_02 trigger.
Applying a Where Clause to table using a table reference is in the VIRTUAL_FIELD_APPLY_02 trigger.
Creating a Where Clause with a fully qualified SQL subquery is in the VIRTUAL_FIELD_APPLY_02 trigger.
Updating the contents of a temporary table after it has been populated is in the VIRTUAL_FIELD_APPLY_06 trigger.
Re-filling a scrolling window using Virtual Keys to change sort order is in the VIRTUAL_FIELD_APPLY_07 and VIRTUAL_FIELD_APPLY_08 triggers.
Using the 5261: VirtualFieldHandler Script Purpose is in the VIRTUAL_FIELD_APPLY_01 Runtime Execute script.
Downloading and Installing
Download the example code, import using the Project Setup window (without any project showing, select the path to the xml file and click Import):
The code will be active on next login or after switching companies, or you can use start the triggers manually from the Project Setup window.
More Information
For more information see:
- GP Power Tools Portal: http://winthropdc.com/GPPT
- GP Power Tools Samples: http://winthropdc.com/GPPT/Samples
- GP Power Tools Videos: http://winthropdc.com/GPPT/Videos
Hope you find this example useful.
David
12-Mar-2025: Improved code used to capture table buffer reference to avoid possible duplicate error being logged and allow for symbols to be used in the Partial Document Number on the Find window.
09-Apr-2025: Added extra optional trigger to clear the virtual fields each time the Find window is opened.
This article was originally posted on http://www.winthropdc.com/blog.