CONTACT USBack to MinistryPlatform Site
Your Knowledge Base has moved to the new Help Center.  Check out the release notes for details. And don't forget to update your bookmarks and in-house documentation before May 28.

Email Addresses Updated Last 30 Days

 

This View shows Contacts where an email address has been updated in the last 30 days.

There is no lifeguard on duty. Swim at your own risk!
This View Example is offered as is and may need to be altered to fit your specific church culture. For assistance, contact your SPoC or, if you are a SPoC, contact Support for a Professional Services estimate (billed at an hourly rate).  


Difficulty: Advanced
Purpose: Creates list of Contacts with an email address that was edited in the past 30 days.
Page: Contacts

Field List

Contacts.[Display_Name] AS [Display Name]
, Contact_Status_ID_Table.[Contact_Status] AS [Contact Status]
, Participant_Record_Table_Participant_Type_ID_Table.[Participant_Type] AS [Participant Type]
, Contacts.[__Age] AS [Age]
, Contacts.[Email_Address] AS [Current Email Address]
, (SELECT TOP 1 Previous_Value from dp_Audit_Detail AD 
     JOIN dp_Audit_Log AL ON AD.Audit_Item_ID = AL.Audit_Item_ID 
       WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30 
       AND AD.Field_Name = 'Email_Address') AS [Previous Email]
, (SELECT TOP 1 New_Value from dp_Audit_Detail AD 
     JOIN dp_Audit_Log AL ON AD.Audit_Item_ID = AL.Audit_Item_ID 
       WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30 
       AND AD.Field_Name = 'Email_Address') AS [New Email]
, (SELECT TOP 1 User_Name FROM dp_Audit_Log AL 
     JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID  
       WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30 
       AND AD.Field_Name = 'Email_Address' ) AS [Changed By]
, (SELECT TOP 1 Date_Time FROM dp_Audit_Log AL 
     JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID 
       WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30 
       AND AD.Field_Name = 'Email_Address' ) AS [Date Changed]
, Contacts.[Bulk_Email_Opt_Out] AS [Bulk Email Opt Out]
, Contacts.[Email_Unlisted] AS [Email Unlisted]

View Clause

EXISTS (SELECT 1 FROM dp_Audit_Log AL 
  JOIN dp_Audit_Detail AD ON AL.Audit_Item_ID = AD.Audit_Item_ID 
    WHERE AL.Table_Name = 'Contacts' AND AL.Record_ID = Contacts.Contact_ID AND AL.Date_Time >= GETDATE() - 30 
    AND AD.Field_Name = 'Email_Address' )

Order By

[Date Changed] DESC

Techniques