#GPPT Using GP Power Tools to track specific changes Revisited

David Meego - Click for blog homepageBack in 2017 I wrote an article which demonstrated how to get GP Power Tools to send an email every time the Credit Limit Field on the Customer Maintenance window was edited by a user and saved.

This article revisits this article by enhancing the functionality with the options to log changes to a text file or a SQL table instead of sending an email.

The original trigger has been added to a project in the Project Setup window and the rest of the components are now all part of this project.

The event used for all three triggers is the table save event for the RM_Customer_MSTR (RM00101) table specifying the Credit Limit Amount field. GP Power Tools will track the previous value of the field so the code in the trigger can tell if the field has been changed when it is saved.

Trigger 1: Send Email

This trigger uses the trigger action to send an email to the administrator (or specified email address) and also export an xml file of the saved record. Both of these options are selected on the Actions Tab of the Trigger Setup window.

If an email is being generated by a GPPT trigger, anything written to the log using the MBS_Auto_Log Helper Function is included in the email, so adding the log entries for the Customer and the old and new values will add that information to the email sent.

inout anonymous table RM_Customer_MSTR;
in integer IN_Operation;
in string IN_OldValue;
in string IN_NewValue;
out boolean OUT_Condition;

OUT_Condition = false;

case IN_Operation
	in [TRIGGER_ON_DB_ADD]
		OUT_Condition = false;

	in [TRIGGER_ON_DB_UPDATE]
		if IN_NewValue <> IN_OldValue then
			OUT_Condition = true;
			call with name "MBS_Auto_Log" in dictionary 5261, "Customer: " + column("Customer Number") of table RM_Customer_MSTR;
			call with name "MBS_Auto_Log" in dictionary 5261, "Old Credit Limit: " + IN_OldValue;
			call with name "MBS_Auto_Log" in dictionary 5261, "New Credit Limit: " + IN_NewValue;
			call with name "MBS_Auto_Log" in dictionary 5261, "";

		end if;

	else
end case;

 

Trigger 2: Save to Log File

This trigger no longer sends an email, instead it uses the Dexterity TextFile_ Function Library commands to open a text file to append to. It will write a tab delimited line including the date and time, the User ID and Intercompany ID followed by the Customer ID and the Old and New values for the Credit Limit Amount.

The script will use the GP Power Tools Logging Path if defined or the Application Data folder if no logging path defined.

inout anonymous table RM_Customer_MSTR;
in integer IN_Operation;
in string IN_OldValue;
in string IN_NewValue;
out boolean OUT_Condition;

local long l_characters_written;
local boolean l_result;
local string l_pathname;
local integer l_file_ID;
local string l_output;

OUT_Condition = false;

case IN_Operation
	in [TRIGGER_ON_DB_ADD]
		OUT_Condition = false;

	in [TRIGGER_ON_DB_UPDATE]
		if IN_NewValue <> IN_OldValue then
			OUT_Condition = true;
			l_output = str(sysdate()) + char(9) + str(systime()) + char(9) + 
						'User ID' of globals + char(9) + 'Intercompany ID' of globals + char(9) +
						column("Customer Number") of table RM_Customer_MSTR + char(9) +
						IN_OldValue + char(9) + IN_NewValue;

			l_pathname = Defaults_Read("MBS_Debug_LogPath");
			if not empty(l_pathname) then
				l_pathname = Path_MakeGeneric(l_pathname);
			else
				l_pathname = Path_GetForApp(PATH_DATAFOLDER);
			end if;
			l_pathname = l_pathname + "CUSTCREDITLIMIT.TXT";
			
		    l_file_ID = TextFile_Open(l_pathname, 1, 2);
			l_characters_written = TextFile_WriteDOS(l_file_ID, l_output);
		    l_result = TextFile_Close(l_file_ID);

		end if;

	else
end case;

 

Trigger 3: Save to SQL Table

This final trigger swaps the log file for a SQL Table. The code becomes a bit more complex, but works really well.

There is a SQL Script written using the SQL Execute Setup window to create the GPPT_Customer_Credit_Limit table in the company database and grant access to DYNGRP. There is also a script which can be used to clear the contents of the SQL Table.

However, the most import SQL script handles the insert into the table. It has a Parameter List created with the Parameter List Maintenance window which it uses as temporary storage for the data to be saved. If you run the script directly, it will ask for the data to be saved and then insert a record into the SQL table.

When the trigger fires, it uses Helper Functions to load and execute the insert script. It also loads the Parameter List and populates the Parameter List values programmatically with the Customer ID, Old Value and New Value and the current User ID. When the SQL script executes, the parameter list values are automatically substituted into the code.

inout anonymous table RM_Customer_MSTR;
in integer IN_Operation;
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 currency MBS_Value_Currency;

OUT_Condition = false;

case IN_Operation
	in [TRIGGER_ON_DB_ADD]
		OUT_Condition = false;

	in [TRIGGER_ON_DB_UPDATE]
		if IN_NewValue <> IN_OldValue then
			OUT_Condition = true;

			call with name "MBS_Parameter_Load" in dictionary 5261, "CUSTCREDLIMIT3", MBS_Status;

			{ Add additional calls to the MBS_Parameter_Set Helper Functions to set the parameter values here }
			MBS_Value_String = column("Customer Number") of table RM_Customer_MSTR;
			call with name "MBS_Parameter_Set_String" in dictionary 5261, 
				1 {Position: "Customer ID"}, 0 {FromTo: From/Single}, MBS_Value_String;

			MBS_Value_Currency = currency(value(IN_OldValue));
			call with name "MBS_Parameter_Set_Currency" in dictionary 5261, 
				2 {Position: "Credit Limit Old"}, 0 {FromTo: From/Single}, MBS_Value_Currency;

			MBS_Value_Currency = currency(value(IN_NewValue));
			call with name "MBS_Parameter_Set_Currency" in dictionary 5261, 
				3 {Position: "Credit Limit New"}, 0 {FromTo: From/Single}, MBS_Value_Currency;

			MBS_Value_String = 'User ID' of globals;
			call with name "MBS_Parameter_Set_String" in dictionary 5261, 
				4 {Position: "User ID"}, 0 {FromTo: From/Single}, MBS_Value_String;

			call with name "MBS_Script_Load_SQL" in dictionary 5261, "CUSTCREDLIMIT3", 
				MBS_Text_Field;

			call with name "MBS_SQL_Check_Exists" in dictionary 5261, 
				MBS_Text_Field, false {Return Data}, false {Show Names}, MBS_Status;
			case MBS_Status 
				in [OKAY] 
{					warning MBS_Text_Field;
}				in [MISSING] 
{					warning MBS_Text_Field;
}				else 
					warning MBS_Text_Field;
			end case;

		end if;

	else
end case;

 

Summary

These scripts just show some alternate ways that changes to a specific field can be tracked and how to use Dexterity functionality like the TextFile_ Function Library. The final technique using SQL Scripts with Parameter Lists and programmatically populating the values in the Parameter List is a really powerful way to get GP Power Tools to do all the “grunt” work for you.

Stay tuned as I will be posting a couple of even more powerful example projects which can monitor changes to Vendors and Customers across multiple fields, in multiple windows and multiple tables and log the changed data to a SQL table.

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):

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:

David

26-Aug-2021: Added extra related articles to the More Information section.

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

2 thoughts on “#GPPT Using GP Power Tools to track specific changes Revisited

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.