#GPPT Building Self Service SQL Scripts

This example shows the different methods that can be used with GP Power Tools to create Self Service SQL Scripts which can be made available to all users or just specific users.

The actual SQL Script used for this demonstration is a simple select query on the Customer Master (RM00101) RM_Customer_MSTR table, but can be replaced by any SQL statements to achieve any manipulation of the data desired.

The first step is to create a Project using the Project Setup window to group all the components of the project together. The screenshot below already includes all the added components.

Creating the SQL Script

Then using the SQL Execute Setup window we can create a SQL Script which contains the desired functionality we want to provide to our users.

Note: The script can use SQL Column and Table Names (Dexterity Physical Names) or Dexterity Technical Names (surrounded by brace {<Technical Name>} characters). The Dexterity Technical Names will be converted to Physical Names when the script is executed.

For example using combination of syntax styles below:

select CUSTNMBR as [Customer ID], {Customer Name}, {Contact Person show}, ADDRESS1, {Country Code} 
from {RM_Customer_MSTR}

becomes:

select CUSTNMBR as [Customer ID], CUSTNAME, CNTCPRSN as [Contact Person], ADDRESS1, CCode 
from RM00101

This example just displays data from the Customer Master table, and is currently limited to only display the default 20 rows when executed from this window.

Adding Parameters to the SQL Script

To allow control of the data returned we will create a Parameter Lists from the Parameter List Maintenance window.

We can then update the SQL Query to use the values from the Parameter List to limit the results returned using the Parameters Button to insert the parameter values placeholders.

Publishing the SQL Script

We can also mark the script as Published to the Execute Window to allow users to use the script without having access to see or change the script. Use the Users Button to limit the access based on User and/or Company, User Class, Security Task, Security Role, Security ModAlt ID.

A user can open the SQL Executer window and select the Script ID and execute it. The parameter entry window will be displayed.

Then the script will be executed using the parameters provided.

Adding Go To functions (optional)

The next step is optional but allows the data returned by the SQL Query to be used to perform further actions.

From the SQL Execute Setup window, select Go To >> Setup SQL Execute Gotos to open the SQL Execute Setup Gotos window.

From this window you can create as many Gotos as desired. Use the buttons on the right hand side to add, delete and re-order as needed.

Note: The top entry is executed when a double click is used on the data result set.

The scripts for the SQL Gotos are created using the Runtime Execute Setup window with the Script Purpose of 5261: SQLExecuteGotoHandler. When the script purpose is selected, the template code is inserted. The template code is based on getting the Customer ID column from the results and using that value to open the Customer Maintenance (RM_Customer_Maintenance) form.

The second SQL Goto script is an example showing how an action can be processed for each of the selected rows in the returned data set.

Note: Multiple rows in the returned data set can be selected using the shift and control keys with the mouse.

This simple example, just shows a list of the selected rows.

Note: By having the Parameter ID with the same value as used in the SQL Script allows Dexterity code to see and use the parameter values used by the SQL Script.

Calling the SQL Script using Code

Using the Runtime Execute Setup window you can use Helper Functions to load and execute the SQL Script ID and display the results using the SQL Results window (with or without the optional SQL Gotos).

The script should use the same Parameter ID show that the parameter values flow through to the SQL script when it is executed. You can also publish the script to make it available to the Runtime Executer window.

The Users Button can be used to control which users have access to the published script.

Parsing the Returned Data

Rather than displaying the returned data set, it is also possible to use a different Helper Function to return the data set (up to 32,000 characters) to the calling script and then parse the data to perform any desired action on the data. See the More Information section below for previous articles which discussed parsing returned data sets and handling different data types.

This example will show a dialog for each record returned with the parsed data, so make sure you don’t return too many records if you try it out. The end of the script also includes the examples of handling other data types as comments.

Adding SQL Scripts to windows with Triggers

Using Triggers it is possible to add a SQL Script directly to a window rather than using the SQL Executer or Runtime Executer windows. With a trigger we can run the script from any event that a trigger can be attached to.  You can add it to form level, field level or application level menus. You can add it to an existing or modifier added button. You can even make it run automatically after an existing script, procedure or function in GP.

As our example relates to Customers we can use a Form Level Menu Trigger to add an “Additional” menu to the Customer Maintenance (RM_Customer_Maintenance) window which will run our code.

The script can just load and execute the script and display the data in the SQL Results window with the SQL Gotos enabled.

Note: The Parameter ID needs to be specified on the Options tab to ask the trigger to open the Parameter Entry window to ask the user for the parameter values.

Using Window Data to set Parameters for Scripts

Taking the trigger method one step further, instead of asking the user to make a selection of parameter values using the Parameter Entry window, we can use Helper Functions to load and populate the parameter values using code.

For this example we will add another form level menu to the Customer Maintenance (RM_Customer_Maintenance).

The script looks at the Customer ID (Customer Number) field on the window and then uses Helper Functions to load the Parameter ID and set the “To” and “From” values before loading and executing the script.

Note: The Parameter ID on the Options Tab now needs to be blank so that the Parameter Entry window does not open as we are using code instead.

The script also sets the OUT_Condition=true if the Customer ID is blank and uses the Action Tab to display a message to the user.  The message can be hard coded on the Trigger Setup window, but you can also use a Message ID as we have here.

The Users Button can be used to control which users have access to the triggers once the trigger is set to automatically start.

Using Messages for Dialogs

The Message ID overrides the Dialog Message field on the window and instead uses the text specified on the Messages Setup window.

The advantage of using Message IDs is that you can re-use the same message in multiple locations and if it needs editing, it can be changed in a single location. Message IDs also support multi-lingual systems allowing different messages to be displayed depending on the Language ID of the current application installation.

Final Steps

From the Project Setup window it is recommended to use the Update Triggers Button to ensure the Triggers are set to start Automatically and to Minimize Log entries for all triggers and scripts.

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 Customers.dbg.zip

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:

• #GPPT Parsing Returned SQL Data into Rows and Columns
• #GPPT Dexterity Samples for Datatype Conversions
• 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

David

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