UDFs are one of the most powerful elements of Cyrious code. They permit the user to effect considerable customization of their Cyrious data for a wide variety of purposes. They can be used to trigger macros for emails and reports, record important metrics, document and notify of workflow steps such as the completion of production art, and a myriad of other uses. However, until the creation of the how_to_create_a_database_table_change_log_using_a_sql_trigger users had no way to identify and take action on UDF edits. The Change Log tables fixed that problem and users now have the data as to what UDF was edited, by whom, when, and the UDF's previous and changed value. .

Concept

StoreData has two tables which log every change to every UDF. The data includes the UDF name, the date it is changed, the previous date on which it was changed, which computer made the change, the value before the change and the value after the change.

This information permits the use of UDFs for a wide variety of purposes such as macros triggered when a change is made or the UDF contains a certain value. All this information is made accessible by two tables: (1) The TableChangeLogSummary and the (2) TableChangeLogDetail. Using them requires an understanding of how they are constructed and some SQL “tricks” to make them actionable. Crystal users will find it easier and often much faster to retrieve data from these tables and reformat it to the correct data type using SQL and pasting the code to Crystal Command input capability for use in Crystal reports. Using the command page with the Record Select works well. For example, you can retrieve all UDFs according to certain criteria in SQL and use the Crystal record select to filter with a date range.

This posting is designed to help users create or edit reports and macros based on UDF data in transactions. The code snippets included can be pasted directly into an SQL editor or the Crystal command page with UDF names and aliases easily changed for a quick start on a new macro or report. Code unique to mining data from these tables is explained. You will note that the code includes certain conventions common to all Cyrious SQL code-E.g. tables in the From clause should have the with(nolock) function. It is a good practice to bracket code in the Where clause by parentheses. The coalesce(x,y) function which returns the first non-null value is often unnecessary but a good safety measure against wonky results (in Crystal) from NULL values, as you can specify a NULL value or replace NULL values with the “y” entry.

Cautions

Nothing in the code and concepts offered herein will change the StoreData database. The only caveat is one true for all data mining. Data is complex and conditions can exclude wanted as well as unwanted records. Test everything carefully to be sure that your code is returning all the data it is supposed to before implementing it for any reports or macros which might have adverse affects on your business if the data is faulty.

Not a caution but obviously the UDF names in this posting are UDFs used by Acorn Sign Graphics. Simply substitute your own UDF names in each instance where a UDF name is shown in the code.

Finally, SQL is so powerful that there are usually many ways to accomplish the same thing. The code suggested here works but in most cases is not the only way, particularly in the discussion of combining the detail records . If in doubt, consult with Cyrious technical support.

TableChangeLogSummary Table

An entry to this table is created each time a UDF field is changed and saved. It's purpose is to point to a list of all UDFs changed and to record who (the host computer) changed them, the time of the change, the time of the previous change, and the UDF table in which the UDFs are found.

The column names shown in the picture below are the field names for each of the fields accessible in the TableChangeLogSummary. The fields key to using the tables in queries are the SummaryID and the ID fields.

The SummaryID is the link to the detail table in which the changed UDFs are listed.

For Estimates and Orders, the ID field of the Summary table points to the TransHeader record of the transaction in which the UDF is changed. In other words, if a UDF has been changed in an Order, the ID of the TableChangeLogSummary will be the same as the TransHeader.ID of the transaction.

The query below shows that from the computer named steve.gillispie 3 separate changes were made to UDFs and saved to TransHeader.ID 137035 on 4-14-20116 between 9:59 PM and 10:51 PM. The UDFs are found in the TransHeaderUserField (as opposed to a Company or Contact User Field or other userfield table). The change type (U) indicates that these were updates.

TableChangeLogDetail Table

This table contains the “pointer” to the summary table – the SummaryID – and the list of all UDFs changed in a specific transaction–their previous value (OldValue) and their new value (NewValue). By comparing with the table above, you will see that the SummaryIDs of these records match the SummaryID of TableChangeLogSummary table.

Joining The Tables For Query Results

For most applications, these tables must be joined together to be useful in a query. For example, the LogDetail table has no pointer to the transaction in which the UDF is changed or a field for who made the change.

The type of Join is not significant but since you only want records returned where there are matching entries between tables, the Inner Join is probably technically the most correct and would be preferred when you employ conditions in the Join statement as described later.

The picture below illustrates the joining of the tables and limiting the search to specific records. The Where clause limits the search to records from both tables to those with a SummaryID of 1198.

To work with the results you must be aware of the data types of the fields. In the Summary table the data types are DateTime for the date fields and Text for the text fields.

In the Detail table with the exception of the two ID columns the data types are all text or string. So when using these fields in a condition clause (E.g. Where clause) or returning them to a Crystal template, they must be converted back to their original date or number types.

In the example below you see that the DR_Number has been changed from an OldValue of 2 to a NewValue of 3 and the DR_No_Completed from 1 to 2, shown in a string in scientific notation. To convert these strings to a number Crystal can format or use for calculations, you can use the SQL function Cast to a type float (12) with an optional number of decimal places in parentheses.

Specify an Integer
Cast(TableChangeLogDetail.OldValue as FLOAT)
Specify a Decimal
Cast(Cast(TableChangeLogDetail.OldValue as FLOAT) as DECIMAL(18,4))
[per Scott StCyr the (18,4) is considered the safe setting but obviously smaller numbers will work for most ASG applications.]

