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

David Meego - Click for blog homepageFinishing this week’s series on customising the Payables Transaction Entry window using GP Power Tools, today we update the Trigger script and make everything work.

If you missed the previous articles please check them out below:

The final step is to update the Trigger script to perform the desired actions to check the last transaction date and open the Vendor Address Inquiry window.

We now have a trigger working for the correct event, we just need to update the script to use the SQL Script and check the date of the last transaction (if there is one).

We will be going through the steps to create the script, but if you want you can skip to the end to see the completed script or even download the entire project.

Opening Trigger Setup

If there are triggers running when you attempt to open Trigger Setup you will receive the dialog “Please unregister all triggers first.”:

Then the Trigger Status window will open and you can select Unregister >> All Triggers. Then open Trigger Setup again.

  1. From the GP Power Tools Area Page, select Cards >> Project Setup.
  2. Enter or select the Project ID: VENDOR REMIT AD
  3. Double click on the VENDOR REMIT AD Automatic Trigger Mode Trigger to open the Trigger Setup window.

You can also open Trigger Setup directly:

  1. From the GP Power Tools Area Page, select Cards >> Trigger Setup.
  2. Enter or select the Trigger ID: VENDOR REMIT AD

Update the Trigger Actions

  1. Click on the Actions tab.
  2. Uncheck the option: Display Message to screen using desktop alert.

Update the Trigger Script – part 1

We are going to write our script inside the two if statements, above the OUT_Condition = true; line.  So we can click at the beginning of that line and prese enter a few times to make some space.

  1. Click on the Script Tab.
  2. Add some blank lines above OUT_Condition = true; line.
  3. Move to the first line in that space and click Helper.
  4. Select the Helper Function: Load SQL Execute Script ID
  5. Enter or select Script ID: VENDOR REMIT AD
  6. Click OK to insert the function.
  7. While the lines are still highlighted, press tab twice to indent to correct level.

  1. Click to the next line in the script and click Helper.
  2. Select the Helper Function: Load Parameter List ID. If this option is not available, please update your GP Power Tools install.
  3. Enter or select Parameter ID: VENDOR REMIT AD
  4. Click OK to insert the function.
  5. While the lines are still highlighted, press tab twice to indent to correct level.

  1. Click to the next line in the script and click Helper.
  2. Select the Helper Function: Set Parameter List ID Value. If this option is not available, please update your GP Power Tools install.
  3. Enter or select Parameter ID: VENDOR REMIT AD
  4. Use the lookup to select the Vendor ID, or enter Position 1, From/Single.
  5. Click OK to insert the function.
  6. While the lines are still highlighted, press tab twice to indent to correct level.
  7. Replace <Variable> with IN_NewValue or ‘Vendor ID’ of window PM_Transaction_Entry of form PM_Transaction_Entry. The parameter will have the same value as the window field.

  1. Click to the next line in the script and click Helper.
  2. Select the Helper Function: Execute SQL Select Statement.
  3. Enable Return Data, but not Show Column Names
  4. Click OK to insert the function.
  5. While the lines are still highlighted, press tab twice to indent to correct level.
  6. Remove the two lines which clear MBS_Text_Field and set it to a dummy SQL statement. This will allow the script loaded by the previous helper function to be used instead.

Test the Trigger Script – part 1

This would be a good time to test what we have so far.

  1. Press Ctrl-S to save the script.
  2. Close the Trigger Setup window.
  3. From the Project Setup window, Start the triggers for the project.
  4. Go to the Payables Transaction Entry window.
  5. Enter a Vendor ID and move off the field.
  6. The trigger should run, load the SQL Script, load the Parameter List, set the parameter programmatically and then execute the script and display the resulting date.
  7. Close the transaction window and delete the incomplete transaction.

Update the Trigger Script – part 2

  • From the Project Setup window, Stop the triggers for the project.
  • Double click on the VENDOR REMIT AD Automatic Trigger Mode Trigger to open the Trigger Setup window.
  • Click on the Script tab.
  • At the bottom of the local variable declarations add the following:

local date l_date;
local integer l_year, l_month, l_day, l_numdays;
local string l_message;

  • In the case statement for the MBS_SQL_Check_Exists helper function for the in [OKAY] section remove the warning MBS_Text_Field; and replace it with the following lines to read the string date and convert it to a date datatype:

