Description
Describes how to use a value in a UDF field or a change in values in a UDF field as part of a macro trigger
Concept
Basic Discussion of Approach
We want a macro to be triggered to generate an email when a date has been entered in a UDF or when the date has been changed in that UDF. To do that we use the TableChangeLogSummary and TableChangeLogDetail tables. These are not standard in Cyrious installations but can be added. Scott St Cyr has posted how to set them up and noted a caution – once set up the file will build quickly so a protocol for deleting records no longer needed should be included in the setup if possible.
The TableChangeLogSummary table is the master table which contains the links to the set of records of which UDFs have been changed on an Edit. The actual records are stored in the TableChangeLogDetail table in a field called ColumnName. The Summary Table contains the ID of the table to which the UDFs are linked – E.g. Account or TransHeader for Estimates, Work Orders, and Service Tickets as well as the date of the edit (change) and the link to the associated records (the Detail table).
For the macro trigger we link the TransHeaderID to the TableChangeLogSummary with the latter's ID. We capture the required UDF value in the Where statement using the “Like” operator. In the example for this posting, the UDF is named “Contracted_Due_Date” The value of the UDF before the edit is in the field “OldValue” and the new, edited value in the field “New Value”.
Cautions
As with all macros, there is the potential to degrade the system performance if not properly written. However, this macro only reads the data so there is no danger that the database will be compromised. Cyrious has advised that all From and Join statements include the “with(nolock)” statement.
Steps
- Create and name a new macro.
- On the Trigger page, uncheck “Run in the background on Server” field
- Select the On Edited trigger and click on the radio button “An Indirect Change Event”
- Enter the SQL code. We highly recommend that the SQL code be developed and tested in an SQL editor before pasting in to the Cyrious formalu box. We use MIcrosoft SQLServer 2014. If it runs there, 98% of the time it runs well in Cyrious.
SELECT Top(1) TH.ID, TH.ClassTypeID FROM TransHeader TH WITH(nolock) LEFT JOIN TableChangeLogSummary TCLS WITH(nolock) ON TH.ID = TCLS.ID LEFT JOIN TableChangeLogDetail TCLD WITH(nolock) ON TCLS.SummaryID=TCLD.SummaryID WHERE --TH.ID = 153015 = TH.ID AND columnname LIKE '%Contracted%' AND OldValue IS NULL AND COALESCE(OldValue,'') COALESCE(NewValue,'') AND DATEDIFF(MINUTE,TCLS.ModifiedDate,GetDate()) < 3 ORDER BY TCLS.ModifiedDate DESC
This macro fires and sends an email when the UDF, Contracted_Due_Date has a date entered for the first time – E.g. the OldValue is Null. For the macro which fires when the date is updated, we change that line to OldValue IS NOT NULL.
Source
Contributor Steve Gillispie Acorn Sign Graphics
Date: 10/18/2017
Version: Control 6.1