Description

Macros, report sorts and groupings, and other data management tasks where UDF values are determinative often require the date the entry was made. This posting describes how to retrieve that date when a value was entered or the value entered on a date or in a date range.

Basic Discussion of Approach

Cyrious has created two tables which can be installed at the User's request in the StoreData database which provide a detailed log of UDF transactions with information such as

  • the date entered
  • the user computer from which the data was entered
  • the UDF affected
  • the date the last entry was made
  • the previous UDF value
  • the new UDF value entered

For installations dependent on UDFs these tables can be extraordinarily useful in expanding the automation you can achieve from Cyrious in creating automated emails, reports, warnings, alerts, etc.

The how_to_use_udf_change_log_tables_for_macros_and_reports and their use are described in more detail in other posting on this WIKI.

For this application we want to find the Log entry for the UDF in question and return the Modified Date.

The query is a read only query and does not affect the database.

There are several variations of the use of the Table Change Log tables to return information or trigger macros. Hopefully, this is enough to provide the bases to tweak the code to the reader's own unique needs and UDF names.

  1. USE IN CRYSTAL

The code below is written for inserting into a Crystal Command and returns the date when a UDF was changed and the User Name who made the change.

In the case below we wanted to know who changed the UDF "GCB_Status" to "BID" and on what day if it were changed in the period 9/1 to 9/20. Note that we added the condition in the Join of the Table Change Log Detail that the join occur only if the Column Name matches the UDF we are querying -- in the example below "GCB_Status".

--SQL CODE FOR USE IN CRYSTAL COMMAND
--Returns the Date of the Change and Who Changed
--For An Order Where the Entry Was Made in the Date Range
--Shows the Parameters passed between Cyrious, Crystal, and SQL
DECLARE @OrderID nvarchar (10)
DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @StartDate = {?fx_RangeStart_Date} --crystal must have this parameter and
                                       --+ an additional Date Range paremeter named DATE
SET @EndDate = {?fx_RangeEnd_Date}--crystal must have this parameter
SET @OrderID = {?fx_Order_OrderID}--crystal must have this parameter
--Set @StartDate = '9/1/2017'
--Set @EndDate = '9/20/2017'
--Set @OrderID = 146637
SELECT
UDFModDate = TCLS.ModifiedDate,
EnteredBy = TCLS.ModifiedByUser
FROM TableChangeLogSummary TCLS WITH(nolock)
LEFT JOIN TableChangeLogDetail TCLD WITH(nolock) ON TCLS.SummaryID=TCLD.SummaryID AND ColumnName ='GCB_Status'
WHERE @OrderID = TCLS.ID AND
ColumnName = 'GCB_Status' AND NewValue = 'Bid' AND
TCLS.ModifiedDate BETWEEN @StartDate AND @EndDate;

2. USE IN MACRO CODE

The code below is designed for macros. It is testing the UDF “GCB_Status” to determine if the entry “BID” has been made on a specific date.

There are two date criteria offered depending on how the macro is launched. For real time macros triggered by On New or On Edit we often want to limit the trigger to a specific time period such as 5 minutes so the macro doesn't fire every time the transaction is edited that day. For that we subtract the difference in minutes from when the entry was made to when the macro is triggered and set a limit of 5 minutes or less.

For batch macros we only want to capture entries made during the day.

--SQL CODE FOR A MACRO RETURNS TRUE
--IF ENTRY MADE ON THE CURRENT DATE
SELECT
TCLS.ID,
TCLS.ClassTypeID
FROM TableChangeLogSummary TCLS WITH(nolock)
LEFT JOIN TableChangeLogDetail TCLD WITH(nolock) ON TCLS.SummaryID=TCLD.SummaryID AND ColumnName ='GCB_Status'
WHERE
 = TCLS.ID
AND TCLD.ColumnName = 'GCB_Status' -- the column name is the name of the UDF
AND NewValue ='Bid'  -- the NewValue contains the entry made for the UDF in string format
AND
 DATEDIFF(MINUTE,TCLS.ModifiedDate,GETDATE()) < 5 -- Return True if entry made in last 5 minutes when Triggered
--CAST(TCLS.MODIFIEDDATE AS DATE) = CAST(GetDate() AS DATE) Returns True if Entry made the same day as Triggered

Contributor:Steve Gillispie, Acorn Sign Graphics

Date: 9/18/2017

Version: Control 6.1_

You could leave a comment if you were logged in.