GP Power Tools Build 32 added Custom Tables as a new feature to the Developer Tools module. Custom Tables can automatically create and maintenance SQL tables in company (or system) databases as defined by the definition in the Custom Tables Setup window.
This is an updated version of the #GPPT Adding Virtual Fields to Item Maintenance project which uses a Custom Table to store the data rather than the DUOS. Easy access to the Custom Tables is provided by a series of Custom Table Buffer Helper Functions which replicate how Dexterity table buffers work.
The project still uses Virtual fields Helper Functions to add additional fields to windows without needing Modified or Alternate windows.
This example adds Customer information to the Item Maintenance window using Virtual Fields and stores the data in a Custom Table. Using a Custom Table allows all the separate fields to be stored in a single record with the fields of the appropriate data types. This is better than the DUOS which stores each field as a separate string record.
This project can work alongside the ITEM URL project used in the series of articles below:
In the screenshot below there are 12 Virtual Fields added, being:
- Change Status Button
- Customer ID Field
- Customer ID Prompt
- Customer ID Lookup
- Customer ID Expansion
- Customer ID Status
- Active Checkbox Field
- Active Checkbox Prompt
- Customer Name Field
- Customer Name Prompt
- Customer Satisfaction Drop Down List
- Horizontal Line above Navigation Fields.
Some of these fields are added with a single Helper Function command, for example the MBS_Add_Virtual_FieldPromptLookup command adds the Field with its associated Prompt and Lookup.
The space at the bottom of the window is created with the MBS_Expand_Virtual_Field_Window Helper Function command.
Below is a screenshot of the VIRTUAL_FIELD2 project showing the components:
Trigger VIRTUAL_FIELD2
This trigger runs before the FORM PRE event and loads and executes the VIRTUAL FIELD Runtime Execute script. Having the actual commands to create the Virtual Fields in a script separate to the trigger makes it easier to test as you do not need to stop and start the project triggers.
Note: Creating Virtual Fields must occur before the FORM PRE event. The commands will not work if called from a different event.
Trigger VIRTUAL_FIELD2_DEL
This trigger runs after the IV_Item_MSTR table delete event and cleans up the Custom Table record.
Trigger VIRTUAL_FIELD2_READ
This trigger runs after Display Existing Record Field Change event and reads the Custom Table record and populates the Virtual Fields. It uses the MBS_Get_Field_Reference Helper Function to get the reference for each Virtual Field and then uses the field() function to re-instantiate the reference.
Trigger VIRTUAL_FIELD2_SAVE
This trigger runs after the Save Record Field Change event and saves the Custom Table record from the data read from the Virtual Fields.
Runtime Execute Script VIRTUAL_FIELD2
This script called by the FORM PRE trigger contains the calls to expand the window and add the Virtual Fields. The MBS_Expand_Virtual_Field_Window Helper Function is used to make the window bigger, move down all the navigation fields (browse buttons and Sort By field) as well as add a new horizontal line. Then a series of MBS_Add_Virtual_Field Helper Function calls are used to add the Virtual Fields.
The script populates the drop down list Virtual field with items, as it is created without any items. The script also demonstrates, with the Change Status button, how you can read the existing position and size of fields and use those values when adding Virtual Fields.
Note: Using the VF_ fields from the GP Power Tools dictionary ensures that the Virtual Fields added never clash with any existing fields on the window. A Virtual Field will fail to be added if that same field already exists on the window.
Runtime Execute Script VIRTUAL_FIELD2_HANDLER
This script is the Virtual Field handler created using the 5261: VirtualFieldHandler Script Purpose on the Runtime Execute Setup window. It is used to handle the triggers for the Virtual Fields when the fields are changed or clicked, or when the window is opened or restarted.
The script starts with a template and uses the MBS_Get_Field_Reference Helper Function to get the reference for each Virtual Field and then uses the field() function to re-instantiate the reference.
Note: The Handler script has been improved to use the native Customer Lookup rather than a custom SQL Lookup for the Customer Number.
SQL Execute Script VIRTUAL_FIELD2_MOVE
This script contains the SQL Select statement to move the DUOS data from the original version of this project into the Custom table.
Custom Table VIRTUAL_FIELD2
This is the definition for the custom table as shown in the screenshot at the top of the article.
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.
Note: If you have previously used Version 1 of this sample with data stored in the DUOS (Dynamic User Object Store) SY90000 table, please use the SQL Execute script VIRTUAL_FIELD_MOVE and either remove or disable the old VIRTUAL_FIELD project.
More Information
For more information see:
- #GPPT Adding Virtual Fields to Item Maintenance
- 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
16-Mar-2026: Added note about moving data and removing or disabling the old VIRTUAL_FIELD project.
17-Mar-2026: Fixed Customer Lookup Sort By field not initializing.
This article was originally posted on http://www.winthropdc.com/blog.
One thought on “#GPPT Adding Virtual Fields to Item Maintenance Version 2”