Welcome to the tenth article as an update to the series of articles that explains in detail the steps to add a user defined custom field to a window using Modifier, Report Writer and GP Power Tools to add the business logic.
The series should be read in order starting with the introduction article:
In this article we will show how to use manual SQL tables to store data rather than the DUOS table. In the previous article we included the Customization Maintenance packages. In the following article we will show how to use GPPT Custom Tables (even better than manual SQL tables).
To understand the original version of the project which adds an Item URL field to the Item Maintenance window and stores the additional field in the DUOS (Dynamic User Object Store – SY90000 table), please review the earlier articles in this series starting with the Introduction.
This is an updated version of the project using a manual SQL table to store the data instead of using the DUOS. Using a manual SQL 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. The creation and maintenance of a manual SQL table must be handled manually. See the next article for a better approach.
Here is the ITEM URL2 Project
Trigger ITEM URL2 BUTTON
This trigger is used with the hidden zoom button used to drill down and open a web browser to the address stored in the Item URL field. It uses a different method depending on if it is running on the Web Client or not.
Trigger ITEM URL2 DEL
This trigger runs after the IV_Item_MSTR table delete event. It loads the ITEM URL2 PARAM Parameter List and calls the ITEM URL2 REMOVE SQL Execute script to delete up the SQL table record.
Trigger ITEM URL2 FIELD
This trigger runs when the Item URL field is entered and ensures that the https:// prefix has been added.
Trigger ITEM URL2 READ
This trigger runs after Display Existing Record Field Change event. It loads the ITEM URL2 PARAM Parameter List and calls the ITEM URL2 READ SQL Execute script to read the SQL table record and then reads the returned data and populates the Item URL field
Trigger ITEM URL2 SAVE
This trigger runs after the Save Record Field Change event. It loads the ITEM URL2 PARAM Parameter List and calls the ITEM URL2 WRITE SQL Execute script to save the SQL table records from the data read from the Item URL field.
Runtime Execute Script ITEM URL2 GET
This script works with the rw_TableHeaderString Report Writer function to return the Item URL field to a report. It has been used with the Detailed Item List modified report. It loads the ITEM URL2 PARAM Parameter List and calls the ITEM URL2 READ SQL Execute script to read the data from SQL table record.
SQL Execute Script ITEM URL2 CREATE
This script used to manually create the manual SQL table and would need to be execute for each of the company databases.
SQL Execute Script ITEM URL2 MOVE
This script contains the SQL Select statement to move the DUOS data from the previous version of this project into the SQL table.
SQL Execute Script ITEM URL2 READ
This script contains the SQL Select statement to read the data from the manual SQL table based on the values passed in via the ITEM URL2 PARAM Parameter List.
SQL Execute Script ITEM URL2 REMOVE
This script contains the SQL Delete statement to remove the data from the manual SQL table based on the values passed in via the ITEM URL2 PARAM Parameter List.
SQL Execute Script ITEM URL2 WRITE
This script contains the SQL Update or Insert statements to write the data from the manual SQL table based on the values passed in via the ITEM URL2 PARAM Parameter List.
Note: This script has two versions of the code. The first method is more efficient as it will only execute a single update statement if the record exists, and a second insert statement if the record does not exist. The second (commented out) method will always execute two statements, the first being a select to check if the record exists and then second being an update or an insert statement as appropriate.
Parameter List ITEM URL2 PARAM
This Parameter List is used to pass parameter variables between the Dexterity scripts and the SQL scripts for reading, writing and removing records.
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 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 ITEM URL2 MOVE and either remove or disable the old ITEM URL project.
More Information
For more information see:
- #GPPT Beginner’s Guide to Adding Custom Fields – Introduction
- #GPPT Beginner’s Guide to Adding Custom Fields – Update: Including the Modified Resources
- #GPPT Beginner’s Guide to Adding Custom Fields – Update: Using manual SQL tables (this article)
- #GPPT Beginner’s Guide to Adding Custom Fields – Update: Using Custom Tables
- GP Power Tools Portal: https://winthropdc.com/GPPT
- GP Power Tools Samples: https://winthropdc.com/GPPT/Samples
- GP Power Tools Videos: https://winthropdc.com/GPPT/Videos
Hope you find these examples useful.
David
16-Mar-2026: Added note about moving data and removing or disabling the old ITEM URL project.
This article was originally posted on https://www.winthropdc.com/blog.
One thought on “#GPPT Beginner’s Guide to Adding Custom Fields – Update: Using manual SQL tables”