Change Logs

Child forms to display change log record pose a challenge in many cases. For example, to display the change log records for an individual will require a comprehensive query with several UNIONs. For example, if your change log child form’s select sql is this:

SELECT 
	[FIELD] = isnull(mdc_description, mdc_name),
	[New Value] = log_value_new,
	[Old Value] = log_value,
	[User] = log_add_user + isnull(' (' + usr_friendly_name + ')', ''),
	[Date] = log_add_date,
	[Time] = convert(varchar(10), log_add_date, 108)
FROM fw_change_log (nolock)
LEFT JOIN md_column (nolock)
	ON mdc_name = log_mdc_name
LEFT JOIN fw_user (nolock)
	ON log_add_user = usr_code AND log_entity_key = usr_entity_key
WHERE
	log_delete_flag = 0
	AND log_record_key = {ind_cst_key}

This query will only return change log records for the Individual table. What about the phone, fax, email and other tables? To get these rows you will need pull in values from other tables as displayed below:

SELECT
  log_key,
  mdc_description AS [FIELD],
  log_value_new AS [New Value],
  log_value AS [Previous Value],
  log_add_user AS [Changed BY],
  log_add_date AS [CHANGE Date]
FROM 
  fw_change_log (nolock) JOIN md_column (nolock)
ON   mdc_name=log_mdc_name
WHERE log_delete_flag=0 AND log_record_key={ind_cst_key}
OR	log_record_key IN (SELECT cph_key FROM co_customer_x_phone (nolock) WHERE cph_cst_key ={ind_cst_key})
OR	log_record_key IN (SELECT cfx_key FROM co_customer_x_fax (nolock) WHERE cfx_cst_key ={ind_cst_key})
OR	log_record_key IN (SELECT phn_key FROM 
co_phone JOIN co_customer_x_phone (nolock) ON phn_key=cph_phn_key WHERE cph_cst_key ={ind_cst_key})
OR	log_record_key IN (SELECT fax_key FROM  co_fax JOIN co_customer_x_fax (nolock) ON fax_key=cfx_fax_key WHERE cfx_cst_key ={ind_cst_key})
OR 	log_record_key IN (SELECT eml_key FROM  co_email  WHERE eml_cst_key ={ind_cst_key})
 
ORDER BY log_add_date DESC,mdc_description

Another consideration is logging changes on columns that are av_key data type. It will make no sense to a user to see that the Registrant Type (reg_rgt_key) changed from "abc-122" to "xyz-989". Therefore if you choose to log such a column then you will need to expand your child form SQL to join out to the primary table to pull in the code from the primary table.

NetForum includes a VIEW and a UDF that simplifies writing change log child forms.

vw_fw_change_log_detail View

The VIEW vw_fw_change_log_detail can be called like this, where {zzz_key} is the primary key of the profile record being viewed:

SELECT log_key, 
FIELD, 
[New Value], [Old Value],
[User], [Date], [Time]
FROM  dbo.vw_fw_change_log_detail
WHERE log_record_key = {zzz_key}
ORDER BY [date] DESC

To prevent showing large blocks of text on a child form, you might want to clip the size of the values as shown below:

SELECT log_key, 
FIELD, 
[New Value] = convert(nvarchar(200),[New Value]), 
[Old Value] = convert(nvarchar(200),[Old Value]), 
[User], [Date], [Time]
FROM  dbo.vw_fw_change_log_detail
WHERE log_record_key = {zzz_key}
ORDER BY [date] DESC

av_fw_change_log_customer_key UDF

The av_fw_change_log_customer_key UDF is useful for showing old and new customer in a change log child form.

Parameters

• key (av_key). Primary Key of the record.
• mdc_name (sysname). Name of the column. Exact name or you can use wild-card. System will match columns with a LIKE expression, not an EQUALS expression.

Usage

Sample Child Form SQL:

SELECT log_key, 
FIELD, 
[New Value] = convert(nvarchar(2000),[New Value]), 
[Old Value] = convert(nvarchar(2000),[Old Value]), 
[User], [Date], [Time]
FROM  dbo.vw_fw_change_log_detail
WHERE log_record_key = {a53_key}
AND log_mdc_name NOT LIKE 'a53%cst%'
 
UNION
-- special, just for cst_key values
SELECT log_key, [FIELD], [New Value], [Old Value],
[User], [Date], [Time]
FROM 
dbo.av_fw_change_log_customer_key({a53_key},'a53%cst%')
ORDER BY [date] DESC

Sometimes you may want to log changes on a table/column that does not even have any change log child form. If this is the case, then you will need to make a new Child Form and display it on the appropriate page, as shown above.

Q. I would like to turn on the Change Log for all columns. Is this OK?

A. No, this would slow the system down significantly. You should log changes only on sensitive and important columns. If you log changes on every column, then ever time a user saves any record, the system will need to update the change log for every little change.

Q. How can I log changes on the Invoice Balance or Event Registrant Count? A. You can log changes only on true database columns. Values like the Invoice Balance or Event Registrant Count are calculated based on a database view or a quick SQL calculation. As such, it is not possible to log these sorts of changes in the change log.

Q. How can I tell the difference?

A. If you are just seeing this information on a page, it can be hard to know which values are "real" columns and which are calculated. Generally, if you can edit a record and actually type into the field then it is probably a real column. There is no column to type a balance -- this value is affected by making payments or adding items to an invoice -- so you can deduce that balance is a calculated column and not a true database column.

Q. When I add a record, why does the change log not populate?

A. It only populates for edits of existing records.

Q. How can I know which Columns are set to log changes?

A. Go to Admin>Column and do a Find or Query for any columns for which the "log changes" field is checked.

Q. If I go to a baseline column and configure it to log changes, this will lock the column and prevent it being from upgraded, right?

A. Yes, it will. If you do not lock it, then the upgrade tool will revert your change. Generally, column definitions do not change dramatically from one build to the next. As a safeguard, you might want to review any locked columns you have during upgrades to see if they were changed in baseline.

If you want to do detailed data analysis or processing on change log data, this can be difficult in practice. Change Log data is good for visually looking at in a user interface to scan changes over time; this is how it is used in NetForum. But the fw_change_log table can be difficult to work with if you are trying to write reports or more complicated data mining that tries to reconstruct history in a record, or figure out cumulative date ranges, etc. This is due in part because of the abstract nature of the table, in part because it can get so large with so many records, and in part because it is not suited for complicated data processing.

Although each scenario is different, you are generally cautioned that if you need to keep careful records of data changes over time, and that data is used in complex aggregations, that you might want to consider other ways to log historical data.

Address

• Address Change Log - the Address change log is different from the standard change log in that the Address Change Log shows the former address in its entirety. Since NetForum has the Address Change Log, we recommend you not log changes on single address fields since the Address Change Log handles this.

NetForum will insert a record in the log if any component of the Mailing Label Format changes, even if you change a prefix from "Ms." to "Dr." A more precise description of the address change log child form might be “address mailing label change log.”

If you want to override the logic for this feature, then you can override the protected virtual method InsertChangeLog method in the CO_Customer_X_Address facade class.

Membership Audit Trail

• Membership Audit Trail - from the Member profile page you will see the Membership Audit Trail child form which takes a snapshot of a Member record right before any changes. By scanning through these records (contained in mb_membership_audit_trail, you will see how the Member data changes over time.

Both of these special change logs do not show the "old" and "new" value. They just show the "old" value. The new value is the current value, or the next-most-recently changed value.