To convert the OldValue and NewValue fields which contain datetime data, you combine the SQL Convert function with the CAST function. The Cast As Date appears to work but Crystal sees this as a string, not a date; so you must use the convert function to use the date in Crystal or other formulas including Trigger formulas in SQL for macros.

Convert(Datetime,CAST(LD.NewValue AS DATE))

Using the Tables to Test For a Transaction Action

The power in these tables is that they can be used to test for specific transactions to trigger macros or generate reports.

The example below shows a query which will trigger a macro to pull all Orders where Production Art is marked complete on the day run which is then used to send emails to the salespersons for those orders.

We join the two Log tables aliased as “TS” and “TD” to the TransHeader table aliased TH . For maximum speed we add the columnname filter to the Join statement of the LogDetail table so only records for the DR_Prod_Art_Complete UDF are returned. Then in the Where statement we test that the UDF is set to 1 (True) and the date of the change is “today”. GetDate() is the SQL function to return today's date.

Select
TH.ID,
TH.ClassTypeID
from TransHeader TH with(nolock)
Left Outer Join TableChangeLogSummary LS with(nolock) on TH.ID = LS.ID
Inner Join TableChangeLogDetail LogDetail with(nolock) on LS.SummaryID = LogDetail.SummaryID AND ColumnName = 'DR_Prod_Art_Complete'
Where
 (
  Coalesce(NewValue,NULL) = 1
  AND CAST(LS.ModifiedDate) = Cast(GetDate() AS DATE)
 )

To make this an event-triggered query which will “fire” when the Production Art Complete UDF is checked, we need only make a couple of changes. (1.) Since these macros are triggered by an Event such as On Order Edit, we want to limit the macro to trigger only once when the User who makes the change saves the record. (2.) We need to tie it to an actual transaction.

Cyrious links macros to transactions with the function combined with an Event such as Order Edit. To limit the macro to only one occurrence, we fudge a bit. Since there is no simple flag or table indicating a macro was executed, we use DATEDIFF to subtract the current time of the host computer with the time of the modification. The assumption is that if the modification occurred within a minute of the system time in the user's computer, the record was created by this user in this sitting.

Select
TH.ID,
TH.ClassTypeID
from TransHeader TH with(nolock)
Left Outer Join TableChangeLogSummary LS with(nolock) on TH.ID = LS.ID
Inner Join TableChangeLogDetail LogDetail with(nolock) on LS.SummaryID = LogDetail.SummaryID AND ColumnName = 'DR_Prod_Art_Complete'
Where
 (
 TH.ID = 
  AND coalesce(NewValue,NULL) = 1
  AND DATEDIFF(minute,LS.ModifiedDate,SysDateTime()) < 1
  )

Combining TransDetail Records Into One Record Set

ChangeLog tables store the record for each UDF change independently, as shown below for a Second ReDo for Order 180491. Since these are retrieved sequentially, you can not use comparisons, math, evaluations, or other formulas on them. For example, you might want to retrieve ReDo reports based on the Date input (record/line 7), the Origin (record/line 4) and the severity (record/line 8). To Do that you must use some SQL alchemy to combine these all into one record.

Combining these records into a single record set is cumbersome but possible. You create a faux grouping by joining the detail record for each UDF to the Summary table with a condition that it only joins records with the UDF you want included. Use the Like command and % escape to name the UDF in a manner which will include all the UDF records you want. For example, in the example below in the Join for the Origin table, we want to capture UDFs named “RDO1_ReqFrom” and “RDO2_ReqFrom”; so we escaped out the first 5 characters of the UDF names. You format your result by aliasing the NewValue column the name of the UDF. This is shown in the query below.

Select
LS.SummaryID,
TH.OrderNumber,
ReqBy.NewValue ReqBy,
Origin.NewValue Origin,
CentersPassed.NewValue CentersPassed,
Cause.NewValue Cause,
Severity.NewValue Severity
FROM TableChangeLogSummary LS with(nolock)
INNER JOIN TableChangeLogDetail Origin with(nolock)
     on LS.SummaryID = Origin.SummaryID and Origin.ColumnName like '%ReqFrom%'
INNER JOIN TableChangeLogDetail Cause with(nolock)
        on LS.SummaryID = Cause.SummaryID and CAuse.ColumnName like '%Cause%'
INNER JOIN TableChangeLogDetail Severity with(nolock)
    on LS.SummaryID = Severity.SummaryID and Severity.ColumnName like  '%Sev%'
INNER JOIN TableChangeLogDetail CentersPassed with(nolock)
     on LS.SummaryID = CentersPassed.SummaryID and CentersPassed.ColumnName like  '%Centers%'
INNER JOIN TableChangeLogDetail ReqBy with(nolock)
       on LS.SummaryID = ReqBy.SummaryID and ReqBy.ColumnName like  '%Req_By%'
LEFT OUTER JOIN TransHeader TH with(nolock) ON LS.ID = TH.ID
Where LS.SummaryID = 530418

The picture below shows the result of this query for SummaryID 530418 (Order 180491). With this record set you can now add conditions to your Where clause such as Origin='Sales' or ModifiedDate in [Range].

Contributor: Steve Gillispie, Acorn Sign Graphics

Date: 11/13/2016

Version: Control 05.75.1610.3102

You could leave a comment if you were logged in.