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

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”.

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.

  1. Create and name a new macro.
  2. On the Trigger page, uncheck “Run in the background on Server” field
  3. Select the On Edited trigger and click on the radio button “An Indirect Change Event”
  4. 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.

Explanation of Code choice:

1.) As a precaution against returning more than one record, we limit the return to 1 with the top(1) statement. To be sure that it is the latest record, we order by TableChangeLogSummary.ModifiedDate and instruct the order to be descending.

2.) Because the macro will fire each time there is an edit, if we only want it to fire when the change is made and not every time the transaction is edited, we need to include a filter for that. We have found that by specifying that the change must have occurred within a minute or two of the macro accomplishes this well. Hence the Datediff() expression in the last line.

  1. SELECT Top(1)
    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
    --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.

Contributor Steve Gillispie Acorn Sign Graphics

Date: 10/18/2017

Version: Control 6.1

You could leave a comment if you were logged in.