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

David Meego - Click for blog homepageAdding to this week’s series on customising the Payables Transaction Entry window using GP Power Tools, I thought it would be interesting to show some variants of the trigger script using less and less of the built in GP Power Tools features.

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

The idea is to show how the helper functions simplify your code.

The following changes just show how we can achieve exactly the same functionality using different helper functions or native Dexterity code. Each of these versions below moves further away from “Best Practice”, but still works.

Remove the Parameter List

The first change we can make is to not use the Parameter List ID in our script. In our original script after loading the SQL Execute Script ID with the MBS_Script_Load_SQL helper function, we used the MBS_Parameter_Load helper function to load the Parameter List ID and MBS_Parameter_Set_String helper function to populate the first parameter with the value from the window. Now all scripts will automatically have the parameter value substituted in for the placeholder just before they are executed.

Another approach is to manually make the substitution in the SQL script ourselves rather than using the Parameter List approach. This will only affect this one instance of the script as it is not using the Parameter List system.

Instead, we use the following helper functions MBS_Parameter_Placeholder to get the string representation of the placeholder used in the SQL script.

Followed by the helper function MBS_Parameter_String to get the string representation of the value we wish to substitute in.

And then we use the helper function MBS_Script_Substitute to manually make the substitution in the script text.

Below is the resulting Trigger Script which could be used instead of the original:

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 string MBS_Placeholder;
local string MBS_Value;

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_Placeholder" in dictionary 5261, 
			1 {Type: String}, 1 {Position}, 0 {FromTo: From/Single}, 2 {Language: SQL Transact-SQL}, MBS_Placeholder;

		MBS_Value_String = IN_NewValue;
		call with name "MBS_Parameter_String" in dictionary 5261, 
			MBS_Value_String, 2 {Language: SQL Transact-SQL}, MBS_Value;

		call with name "MBS_Script_Substitute" in dictionary 5261, 
			MBS_Text_Field, MBS_Placeholder, MBS_Value;

		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;

Remove the SQL Execute Script

We can also remove the SQL Execute script, by placing the SQL code directly into the Trigger Script. We can just build up the SQL Script ourselves into the text variable and execute it as before. We must use SQL_FormatStrings() when passing the Vendor ID to put single quotes around the field and handle if there are any single quotes in the Vendor ID itself.

While this code might look simpler, you can no longer test the SQL Script independently from the Trigger. Below is the updated Trigger Script:

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

local text MBS_Text_Field;
local integer MBS_Status;

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

		clear MBS_Text_Field;
		MBS_Text_Field = MBS_Text_Field + "SELECT CONVERT(VARCHAR, COALESCE(MAX(DOCDATE), '1900-01-01'), 111) AS DOCDATE" + char(13);
		MBS_Text_Field = MBS_Text_Field + "FROM PM00400 WHERE VENDORID = " + SQL_FormatStrings(IN_NewValue) + char(13);
		
		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;

Remove all Helper Functions

For a final example, we shall remove the MBS_SQL_Check_Exists helper function which handles the execution of the SQL Script for us and replace it with the native Dexterity SQL_Execute() function. This will make the code much larger as this helper function handles all the processing of the returned data set.

Here is the updated code for the Trigger Script:

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

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

local long SQL_connection, status;
local text SQL_Statements;

{SQL error information}
local long GPS_error_number, SQL_error_number;
local string SQL_error_string, ODBC_error_string;

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
		
		{Connect to the SQL data source.}
		status = SQL_Connect(SQL_connection);
		if status = 0 then
		    {Build SQL statement to use the appropriate database.}
		    SQL_Statements = "use " + 'Intercompany ID' of globals;
		    {Execute the SQL statements.}
		    status = SQL_Execute(SQL_connection, SQL_Statements);
		    if status = 0 then
			    {Build the SQL statements}
				clear SQL_Statements;
				SQL_Statements = SQL_Statements + "SELECT CONVERT(VARCHAR, COALESCE(MAX(DOCDATE), '1900-01-01'), 111) AS DOCDATE" + char(13);
				SQL_Statements = SQL_Statements + "FROM PM00400 WHERE VENDORID = " + SQL_FormatStrings(IN_NewValue) + char(13);
	            {Execute the SQL statements.}
	            status = SQL_Execute(SQL_connection, SQL_Statements);
	            if status = 0 then
	                {Retrieve data from the first results set.}
	                status = SQL_FetchNext(SQL_connection);
	                if status <> 31 then
	                    {Get the information about the seller.}
	                    status = SQL_GetData(SQL_connection, 1, l_string);
						l_year 	= integer(value(substring(l_string, 1, 4)));
						l_month = integer(value(substring(l_string, 6, 2)));
						l_day 	= integer(value(substring(l_string, 9, 2)));
						l_date 	= mkdate(l_month, l_day, l_year);
	                else
	                    error "No results returned.";
	                end if;
	            else
	                error "An error occurred executing SQL statements.";
	                {Retrieve the specific error information.}
	                status = SQL_GetError(SQL_connection, GPS_error_number, SQL_error_number, SQL_error_string, ODBC_error_string);
	                if status = 0 then
	                    warning "GPS Error: " + str(GPS_error_number);
	                    warning "SQL Error: " + str(SQL_error_number) + " " + SQL_error_string;
	                    warning "ODBC Error: " + ODBC_error_string;
	                else
	                    error "Unable to retrieve SQL error information.";
	                end if;
		        end if;
		    else
		        error "Could not switch to the correct database.";
		    end if;
		    {Disconnect from the SQL data source.}
		    status = SQL_Terminate(SQL_connection);
		else
		    {An error occurred creating the pass-through SQL connection.}
		    warning "An error occurred creating the pass-through SQL connection: " + str(status);
		end if;
				
		{ 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;

As you can see the helper functions make it a lot simpler to perform complex tasks without needing in depth programming knowledge.

More Information

For more information, check out the links below:

I hope you find this extra article interesting. The updated 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.

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.