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.
Concept
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.
Cautions
The query is a read only query and does not affect the database.
Actions
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.
- USE IN CRYSTAL
--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
Source
Contributor:Steve Gillispie, Acorn Sign Graphics
Date: 9/18/2017
Version: Control 6.1_