Today’s article comes after a GP Power Tools customer asked for the ability to track when the Credit Limit on a Customer Master record was changed.
A user of the system had made changes to a customer’s credit limit without permission and the financial controller wanted to be notified if this ever happened again.
While GP Power Tools is not designed as a generic auditing tool (Like Rockton Software’s Auditor), it can be used really easily to track changes to any specific field using a non-logging trigger. As this functionality involves scripting it does require the Developer Tools module to be registered.
All we need to do is use the Trigger Setup window to create a single trigger. Just enter the following fields:
Header
- Trigger ID: CUSTCREDITLIMIT
- Trigger Description: Monitor Customer Credit Limit Changes
- Trigger Type: Table
- Trigger Event: Save Record
- Trigger Attach: After Table Event
- Start Trigger Automatically on Login: Selected
- Do not activate Logging Mode: Selected
- Minimize Log Entries: Selected
Resources Tab
- Product Name: Microsoft Dynamics GP
- Table Name: RM_Customer_MSTR
- Field Name: ‘Credit Limit Amount’
Actions Tab
- Send Email using Administrator Email or Address below: Selected
- Email Address: <Optional if you want a specific email address>
- Export Current Table Record to XML: Selected
Script Tab
- Context: Microsoft Dynamics GP
- Script: See below for minor alterations to default script
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;
Options Tab
- No Changes needed.
If you want to download this example, the settings file is below:
Use the Email Settings window to set up the default administrator’s email address and the Email Server to use. To avoid seeing the email being sent turn off Preview and turn on Auto Send. Using an Email Mode of SMTP Server via CDO will also avoid emails showing in Outlook’s Sent Items.
This is just a simple example of how to track changes for a field, the same concept can be used on any field in any table in any product dictionary.
For more information see the GP Power Tools Portal:
- #GPPT Using GP Power Tools to track specific changes Revisited
- #GPPT Tracking changes to Customer and Vendor Cards
- http://WinthropDC.com/GPPT
Enjoy
David
26-Aug-2021: Added extra related articles to the More Information section.
This article was originally posted on http://www.winthropdc.com/blog.
This is awesome David, I have been trying to work out a method of doing this not involving SQL triggers or SQL in general as this ticks all the boxes.
LikeLike
Hi Arthur
I am glad you like this demo. I plan on publishing many more scripting examples to show how powerful the Developer Tools module of GP Power Tools really is.
David
LikeLike