This View displays Contacts that have recently been edited along with a list of fields that have changed.

This View shows all the changes (edits) made to Contact records in the last 7 days. (This does not include changes made to related records, such as Participant and Household.)

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 Changes

Criteria (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 

Variations

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 highlighted number. Be sure to do so in all places fields and criteria which 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 Changes

Other 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 Changes

Techniques

This View uses the following SQL Functions and techniques:

• STUFF
• EXISTS
• GETDATE
• ISNULL
• CONVERT
• SELECT DISTINCT

Other Resources

• Audit Log Tables