#GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 2


David Meego - Click for blog homepageFollowing on from yesterday’s article: #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 1, let’s look at how this can be achieved using GP Power Tools.

I will be using the very latest GP Power Tools Build 23, Last Modified: 17-Mar-2018 Hotfix release as this includes new Helper Functions to make the use of parameters in scripts called programmatically much simpler to use.

As mentioned in the previous article, it would be possible to create the customisation using a single Trigger Script and use the Dexterity SQL_Execute() function for the pass through SQL query. However, I want to demonstrate how GP Power Tools allows the embedding of other languages in your scripts and how you can mix and match Dexterity and SQL (and C# or VB.Net) together.

Please look at this series of articles and think about all that you can achieve with this sort of approach. The possibilities are only limited by your imagination.

Create the Project

As this customisation is going to include more than one script or object in GP Power Tools, let’s create a Project to group all the objects together. You can create the project on-the-fly from any of the other windows, but for simplicity let’s create it first.

  1. From the GP Power Tools Area Page, select Cards >> Project Setup.
  2. If a current project is automatically populated, press Clear to reset.
  3. Enter a Project ID: VENDOR REMIT AD
  4. Enter a Project Description: Vendor Remit to Address check
  5. Optional: Mark the project as your current project. The current project populates automatically when the window is opened.
  6. Click Save to save the project or press Ctrl-S to save the project and keep it displayed.

Create the Parameter List

We are going to create a SQL Script to get the last transaction date for a given Vendor. This script will be used in our final code programmatically, but we can write it and test it using the User Interface using GP Power Tools. While you can add the Parameter List on-the-fly, let’s create it in advance.

  1. From the GP Power Tools Area Page, select Cards >> Parameter Lists.
  2. Enter a Parameter ID: VENDOR REMIT AD
  3. Enter a Parameter Description: Vendor Remit to Address check
  4. Enter or select via lookup the Project ID: VENDOR REMIT AD
  5. Enter the Parameter Title. This is the Title of the window used when asking for parameters. Use: Select a Vendor
  6. Enter the Parameter Instructions. These are the instructions of the user displayed when asking for parameters. Use: Please enter or select via lookup the desired Vendor ID.
  7. For the first Parameter, Set it up as Prompt: Vendor ID, Type: Lookup, Mode: Single Field, Options: Vendor Lookup, Length: 15 and no default value.
  8. Optional: Click Preview to see the Parameter in action.
  9. Click Save to save. Then close the window.

Create the SQL Script

Now we can create a SQL Script to get latest document from all transactions for the specified Vendor ID. To ensure no problems with international installations, we can use the SQL CONVERT() function to format the date into YYYY/MM/DD format. We will also use the previously created parameter is to select the Vendor ID for the script. The final script is shown after the screenshot (below).

  1. From the GP Power Tools Area Page, select Cards >> SQL Execute Setup.
  2. Enter a Script ID: VENDOR REMIT AD
  3. Enter a Script Name: Vendor Remit to Address check
  4. Enter or select via lookup the Project ID: VENDOR REMIT AD
  5. Enter or select via lookup the Parameter ID: VENDOR REMIT AD
  6. Enter the script:SELECT COALESCE(MAX(DOCDATE), ‘1900-01-01’) AS DOCDATE
    FROM PM00400 WHERE VENDORID =
  7. Leaving the cursor at the end after the equals sign and a space, Click Parameters
  8. From the Parameter Placeholders lookup select Vendor ID to insert the parameter placeholder string ”/*%01%*/
  9. To format the date as YYYY/MM/DD wrap the date COALESCE() statement with CONVERT(VARCHAR, <Date> , 111)
  10. Optional: Check the Published to Executer window, if you want the script to be published so end users can execute it from the SQL Executer window.
  11. Press Ctrl-S to save the script and keep it displayed.
  12. Click Execute or press Ctrl-E to execute the script to test it. This will automatically use the Parameter List associated with the query.

SELECT CONVERT(VARCHAR, COALESCE(MAX(DOCDATE), '1900-01-01'), 111) AS DOCDATE
FROM PM00400 WHERE VENDORID = ''/*%01%*/

One thing that is worth noting is that the system which automatically substitutes parameters into the scripts will handle when there are single quotes in the entered data to ensure the code still works and is protected from SQL Injection security risks.

Here is what the Parameter List Entry window looks like when we execute the SQL Script:

More Information

For more information, check out the links below:

In the next post we will create the trigger which will use the SQL Query and Parameter List created in this article.

David

PS: If you need to update GP Power Tools to the latest code, make sure all workstations on your system are updated to the latest release.

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

4 thoughts on “#GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 2

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.