Contact Changes
This View displays Contacts that have recently been edited along with a list of fields that have changed.
Difficulty: Advanced
Purpose: To track changes to Contacts.
Page: Contacts
This View shows all the changes (edits) made to Contact records in the last seven days. This does not include changes made to related records, such as Participant, Household, and so on.
Change List Field
This field shows all the changes made in a single field. The following SQL is added to the list of fields:
, STUFF((SELECT ', ' + ISNULL(AD.Field_Name,AL.Audit_Description) FROM dp_Audit_Log AL LEFT OUTER JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID WHERE AL.Record_ID = Contacts.Contact_ID AND AL.Table_Name = 'Contacts' AND AL.Date_Time >= GETDATE() - 7 FOR XML PATH('')), 1, 2, '') AS ChangesCriteria (View Clause)
This criteria limits the result to only those Contacts which have Audit Log changes.
EXISTS (SELECT 1 FROM dp_Audit_Log AL WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 7 )Other Details
Who?
The following field will show a list of User Names for changes made in the period. The DISTINCT keyword will only show each User Name once.
, STUFF((SELECT DISTINCT ', ' + AL.User_Name FROM dp_Audit_Log AL LEFT OUTER JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID WHERE AL.Record_ID = Contacts.Contact_ID AND AL.Table_Name = 'Contacts' AND AL.Date_Time >= GETDATE() - 7 FOR XML PATH('')), 1, 2, '') AS [Changes By]
When?
The following field will show the dates changes were made. The date may be formatted differently using the CONVERT function with a different date style. The DISTINCT keyword will only show each User Name once.
, STUFF((SELECT DISTINCT ', ' + CONVERT(varchar(20),AL.Date_Time,101) FROM dp_Audit_Log AL LEFT OUTER JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID WHERE AL.Record_ID = Contacts.Contact_ID AND AL.Table_Name = 'Contacts' AND AL.Date_Time >= GETDATE() - 7 FOR XML PATH('')), 1, 2, '') AS [Change Dates]What Values?
The following field will show the values that were changed. This should not be used for text-heavy fields.
, STUFF((SELECT DISTINCT ', ' + AD.Field_Name + ': From ' + AD.Previous_Value + ' to ' + AD.New_Value FROM dp_Audit_Log AL LEFT OUTER JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID WHERE AL.Record_ID = Contacts.Contact_ID AND AL.Table_Name = 'Contacts' AND AL.Date_Time >= GETDATE() - 7 FOR XML PATH('')), 1, 2, '') AS [Changed Values]Different Time Period
For a longer or shorter period, alter the number. Be sure to do so in all places, fields, and criteria that use the same logic.
, STUFF((SELECT ', ' + ISNULL(Field_Name,Audit_Description)
FROM dp_Audit_Log AL
LEFT OUTER JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID
WHERE AL.Record_ID = Contacts.Contact_ID AND AL.Table_Name = 'Contacts'
AND AL.Date_Time >= GETDATE() - 30
FOR XML PATH('')), 1, 2, '') AS ChangesOther Pages
For use in other Pages, replace references to Contacts and Contact_ID with your table and ID.
, STUFF((SELECT ', ' + ISNULL(AD.Field_Name,AL.Audit_Description) FROM dp_Audit_Log AL LEFT OUTER JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID WHERE AL.Record_ID = Contacts.Contact_ID AND AL.Table_Name = 'Contacts' AND AL.Date_Time >= GETDATE() - 7 FOR XML PATH('')), 1, 2, '') AS ChangesTechniques
This View uses the following SQL Functions and techniques:
- STUFF
- EXISTS
- GETDATE
- ISNULL
- CONVERT
- SELECT DISTINCT