Welcome to the third article in 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 use GP Power Tools to add the business logic to save the data from the newly added custom field on the Item Maintenance window.
For the business example we are working through, we need to add an internet URL field to the Item Maintenance window which can be opened in an internet browser.
The process to do this can be broken down into 6 Steps:
- Modifying the Window; Use Modifier to add the URL field to the Item Maintenance window with a hyperlinked prompt.
- Saving the Data; The data will be stored in the DUOS (Dynamic User Object Store) SY90000 table and business logic will be added to handle saving the URL field.
- Using the Data; Make the hyperlink open the URL in a browser window.
- Creating the Report Writer Function; Use Power Tools to create a Report Writer Function to allow the data to be pulled into reports.
- Modifying the Report; Use Report Writer to add a calculated field to call the Report Writer Function.
- Publishing the Project; Change settings to make the project available to all users.
Follow the steps below to complete Step 2: Saving the Data. To achieve this we need to create a GP Power Tools Project and add the three Triggers needed to save the data, display the data and delete the data. (Click on images to see full sized versions and full scripts are listed at the end of the article).
Note: During the development of this project we will be limiting triggers to run for the current user only. If you are developing on a separate test or development system, these steps can be optional.
- From the GP Power Tools Area Page, select Cards >> Project Setup. If a current project is displayed click Clear to restart the window. Enter a Project ID as ITEM URL and the Project Description as Add Web URL Link field to Item Maintenance Window. Select the Current Project checkbox (say yes to the dialog confirming the change if it appears). Press Ctrl-S (or selection Options >> Save and Continue) to save your changes without clearing the window.
- Before we can create the triggers we need to handle the CRUD (Create, Read, Update & Delete) events, and we will need to capture some logs to confirm how the original window works. To capture the logs we will use GP Power Tools Manual Logging. Open the Item Maintenance window, from the menus select Tools >> Start Manual Logging. Now type in an existing Item Number to retrieve a record and then click Save to save it. Then from the menus select Tools >> Stop Logging (click no to the email option if asked). To quickly get to the folder where the logs are stored, from the Project Setup window, click the Configuration File Path hyperlink.
- The Windows Explorer will open the Logging Folder. Click on the Date Modified column header to put the newest files to the top of the window. Open the Script_<User>_<Company>_<Date>_<Time>.log file with your favourite text editor (Notepad.exe is fine). Search for “Display Existing Record” (spaces, no quotes) or “Display_Existing_Record” (underscores, no quotes) near the top of the log just after the change script for the primary key field. If the Display Existing Record script has “_CHG of form” then it is a field change script, if it has “() of form” then it is a form function and if it has just “of form” it is a form procedure.
- You need to also search for “Save Record” (spaces, no quotes) or “Save_Record” (underscores, no quotes) just after the change script for the Save Button. If the Save Record script has “_CHG of form” then it is a field change script, if it has “() of form” then it is a form function and if it has just “of form” it is a form procedure. Now that we know how the original code works we can add GP Power Tools Triggers after these scripts to display and save our data.
- Starting with the save trigger, go to the Project Setup window (with our project displayed), from the toolbar select Add >> Trigger Setup. Complete the header fields: Trigger ID: ITEM URL SAVE, Trigger Description: Save Web URL Link field from Item Maintenance Window. Check the Start Trigger Automatically, Do not activate Logging and Minimize Log Entries checkboxes. Click on the Users Button and limit the trigger to only start for your User ID and click OK.
- Now as the Save Script was a field change script we will set the Trigger Type: Focus Event, Trigger Event: Field Change, Trigger Attach: After Original. We can then use the by menu lookup (left hand of two lookup buttons) on the Resource tab to select Form Name: IV_Item_Maintenance, Window Name: IV_Item_Maintenance and Field Name: ‘Save Record’.
- No changes will be needed on the Actions tab and because we added the trigger from the Project Setup window, the Project ID is already entered on the Options tab. We just need to add code to the Script tab. Click on the Script Tab and select the Modified and Check Security checkbox. This will allow us to access the field added with Modifier, and will ensure the script will only run if the Modified window is in use.
- The default script needs to be edited to add a helper function to save the data into the DUOS table. Make the following changes: Remove the empty() function from the second if statement (don’t forget the closing bracket). Add abort script; on the line below the second if statement to abort the script if the record was not saved for any reason. Move the OUT_Condition, TRUE_STRING line below the end if; and make some space above it so we can add the helper function.
- With the cursor in the space made, click the Helper button to open the Helper Functions window. Select Set a DUOS Property and click OK to insert the code.
- Edit the script to remove any double blank lines and indent the inserted code using tab key. If you select multiple lines and press tab or shift-tab you can change the indentation of multiple lines at a time.
- On the MBS_DUOS_Set line change the “Object” text to “Item” as we are storing data against Items and change the “Property” text to “URL” as we will be storing a URL field. Then on the MBS_ObjectID line remove the “ID” placeholder and use the Names Button >> Forms, Windows & Fields to select the Item Number field on the Item Maintenance window of the Item Maintenance Form.
- The final bit is to remove the “Value” placeholder on the MBS_Property line and use Names button again to insert any local field starting with “(L)”. Then edit the local field to the name of our Modifier added field ‘(L) URL’.Note: With Build 28 onwards you can select the Modified window and find the Modifier added field in the lookup. Also, if the field you wish to store is not of string datatype, see this article for quick methods of converting other datatypes to and from strings: #GPPT Dexterity Samples for Datatype Conversions.
- Now the script is complete. However, if we want to make a minor enhancement where we don’t store a record in the DUOS table if the URL field is blank we can use an if not empty() statement and the Delete a DUOS Property Helper function. You can delete the extra MBS_ObjectID line added, and don’t forget to change the “Object” and “Property” placeholders to “Item” and “URL”.
- Click Save to check syntax and save the Trigger. You will now see the trigger added to the Project Setup window.
- To speed up the creation of the next Trigger let’s double click on the ITEM URL SAVE trigger to open it and click on the Duplicate Button. Enter the new Trigger ID as ITEM URL READ and click OK.
- In the header change the Trigger Description to Read Web URL Link field to Item Maintenance Window. On the Resource tab change the Field Name to ‘Display Existing Record’ (you can use the lookup or type it). Click Yes when asked about reset the current script back to default code.
- Click on the Script Tab to display the now default script.
- Edit the script to remove the line if not empty(‘Display Existing Record… and the associated end if line. Use shift-tab to unindent the OUT_Condition, TRUE_STRING line. Add some blank lines above the OUT_Condition, TRUE_STRING line.
- Use the Helper Button to add the Get a DUOS Property helper function.
- Edit the script to remove any double blank lines and indent the helper function. Change “Object” and “Property” placeholders to “Item” and “URL” respectively. Use the Names Button again to change the “ID” place holder to the Item Number field.
- Last step for this script is to add a line to place the value read from the DUOS table into the URL field. Add a blank line under the MBS_DUOS_Get line and use the trick of adding any local field and then changing it to ‘(L) URL’. Then add = MBS_Property; to the end of the line.Note: With Build 28 onwards you can select the Modified window and find the Modifier added field in the lookup. Also, if the field you wish to store is not of string datatype, see this article for quick methods of converting other datatypes to and from strings: #GPPT Dexterity Samples for Datatype Conversions.
- Click Save and return to the Project Setup window, which now has two triggers listed.
- On the Project Setup window, select Add >> Trigger Setup so we can add the final trigger of this stage which will handle when the item is deleted and ensure the additional data is not left behind as an orphaned record. Enter the Trigger ID as ITEM URL DEL, Trigger Description as Delete Web URL Link field with Item Master Table. Check the Start Trigger Automatically, Do not activate Logging and Minimize Log Entries checkboxes. Click on the Users Button and limit the trigger to only start for your User ID and click OK.
- As we are going to identify the Item has been deleted by watching the Item Master table, set Trigger Type: Table, Trigger Event: Delete Record, Trigger Attach: After Table Event. You can use the lookup on the Resource tab to select Table Name: IV_Item_MSTR from the Inventory series and Field Name: ‘Item Number’.
- No changes will be needed on the Actions tab and, because we added the trigger from the Project Setup window, the Project ID is already entered on the Options tab. We just need to add code to the Script tab. Click on the Script Tab and insert some blank lines above the OUT_Condition = true line.
- Use the Helper button and Helper Functions window to add the Delete all DUOS Properties helper function. Use tab to indent the lines and change the “Object” placeholder to “Item”.
- As the table we are using in this script has been passed in as an anonymous table it is best to use the column() function to refer to the fields in the table using the name as a string. So copy from the if line column(“‘Item Number'”) of table IV_Item_MSTR and paste it to replace the “ID” on the MBS_ObjectID line. Finally, clean up any double blank lines in the script.
- Click Save and close the Trigger Setup window to return to the Project Setup window.
We are now in a position to test the customization. At this stage the triggers we have created are not active. We need to activate them before we can test the modified Item Maintenance window.
The easiest approach is to use the Start >> Start Project Triggers from the toolbar of the Project Setup window. This will only start the triggers for the current project and not all “Automatic Start” triggers.
Note: If we pull up the Company Login dialog and click OK or exit GP and log back in, the triggers will be enabled for your User ID. This assumes you have not disabled automatically starting triggers using the “GP Power Tools Setup Mode” option on the Dex.ini Settings window.
To see the triggers running we need to open the Trigger Status window. You can open it from the Project Setup window using Add >> Trigger Status from the toolbar, or from the GP Power Tools Area page >> Inquiry >> Trigger Status.
You should see 5 triggers registered as a couple of the Trigger IDs we set up use secondary triggers at the Dexterity level to achieve their purpose.
Now open Item Maintenance (Cards >> Inventory >> Items) and select to open the Modified window. Pull up an item, enter a value into the URL field, save the item and then pull it up again. The custom field should work as though it was part of the original design of the window.
Congratulations, you have created a customization with a custom field. The next step is to add a couple more triggers to use the custom field. Stay tuned for the next article.
Below are the scripts from the three triggers:
ITEM URL SAVE Trigger Script
local boolean err_val; local string IN_OldValue; local string IN_NewValue; local string MBS_ObjectID; local string MBS_Property; err_val = OLE_GetProperty("IN_OldValue", IN_OldValue); err_val = OLE_GetProperty("IN_NewValue", IN_NewValue); err_val = OLE_SetProperty("OUT_Condition", FALSE_STRING); if isopen(form IV_Item_Maintenance) then if not 'Save Record' of window IV_Item_Maintenance of form IV_Item_Maintenance then abort script; end if; MBS_ObjectID = 'Item Number' of window 'IV_Item_Maintenance' of form 'IV_Item_Maintenance'; MBS_Property = '(L) URL' of window 'IV_Item_Maintenance' of form 'IV_Item_Maintenance'; if not empty(MBS_Property) then call with name "MBS_DUOS_Set" in dictionary 5261, "Item", MBS_ObjectID, "URL", MBS_Property; else call with name "MBS_DUOS_Del" in dictionary 5261, "Item", MBS_ObjectID, "URL"; end if; err_val = OLE_SetProperty("OUT_Condition", TRUE_STRING); end if;
ITEM URL READ Trigger Script
local boolean err_val; local string IN_OldValue; local string IN_NewValue; local string MBS_ObjectID; local string MBS_Property; err_val = OLE_GetProperty("IN_OldValue", IN_OldValue); err_val = OLE_GetProperty("IN_NewValue", IN_NewValue); err_val = OLE_SetProperty("OUT_Condition", FALSE_STRING); if isopen(form IV_Item_Maintenance) then MBS_ObjectID = 'Item Number' of window 'IV_Item_Maintenance' of form 'IV_Item_Maintenance'; call with name "MBS_DUOS_Get" in dictionary 5261, "Item", MBS_ObjectID, "URL", MBS_Property; '(L) URL' of window 'IV_Item_Maintenance' of form 'IV_Item_Maintenance' = MBS_Property; err_val = OLE_SetProperty("OUT_Condition", TRUE_STRING); end if;
ITEM URL DEL Trigger Script
inout anonymous table IV_Item_MSTR; in integer IN_Operation; in string IN_Value; out boolean OUT_Condition; local string MBS_ObjectID; OUT_Condition = false; case IN_Operation in [TRIGGER_ON_DB_DELETE] if not empty(column("'Item Number'") of table IV_Item_MSTR) then MBS_ObjectID = column("'Item Number'") of table IV_Item_MSTR; call with name "MBS_DUOS_DelAll" in dictionary 5261, "Item", MBS_ObjectID; OUT_Condition = true; end if; else end case;
Hope you find this series useful.
09-Jun-2021: Added link to article explaining methods of parsing other datatypes to and from strings for storing in the DUOS: #GPPT Dexterity Samples for Datatype Conversions.
This article was originally posted on http://www.winthropdc.com/blog.