l_year = integer(value(substring(MBS_Text_Field, 1, 4)));
l_month = integer(value(substring(MBS_Text_Field, 6, 2)));
l_day = integer(value(substring(MBS_Text_Field, 9, 2)));
l_date = mkdate(l_month, l_day, l_year);

  • After the case statement add code to check the date against the system date or the User Date and if there has not been a transaction for 60 days, display a dialog and open the Vendor Inquiry window.
  • Remove the OUT_Condition = true; line near the end of the script as it is replaced by the same line inside the conditional statements above.

Below is the complete script which can be cut and pasted in, or you can import the entire project using the steps and file below.

in string IN_OldValue;
in string IN_NewValue;
out boolean OUT_Condition;

local text MBS_Text_Field;
local integer MBS_Status;
local string MBS_Value_String;

local date l_date;
local integer l_year, l_month, l_day, l_numdays;
local string l_message;

OUT_Condition = false;

if isopen(form PM_Transaction_Entry) then
	if not empty('Vendor ID' of window PM_Transaction_Entry of form PM_Transaction_Entry) then

		call with name "MBS_Script_Load_SQL" in dictionary 5261,
			"VENDOR REMIT AD", MBS_Text_Field;

		call with name "MBS_Parameter_Load" in dictionary 5261, "VENDOR REMIT AD", MBS_Status;

		MBS_Value_String = IN_NewValue;
		call with name "MBS_Parameter_Set_String" in dictionary 5261,
			1 {Position: "Vendor ID"}, 0 {FromTo: From/Single}, MBS_Value_String;

		call with name "MBS_SQL_Check_Exists" in dictionary 5261,
			MBS_Text_Field, true {Return Data}, false {Show Names}, MBS_Status;
		case MBS_Status
			in [OKAY]
				l_year 	= integer(value(substring(MBS_Text_Field, 1, 4)));
				l_month = integer(value(substring(MBS_Text_Field, 6, 2)));
				l_day 	= integer(value(substring(MBS_Text_Field, 9, 2)));
				l_date 	= mkdate(l_month, l_day, l_year);
			in [MISSING]
				warning MBS_Text_Field;
			else
				warning MBS_Text_Field;
		end case;

		{ Vendor has Transactions, so check how many days ago against current date or user date }
		if not empty(l_date) and l_date > mkdate(1,1,1990) then
			l_numdays = {sysdate()} 'User Date' of globals - l_date;
			if l_numdays > 60 {or true} then
				l_message = "This vendor has not had a transaction since %1 (%2 days ago). " +
							"Please review the current vendor Remit To address and compare to the invoice address.";
				substitute l_message, str(l_date), str(l_numdays);
				warning l_message;

				{ Open Vendor Inquiry to Remit to Address }
				call Open of form PM_Vendor_Inquiry, IN_NewValue;
				if isopen(form PM_Vendor_Inquiry) then
					'Vendor Address Code - Primary' of window PM_Vendor_Inquiry of form PM_Vendor_Inquiry =
						'Vendor Address Code - Remit To' of window PM_Transaction_Entry of form PM_Transaction_Entry;
				end if;

				OUT_Condition = true;
			end if;
		end if;

	end if;
end if;

Test the Trigger Script – part 2

To test this script you might need to uncomment the or true condition in the if statement to force it to run the code even if it is not showing a date older than 60 days. If you make that change, be sure to comment it back out again after testing.

The process to test is the same as the previous testing section, so there is no need to repeat it again here. After entering the Vendor ID, if the condition is met, the following dialog will be displayed.

After the warning dialog is displayed, the Vendor Inquiry window opens directly to the Remit To Address ID:

Exporting the Project

Now that the code is completed, tested and working, you can go to the Project Setup window and click Export to save the configuration settings xml file. After exporting, click on the Configuration File Path hyperlink if you want to open the folder where the exported file is located.

Importing the Project

To import the project, copy the attached zip file to your system and extract the xml file. From the GP Power Tools Area Page, select Routines >> Configuration Export/Import. Select the path to the xml file and click Import. Click OK on the confirmation dialog to import the objects into your system.

Note: You might want to open the Project Setup window and click the Reset Path button to set the path to a location appropriate for your system. You can then Save and close the window.

Deploying the Project for Desktop Client

Already done.

Just by importing the project, it is already deployed and will become active when the user next logged into Microsoft Dynamics GP. This is assuming that GP Power Tools is installed on all workstations as per the recommended configuration.

Deploying the Project for Web Client

Already done.

Assuming that GP Power Tools is installed in the application folder used for the web client.

More Information

For more information, check out the links below:

I hope you found this example useful.  The exported Configurations Settings file for the project is attached below:

  • Download the GP Power Tools Configuration Settings file from here.

David

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 4

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.