home

MinistryPlatform

RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite
RealmRealm AccountingMinistryPlatformGivingPDSACSConnectShepherdGrowth MethodHeadMasterRefresh WebsitesTrust CenterGo MethodMissionInsite

What would you like to know more about?

Show Contents

Show Page Sections

thumbnailMinistryPlatform

Views

View Examples

Don't see the view you need in MinistryPlatform? Check here for examples to use or get you started building your own!

How Do I Add Views to My System?

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields as recommended in the view example you want to add (linked under this help page).

Basic Views

You can create the following views using the Advanced Search Tool with no additional SQL:

Intermediate Views

You can create the following views by adding or editing SQL in an otherwise straightforward view:

Advanced Views

You can create the following views using advanced SQL concepts:

Active Group Participants

This view can show all active participants in a group that you specify.

Difficulty: Basic - This view can be created in the Advanced Search Tool with no additional SQL. Or, if you prefer, you can use SQL criteria.

Assumptions: Records do not have future end dates. The name of the group is "4th Grade" in our example.

In the navigation menu, click People Lists > Group Participants. From the view drop-down menu, click New/Copy View. Create your view using the Group Name you want.

Example of 4th Grade View with fields for Display Name, Gender, Role Title, Group Name = 4th Grade, and End Date NULL

Or, create the view using SQL criteria on the SQL Layout tab.

Field List
Enter this SQL code to display the standard fields.
Participant_ID_Table_Contact_ID_Table.[Display_Name] AS [Display Name], 
Participant_ID_Table_Contact_ID_Table_Gender_ID_Table.[Gender] AS [Gender], 
Group_Role_ID_Table.[Role_Title] AS [Role Title], 
Group_ID_Table.[Group_Name] AS [Group Name], 
Group_ID_Table.[End_Date] AS [End Date]
Filter Clause
Enter this criteria to filter your list.
Group_ID_Table.[Group_Name] = '4th Grade'

Background Check Information

This view can show group participants and the information for their most recent background check, including submitted and returned dates.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • SELECT TOP 1
  • DATEDIFF
  • GETDATE
  • ISNULL

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Group Participants.
Field List
Enter this SQL code to create Check Submitted and Check Returned date fields.
Participant_ID_Table_Contact_ID_Table.[Display_Name] AS [Display Name] , Group_ID_Table.[Group_Name] AS [Group Name], Group_ID_Table_Parent_Group_Table.[Group_Name] AS [Parent Group], Group_Participants.[Start_Date] AS [Joined Team] ,
(	SELECT TOP 1 Background_Check_Started FROM Background_Checks BC
	JOIN Participants P ON BC.Contact_ID = P.Contact_ID
	WHERE P.Participant_ID = Group_Participants.Participant_ID
	ORDER BY Background_Check_Started DESC) AS Check_Started,
(	SELECT TOP 1 Background_Check_Submitted FROM Background_Checks BC
	JOIN Participants P ON BC.Contact_ID = P.Contact_ID
	WHERE P.Participant_ID = Group_Participants.Participant_ID
	ORDER BY Background_Check_Started DESC) AS Check_Submitted,
(	SELECT TOP 1 Background_Check_Returned FROM Background_Checks BC
	JOIN Participants P ON BC.Contact_ID = P.Contact_ID
	WHERE P.Participant_ID = Group_Participants.Participant_ID
	ORDER BY Background_Check_Started DESC) AS Check_Returned,
(	SELECT TOP 1 All_Clear FROM Background_Checks BC
	JOIN Participants P ON BC.Contact_ID = P.Contact_ID
	WHERE P.Participant_ID = Group_Participants.Participant_ID
	ORDER BY Background_Check_Started DESC) AS All_Clear, Group_ID_Table_Ministry_ID_Table.[Ministry_Name] AS [Ministry Name] , Group_ID_Table_Congregation_ID_Table.[Congregation_Name] AS [Congregation Name] , Group_Role_ID_Table.[Role_Title] AS [Role Title]
View Clause
Enter this criteria to ensure the view only returns current group participants.
GetDate() BETWEEN Group_Participants.Start_Date AND ISNULL(Group_Participants.End_Date,GetDate())

Changes to Contacts

This view can show Contact records that have recently been edited along with a list of fields that were changed. This is a great way to quickly track changes to contacts.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • STUFF
  • EXISTS
  • GETDATE
  • ISNULL
  • CONVERT
  • SELECT DISTINCT

Other Resources: See "Audit Log Tables" on Database Relationships.

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Contacts.
Field List
Enter this SQL code to show all the changes made in a single field.
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
Who made the change: Add the following to show a list of User names for the changes made. The DISTINCT keyword ensures each User name only displays 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 change was made: Add the following to show the dates that changes were made. The date may be formatted differently using the CONVERT function. The DISTINCT keyword ensures each User name only displays 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]
Which values were changed: Add the following to show the values that were changed. Do not use this 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 periods: To use a longer or shorter period to check for changes, change the number associated with the date. For example, enter AL.Date_Time >= GETDATE() - 30 to see the last 30 days instead of the last seven days. Be sure to do so in all places, fields, and criteria that use the same logic.

Other pages: To use this view in other pages, replace the references to Contacts and Contact_ID with the table and ID for the page you want. For example, enter WHERE AL.Record_ID = Participants.Participant_ID AND AL.Table_Name = 'Participants' to see Participant record changes.

View Clause
Enter this criteria to limit the results to only contacts who have Audit Log changes in the last seven days. Note that this does not include changes made to related records, such as Participant, Household, and so on.
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 )

Companies With Security Roles

This view can show any Company user records that have Platform access or security roles (which are not recommended) along with relevant information like last login date.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • EXISTS
  • COUNT
  • SELECT DISTINCT

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Users.
Field List
Enter this SQL code to show the following columns: User ID, Company Name, Total Role Count, Platform Role Count, Most Recent Login, and Total Logins.
dp_Users.[User_ID] AS [User ID], Contact_ID_Table.[Company_Name] AS [Company Name], 
(	SELECT COUNT(*) FROM dp_User_Roles UR
	WHERE UR.[User_ID] = dp_Users.[User_ID]) AS [Total Role Count], 
(	SELECT COUNT(*) FROM dp_User_Roles UR 
	WHERE UR.[User_ID] = dp_Users.[User_ID] 
	AND 
	(	EXISTS
		(	SELECT 1 FROM dp_Role_Pages RP 
			WHERE RP.[Role_ID] = UR.[Role_ID] AND RP.[Access_Level] IS NOT NULL) 
		OR EXISTS
		(	SELECT 1 FROM dp_Role_Sub_Pages RSP 
			WHERE RSP.[Role_ID] = UR.[Role_ID] AND RSP.[Access_Level] IS NOT NULL))) AS [Platform Role Count], 
(	SELECT TOP 1 AL.[Date_Time] FROM dp_Authentication_Log AL 
	WHERE AL.[User_ID] = dp_Users.[User_ID] 
	ORDER BY AL.[Date_Time] DESC) AS [Most Recent Login], 
(	SELECT COUNT(*) FROM dp_Authentication_Log AL 
	WHERE AL.[User_ID] = dp_Users.[User_ID]) AS [Total Logins]
View Clause
Enter this criteria to limit the results to only Company user records with security roles.
Contact_ID_Table.[Company] = 1 AND 
(	SELECT COUNT(*) FROM dp_User_Roles UR 
	WHERE UR.[User_ID] = dp_Users.[User_ID] 
	AND 
	(	EXISTS
		(	SELECT 1 FROM dp_Role_Pages RP 
			WHERE RP.[Role_ID] = UR.[Role_ID] 
			AND RP.[Access_Level] IS NOT NULL) 
		OR EXISTS 
	(	SELECT 1 FROM dp_Role_Sub_Pages RSP
		WHERE RSP.[Role_ID] = UR.[Role_ID] 
		AND RSP.[Access_Level] IS NOT NULL))) > 0

Contacts Created by a User

This view can show any contacts created by a user that you specify. This way, you can see which records were created on the same day by the same user.

Difficulty: Basic - This view can be created in the Advanced Search Tool with no additional SQL. Or, if you prefer, you can use SQL criteria.

Assumptions: The name of the user is "Webuser" in our example.

In the navigation menu, click Contacts. From the view drop-down menu, click New/Copy View. Create your view using the Created By user you want. (Make sure to enter the user's Display Name exactly as it appears on the Contact record.)

Example of Created by Same User View with fields Display Name, Nickname, Date Created BETWEEN 12/12 and 1/15, and Created By Webuser

Or, create the view using SQL criteria on the SQL Layout tab.

Field List
Enter this SQL code to display the standard fields.
Contacts.[Display_Name] AS [Display Name], 
Contacts.[Nickname] AS [Nickname], 
[dp_Created].[Date_Time] AS [Date Created], 
[dp_Created].[User_Name] AS [Created By]
Filter Clause
Enter this criteria to filter your list. Make sure to change the dates and enter the Created By user's Display Name exactly as it appears on the Contact record.
[dp_Created].[Date_Time] BETWEEN '2023-12-12 00:00:00' AND '2024-01-15 00:00:00' AND 
[dp_Created].[User_Name] = 'Webuser'

Contacts in Groups

This view can show contacts who are active participants in one or more groups that you specify.

Difficulty: Intermediate

SQL Functions and Techniques Used: The criteria uses EXISTS to check for matching records. The contact's Participant Record field is inserted to make this a correlated subquery. GETDATE and ISNULL are used to check that the current date is between the active date range for the group participant.

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Contacts.
Field List
Leave this blank to use the default fields.
View Clause
Enter this criteria to list all the contacts who are active participants in a particular group.
EXISTS 
(	SELECT 1 FROM Group_Participants GP 
	WHERE GP.Participant_ID = Contacts.Participant_Record 
	AND GP.Group_ID = 20 
	AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date, GETDATE()+1))

Multiple groups: The Group ID can be a single ID (like = 20 in our example) or a list of IDs, such as IN (20, 35, 102).

Remove a group instead: To find contacts who are not in the specified group(s), use NOT EXISTS in place of EXISTS.

Further limit the view: Use AND to combine with other criteria as needed.

Participants in groups: To change the view to work on the Participants page, change the WHERE statement to WHERE GP.Participant_ID = Participants.Participant_ID.

Contacts in a Households Selection

This view can show Contact records that are associated with any Household records in a selection of households.

Difficulty: Advanced

Assumption: The selection must be on the Contacts page, created by the logged in user, and be the Current/Unsaved selection.

SQL Functions and Techniques Used:
  • ISNULL
  • Subqueries
  • ID IN SELECT
  • A selection to control a view
Tip: Want to create this view quickly? From the Contacts page, open the Views/Advanced Search, and paste the code into the Filter Clause. Leave Field List empty. Then, to use this view, you would create a selection of households, go to the Contacts page, then select this view from the drop-down menu.

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields. To use this view, you would create a selection of households, go to the Contacts page, then select this view from the drop-down menu.

Page
Select Contacts.
View Clause
Enter this SQL code to list all the contacts in the Current/Unsaved (default) selection on the Households page.
Contacts.Household_ID IN 
(	SELECT Record_ID FROM dp_Selections S 
	JOIN dp_Selected_Records SR ON S.Selection_ID = SR.Selection_ID 
	WHERE S.User_ID = dp_UserID AND S.Selection_Name = 'dp_DEFAULT' AND S.Page_ID =           
		(	SELECT TOP 1 P.Page_ID FROM dp_Pages P 
			WHERE P.Table_Name = 'Households' AND P.Filter_Clause IS NULL))
Named selections:
  • To use something other than Current/Unsaved, like selections created with the Transfer Selection button two stacked arrows, one pointing right and one pointing left or Transfer Selection Tool, you can change dp_Default to the name of the selection. Make sure it is in single quotes. For example, Selection_Name = 'Name 1'.
  • To use a list of named selections (so that the view shows all records matching all the selections), use the IN comparison. For example, Selection_Name IN ('Name 1', 'Name 2', 'Name 3').

Selections from other users: To use a selection generated by another user, you can change dp_UserID to the ID of the use who will maintain the selection. For example, User_ID = 108.

Minor children in selected households:
  • Add this code to limit the list to only contacts that have a household position of Minor Child.
    AND Contacts.Household_Position_ID = 2
  • To use a list of minor household positions so the view shows all records matching all the selections, use the IN comparison.
    AND Contacts.Household_Position_ID IN (2,5)
Selection based on Page ID: The initial example looks up the Households page based on the page name. Alternatively, you can specify which page your selection is on. For example, you might do this if you have a Households Filtered page. Note that S.Page_ID=327 in the code refers to the page ID of the Households page in default MinistryPlatform instances. This may vary in your system, so be sure to confirm your Households page ID and modify the code as needed.
Contacts.Household_ID IN 
(	SELECT Record_ID FROM dp_Selections S 
	JOIN dp_Selected_Records SR ON S.Selection_ID = SR.Selection_ID 
	WHERE S.User_ID = dp_UserID AND S.Page_ID = 327 
	AND Sub_Page_ID IS NULL AND S.Selection_Name = 'dp_DEFAULT' )

Contacts Not Living in an Area

This view can show active contacts who are not living within certain ZIP Codes.

Difficulty: Basic - This view can be created in the Advanced Search Tool with no additional SQL. Or, if you prefer, you can use SQL criteria.

In the navigation menu, click Contacts. From the view drop-down menu, click New/Copy View. Create your view using the ZIP Code(s) you want.

Example of Contacts Not Living in Area View with fields Display Name, Contact Status = Active, Address Line 1, Address Line 2, City, State/Region, and Postal Code NOT IN 30041,30092,92870

Or, create the view using SQL criteria on the SQL Layout tab.

Field List
Enter this SQL code to display the standard fields.
Contacts.[Display_Name] AS [Display Name], 
Contact_Status_ID_Table.[Contact_Status] AS [Contact Status], 
Household_ID_Table_Address_ID_Table.[Address_Line_1] AS [Address Line 1], 
Household_ID_Table_Address_ID_Table.[Address_Line_2] AS [Address Line 2], 
Household_ID_Table_Address_ID_Table.[City] AS [City], 
Household_ID_Table_Address_ID_Table.[State/Region] AS [State/Region], 
Household_ID_Table_Address_ID_Table.[Postal_Code] AS [Postal Code]
Filter Clause
Enter this criteria to filter your list.
Contact_Status_ID_Table.[Contact_Status] = 'Active' AND 
Household_ID_Table_Address_ID_Table.[Postal_Code] NOT IN ('30041', '30092', '92870')

Contacts with Files Attached

This view can show Contact records that have files attached to them.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • EXISTS
  • STUFF
  • COUNT

Other Resources: See "Files Table" on Database Relationships.

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Contacts.
Field List
If you want to use the default fields, you can leave this blank. Or, you can add any of the following fields to show the file count, file names, and/or descriptions. The clause is page-specific for this type of view.
(	SELECT COUNT(*) FROM dp_Files AS F 
	WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID ) AS [File Count]

, STUFF(
(	SELECT ', ' + F.File_Name FROM dp_Files AS F 
	WHERE F.Table_Name =  'Contacts'  AND F.Record_ID =  Contacts.Contact_ID 
	FOR XML PATH('')), 1, 2, '') AS [File Names]

, STUFF(
(	SELECT ', ' + F.Summary FROM dp_Files AS F 
	WHERE F.Table_Name =  'Contacts'  AND F.Record_ID =  Contacts.Contact_ID 
	AND F.Summary IS NOT NULL AND F.Summary <> '' 
	FOR XML PATH('')), 1, 2, '') AS [Descriptions]

, (	SELECT TOP 1 UTC_Date_Added FROM dp_Files AS F 
	WHERE F.Table_Name =  'Contacts'  AND F.Record_ID =  Contacts.Contact_ID 
	ORDER BY UTC_Date_Added DESC) AS [Last File Added]
View Clause
Files attached: Enter this criteria to limit the list of contacts to those with files attached. Contacts without any files attached will not display in the view.
EXISTS 
(	SELECT 1 FROM dp_Files AS F 
	WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID )
Default image attached: To only show contacts with a default image, enter this clause.
EXISTS 
(	SELECT 1 FROM dp_Files AS F 
	WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID 
	AND F.Default_Image=1)
Other pages: To use the same view on pages other than Contacts, replace the Table Name and ID fields.
EXISTS 
(	SELECT 1 FROM dp_Files AS F 
	WHERE F.Table_Name='Participants' AND F.Record_ID = Participants.Participant_ID )
Multiple default files: To only show contacts with duplicate default files, replace the View Clause with this code.
(1 < 
(	SELECT COUNT(*) FROM dp_Files AS F 
	WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID 
	AND F.Default_Image=1))

Contacts with Milestones

This view can show all contacts who have been assigned one or more milestones that you specify.

Difficulty: Intermediate

SQL Functions and Techniques Used: The criteria utilizes EXISTS to check for matching records. The Contact's Participant_Milestone field is inserted to make this a correlated subquery. GETDATE can be used to check that the date accomplished is within a particular timeframe.

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Contacts.
Field List
Enter this SQL code to display the date a milestone was accomplished.
(	SELECT PS.Date_Accomplished FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Contacts.Participant_Record 
	AND PS.Milestone_ID = 3) AS [Baptism Date]
Enter this to display "Yes" or "No" depending on whether the contact has the milestone.
, CASE WHEN EXISTS 
(	SELECT 1 FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3) 
	THEN 'Yes' 
	ELSE 'No' 
END 
AS [Baptized]

Add any other fields you want to see.

View Clause
Enter this criteria, making sure to replace the ID associated with the milestone you want.
EXISTS 
(	SELECT 1 FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3 )
Without a milestone: To filter for anyone who does not have the specified milestone, add the NOT operator to the criteria.
NOT EXISTS 
(	SELECT 1 FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3 )
Any specified milestones: You can change the basic criteria to use a list of Milestone IDs. This shows contacts who have at least one of the specified milestones.
EXISTS 
(	SELECT 1 FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID IN (3, 4, 18) )
All specified milestones: To show contacts who have all of the specified milestones, you'll need to list them out separately.
EXISTS 
(	SELECT 1 FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3 )
AND EXISTS 
(	SELECT 1 FROM Participant_Milestones PS   
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 4 )
AND EXISTS 
(	SELECT 1 FROM Participant_Milestones PS   
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 18 )

Milestones accomplished by date: You can limit results to show those who accomplished a milestone within a certain date range or in the last specified number of days.

For example, this criteria would filter for Date Accomplished from January 1 to December 31. Enter your dates in single quotes, formatted as YYYY-MM-DD.
EXISTS 
(	SELECT 1 FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Contacts.Participant_Record AND PS.Milestone_ID = 3 
	AND PS.Date_Accomplished BETWEEN '2024-01-01' AND '2024-12-31' )
This criteria would filter by a date in the past 60 days using the GETDATE() function.
EXISTS 
(	SELECT 1 FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Contacts.Participant_Record 
	AND PS.Milestone_ID = 3 AND PS.Date_Accomplished > GETDATE()-60 )
Participants with milestones: You can change this view to work on the Participants page. To do so, the Participant_Milestones Participant_ID needs to be compared to the Participant_ID field rather than the Contact Participant_Record field. Use this criteria:
EXISTS 
(	SELECT 1 FROM Participant_Milestones PS 
	WHERE PS.Participant_ID = Participants.Participant_ID 
	AND PS.Milestone_ID = 3)

Email Address but No User

This view can show contacts who have an email address listed but don't have a user account.

Difficulty: Basic - This view can be created in the Advanced Search Tool with no additional SQL. Or, if you prefer, you can use SQL criteria.

In the navigation menu, click Contacts. From the view drop-down menu, click New/Copy View. Create your view using these fields and values.

Example of Email Address but No User View with fields User ID NULL, Email Address NOT NULL, and Company FALSE

Or, create the view using SQL criteria on the SQL Layout tab.

Field List
Leave this blank to use the default fields.
Filter Clause
Enter this criteria to filter your list.
User_Account_Table.[User_ID] IS NULL 
AND Contacts.[Email_Address] IS NOT NULL 
AND Contacts.[Company] = 0

Email Addresses Updated in the Last 30 Days

This view can show contacts whose email address has been edited in the last 30 days.

Attention: This view example is offered as is and may need to be altered to fit your specific needs. For assistance, contact your SPoC; if you are a SPoC, contact Support for a Professional Services estimate (billed at an hourly rate).

Difficulty: Advanced

SQL Functions and Techniques Used:
  • INNER JOIN
  • GETDATE
  • SELECT TOP 1

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Contacts.
Field List
Enter this SQL code to display the necessary fields.
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
Enter this criteria to limit the list.
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
Enter this code to change the order of the list.
[Date Changed] DESC

Event Participants with Amount Paid

This view can show the participants for events, including any participant notes and the amount paid to date.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • SUM
  • INNER JOIN

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Event Participants.
Field List
Enter this SQL code to display Participation Status, Participant Notes, and the amount paid compared to the full cost of the event.
Event_ID_Table.[Event_Start_Date] AS [Event Start Date], 
Participant_ID_Table_Contact_ID_Table.[Last_Name] AS [Last Name], 
Participant_ID_Table_Contact_ID_Table.[First_Name] AS [First Name], 
Event_ID_Table.[Event_Title] AS [Event Title], 
Participation_Status_ID_Table.[Participation_Status]  AS [Participation Status], 
Event_Participants.[Notes] AS [Participant Notes], 
(	SELECT SUM(Line_Total) FROM Invoice_Detail ID 
	WHERE ID.Event_Participant_ID = Event_Participants.Event_Participant_ID) AS [Event_Cost], 
	(	SELECT SUM(Payment_Amount) FROM Payment_Detail PD INNER JOIN Invoice_Detail ID 
		ON ID.Invoice_Detail_ID = PD.Invoice_Detail_ID 
		WHERE ID.Event_Participant_ID = Event_Participants.Event_Participant_ID) AS [Amount_Paid]
View Clause
Enter this criteria to limit the results to only event participants who have a value for Participation Status.
Participation_Status_ID_Table.[Participation_Status] IS NOT NULL

Event Rooms

This view can show which rooms are associated with each Event record.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • STUFF
  • EXISTS
  • ISNULL
  • GETDATE

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Events.
Field List
Enter this SQL code to create a field with all associated rooms, separated by commas.
Events.[Event_Start_Date] AS [Event Start Date] , 
Events.[Event_Title] AS [Event Title]
, STUFF(
(	SELECT ', ' + R.Room_Name FROM Rooms R 
	JOIN Event_Rooms ER ON R.Room_ID = ER.Room_ID 
	WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0 FOR XML PATH('')), 1, 2, '') AS [Event Rooms]
Unapproved room list: To show a list of rooms that are not approved, you can change the STUFF function in the initial SQL code.
, STUFF(
(	SELECT ', ' + R.Room_Name FROM Rooms R 
	JOIN Event_Rooms ER ON R.Room_ID = ER.Room_ID 
	WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0 
	AND ISNULL(ER._Approved,0)=0 FOR XML PATH('')), 1, 2, '') AS [Unapproved Rooms]
View Clause
Enter this criteria to limit the list to events for today or later.
Events.[Event_Start_Date] >= GETDATE()
Events with rooms: You can filter to only show events that have rooms associated with them.
AND EXISTS 
(	SELECT 1 FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0)
Events with unapproved rooms: You can filter to only show events with room reservations that haven't been approved yet.
AND EXISTS 
(	SELECT 1 FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID AND ER.Cancelled = 0 
	AND ISNULL(ER._Approved,0)=0 )
Approved events: You can filter to only show events that have been approved.
AND ISNULL(Events._Approved,0)=1

Events in Series

This view can show a list of events that are part of a series.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • SELECT TOP 1
  • SELECT IN

Other Resources: See "Series Tables" on Database Relationships.

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Events.
Field List
Enter this SQL code if you want to add the Series ID as a field in the view. Add any other fields you want to see.
(	SELECT SR.Sequence_ID FROM dp_Sequence_Records SR 
	WHERE SR.Table_Name = 'Events' AND SR.Record_ID = Events.Event_ID) AS [Series ID]
View Clause
Events in any series: You can show events that are part of any series.
Events.Event_ID IN 
(	SELECT SR.Record_ID FROM dp_Sequence_Records SR WHERE SR.Table_Name = 'Events')
Tip: You could combine this with additional criteria to narrow down the list to current or future events.
Event in a specific series: Enter this criteria to limit the list to an event in a specific series. Make sure you replace the 0 in our example with the Event ID of one of the events in the series you want to view.
Events.Event_ID IN 
(	SELECT SR1.Record_ID 
	FROM dp_Sequence_Records SR1 WHERE Sequence_ID IN 
	(	SELECT SR2.Sequence_ID FROM dp_Sequence_Records SR2 
		WHERE SR2.Table_Name = 'Events' AND SR2.Record_ID = 0 ))
Multiple events in a specific series: This variation uses the default selection to determine which series to show. To use this view, you would select an event (leave the selection unsaved so it is the default) then select the view from the drop-down menu. You'll see a list of events in the series that matches your selection.
Events.Event_ID IN 
(	SELECT SR1.Record_ID  FROM dp_Sequence_Records SR1 WHERE Sequence_ID IN 
	(	SELECT SR2.Sequence_ID FROM dp_Sequence_Records SR2 
		WHERE SR2.Table_Name = 'Events' AND SR2.Record_ID IN 
		(	SELECT SR.Record_ID FROM dp_Selections S 
			JOIN dp_Selected_Records SR ON S.Selection_ID = SR.Selection_ID 
			WHERE User_ID = dp_UserID AND Selection_Name = 'dp_Default' 
			AND Page_ID = 
			(	SELECT TOP 1 P.Page_ID FROM dp_Pages P 
				WHERE P.Display_Name = 'Events' AND P.Filter_Clause IS NULL) ) ))
Note: You could simplify this by hard-coding the Page ID for your Events page. To do this, replace the last SELECT statement and parentheses with the ID.

Events that Need Approval

This view can show events with rooms, services, or equipment that still need to be approved. You can also see the count of rooms, services, or equipment that need approval.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • COUNT
  • EXISTS

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Events.
Field List
Enter this SQL code to display the standard fields. Together with the calculated fields, these can create a well-rounded view of events and where they are in the approval process.
Events.[Event_Start_Date] AS [Event Start Date] , Events.[Event_Title] AS [Event Title] , Event_Type_ID_Table.[Event_Type] AS [Event Type] , Congregation_ID_Table.[Congregation_Name] AS [Campus] , Primary_Contact_Table.[Display_Name] AS [Contact] , Program_ID_Table_Ministry_ID_Table.[Ministry_Name] AS [Ministry Name] , Location_ID_Table.[Location_Name] AS [Location Name] , Program_ID_Table.[Program_Name] AS [Program Name] ,Events.[_Approved] AS [Approved]
Counts of unapproved items: Add this code to display fields for the counts of rooms, services, and equipment that are not approved.
, (	SELECT COUNT(ER.Event_Room_ID) FROM Event_Rooms ER 
	WHERE ER.Event_ID = Events.Event_ID AND ISNULL(ER._Approved,0)=0 ) AS [Rooms Not Approved] , 
(	SELECT COUNT(ES.Event_Service_ID) FROM Event_Services ES 
	WHERE ES.Event_ID = Events.Event_ID AND ISNULL(ES._Approved,0)=0 ) AS [Services Not Approved] , 
(	SELECT COUNT(EE.Event_Equipment_ID) FROM Event_Equipment EE 
	WHERE EE.Event_ID = Events.Event_ID AND ISNULL(EE._Approved,0)=0 ) AS [Equipment Not Approved]
Show True or False for approvals needed: Add this code to display fields that show "True" or "False" based on whether room, service, or equipment approval is needed.
, (	SELECT CASE 
		WHEN 0 = COUNT(ER.Event_Room_ID) THEN 'False' 
		ELSE 'True' END 
	FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID 
	AND ISNULL(ER._Approved,0)=0 ) AS [Room Approval Needed] , 
(	SELECT CASE 
		WHEN 0 = COUNT(ES.Event_Service_ID) THEN 'False' 
		ELSE 'True' END 
	FROM Event_Services ES WHERE ES.Event_ID = Events.Event_ID 
	AND ISNULL(ES._Approved,0)=0 ) AS [Service Approval Needed] , 
(	SELECT CASE 
		WHEN 0 = COUNT(EE.Event_Equipment_ID) THEN 'False' 
		ELSE 'True' END 
	FROM Event_Equipment EE WHERE EE.Event_ID = Events.Event_ID 
	AND ISNULL(EE._Approved,0)=0 ) AS [Equipment Approval Needed]
Show True or False for any approvals needed: The following code combines all approvals together. It displays "True" if approvals are needed or "False" if approvals are not needed.
, (	SELECT CASE 
		WHEN 0 = (COUNT(ER.Event_Room_ID) + COUNT(ES.Event_Service_ID) + COUNT(EE.Event_Equipment_ID)) THEN 'False' 
		ELSE 'True' END 
	FROM Events E LEFT JOIN Event_Rooms ER ON E.Event_ID = ER.Event_ID 
	LEFT JOIN Event_Services ES ON E.Event_ID = ES.Event_ID 
	LEFT JOIN Event_Equipment EE ON E.Event_ID = EE.Event_ID 
	WHERE E.Event_ID = Events.Event_ID 
	AND (	ISNULL(ER._Approved,0)=0 
			OR ISNULL(ES._Approved,0)=0 
			OR ISNULL(EE._Approved,0)=0) ) AS [Approvals Needed]
View Clause
Enter this criteria to filter out the events that do not need approval.
( Events.[Event_Start_Date] >= GetDate() 
AND ISNULL(Events.[Cancelled], 0) = 0 ) 
AND ( ISNULL(Events.[_Approved], 0) = 0 OR EXISTS 
	(	SELECT 1 FROM Event_Rooms ER WHERE ER.Event_ID = Events.Event_ID 
		AND ISNULL(ER._Approved,0)=0) 
	OR EXISTS 
	(	SELECT 1 FROM Event_Services ES WHERE ES.Event_ID = Events.Event_ID 
		AND ISNULL(ES._Approved,0)=0) 
	OR EXISTS 
	(	SELECT 1 FROM Event_Equipment EE WHERE EE.Event_ID = Events.Event_ID 
		AND ISNULL(EE._Approved,0)=0) )

Expiring Option Prices

This view can show product option prices for upcoming events that have an expiration. Using this view, you can track expiring product option prices.

Difficulty: Advanced

Assumptions: Events have a future start date. Product Option Prices have a value set for Days Out To Hide.

SQL Functions and Techniques Used:
  • DATEADD
  • DATEDIFF
  • EXISTS
  • GETDATE

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Product Option Prices.
Field List
Enter this SQL code to display the standard fields.
Product_Option_Group_ID_Table_Product_ID_Table.[Product_Name], Product_Option_Prices.[Option_Title], Product_Option_Prices.[Days_Out_To_Hide] AS [Days Out To Hide], 
(	SELECT TOP 1 Ev.Event_Title FROM Events Ev 
	WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID 
	AND Ev.Event_Start_Date > GETDATE() 
	ORDER BY Ev.Event_Start_Date) AS [Event], 
(	SELECT TOP 1 Ev.Event_Start_Date FROM Events Ev 
	WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID 
	AND Ev.Event_Start_Date > GETDATE() 
	ORDER BY Ev.Event_Start_Date) AS [Event Start Time], 
(	SELECT TOP 1 DATEADD(dd,-Product_Option_Prices.[Days_Out_To_Hide],CONVERT(date,Ev.Event_Start_Date)) FROM Events Ev 
	WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID 
	AND Ev.Event_Start_Date > GETDATE() 
	ORDER BY Ev.Event_Start_Date) AS [Hide Date], 
(	SELECT TOP 1 DATEDIFF(dd,GETDATE(),Ev.Event_Start_Date) FROM Events Ev 
	WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID 
	AND Ev.Event_Start_Date > GETDATE() 
	ORDER BY Ev.Event_Start_Date) AS [Days Till Event]
View Clause
Enter this criteria to limit the results to only show active product option prices that have an expiration and are only associated with future events.
Product_Option_Prices.[Days_Out_To_Hide] IS NOT NULL
AND Product_Option_Prices.Active = 1
AND EXISTS 
(	SELECT TOP 1 1 FROM Events Ev 
	WHERE Ev.Online_Registration_Product = Product_Option_Group_ID_Table.Product_ID 
	AND Ev.Event_Start_Date > GETDATE())

Fix: Same Email but No Relationship

This view can show contacts who have the same email address but do not have a contact relationship with each other.

Note: The Data Quality Team uses this view to help identify possible duplicate records that are not found by the Duplicate Finder Routine.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • EXISTS

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Contacts.
Field List
Leave this blank to use the default fields. Alternatively, you can add any of the following SQL code to show helpful details for identifying duplicates.
Contacts.Last_Name ,Contacts.Nickname ,Contacts.First_Name ,Contacts.Display_Name ,Contacts.Email_Address ,Contact_Status_ID_Table.Contact_Status ,Household_ID_Table.Home_Phone ,Contacts.Mobile_Phone ,Household_ID_Table_Address_ID_Table.Address_Line_1 ,Household_ID_Table_Address_ID_Table.City ,Household_ID_Table_Address_ID_Table.[State/Region] AS State ,Household_ID_Table_Address_ID_Table.Postal_Code ,Convert(Varchar(12),Contacts.Date_of_Birth,101) AS Date_of_Birth ,Gender_ID_Table.Gender ,Marital_Status_ID_Table.Marital_Status ,Household_ID_Table_Congregation_ID_Table.Congregation_Name ,Household_ID_Table.Household_Name ,Household_Position_ID_Table.Household_Position ,[dp_Created].[User_Name] AS [Created By] ,[dp_Updated].[User_Name] AS [Updated By]
View Clause
Enter this criteria to limit the list to show contacts with the same email address but no contact relationship, such as "Married To" or "Child Of".
Contacts.Email_Address IS NOT NULL AND EXISTS 
(	SELECT 1 FROM Contacts C WHERE C.Email_Address IS NOT NULL 
	AND C.Company = Contacts.Company AND C.Email_Address = Contacts.Email_Address 
	AND C.Contact_ID <> Contacts.Contact_ID 
	AND ISNULL(C.Household_ID,0) <> ISNULL(Contacts.Household_ID,0) 
	AND NOT EXISTS 
	(	SELECT 1 FROM Contact_Relationships CR 
		WHERE CR.Contact_ID = Contacts.Contact_ID AND CR.Related_Contact_ID = C.Contact_ID))

Group Attendance - Most Recent

This view can show the most recent time someone in a group was marked as Attended or Confirmed. Using this view, you can see which groups have taken attendance recently.

Difficulty: Intermediate

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Groups.
Field List
Enter this SQL code to create a field that shows the most recent time someone attended the group. Add any other fields you want to see.
Groups.[Group_Name] AS [Group Name]
, (	SELECT MAX(E.Event_Start_Date) FROM Event_Participants EP 
INNER JOIN Events E ON E.Event_ID = EP.Event_ID 
INNER JOIN Group_Participants GP ON EP.Group_Participant_ID = GP.Group_Participant_ID 
WHERE GP.Group_ID = Groups.Group_ID AND EP.Participation_Status_ID IN (3,4)) AS Last_Attended
View Clause
There is no set filter for this view, so enter any criteria you want to refine your view.

Group Counts

This view can provide a list of group target sizes, member counts, and available spaces. Use this view on the Groups page or as a filtered page.

Difficulty: Advanced

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Groups.
Field List
Enter this SQL code to display the standard fields.
Groups.[Group_Name] AS [Group Name]
, Congregation_ID_Table.[Congregation_Name] AS [Group Congregation]
, Parent_Group_Table.[Group_Name] AS [Neighborhood]
, Life_Stage_ID_Table.[Life_Stage] AS [Life Stage]
, Meeting_Day_ID_Table.[Meeting_Day] AS [Meeting Day]
, Groups.[Meeting_Time] AS [Meeting Time], Groups.[Target_Size] AS [Target Size]
, (SELECT COUNT(*) FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID AND GETDATE()  
BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,GETDATE())) AS Current_Participants
,ISNULL(Groups.Target_Size,0)-(SELECT COUNT(*) FROM Group_Participants GP  
WHERE GP.Group_ID = Groups.Group_ID AND GETDATE()  
BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,GETDATE())) AS Space_Available
, Groups.[Group_Is_Full] AS [Group_is_Full]
Count of current members: Add this code to include a count of the current members in the group.
, (	SELECT COUNT(*) FROM Group_Participants GP 
	WHERE GP.Group_ID = Groups.Group_ID AND GETDATE() 
	BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,GETDATE())) AS Current_Participants
Count of open spaces: Add this code to include a count of open spaces available for the group.
, ISNULL(Groups.Target_Size,0)-(SELECT COUNT(*) FROM Group_Participants GP 
WHERE GP.Group_ID = Groups.Group_ID AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,GETDATE())) AS Space_Available
View Clause
Enter this criteria to limit the results.
(Groups.End_Date IS NULL OR Groups.End_Date >= GETDATE()) AND Groups.Group_Type_ID = 1

Group Members of the Current User

This view can show all current group members for the groups that the user is a member of.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • GETDATE
  • ISNULL
  • INNER JOIN

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Group Participants.
Field List
Enter this SQL code to display the standard fields.
Group_ID_Table.[Group_Name] AS [Group Name], Participant_ID_Table_Contact_ID_Table.[Display_Name] AS [Display Name], Group_Participants.[Start_Date] AS [Start Date], Group_Participants.[End_Date] AS [End Date], Participant_ID_Table.[Participant_ID] AS [Participant ID]
View Clause
Enter this criteria to limit the list.
GETDATE() <= ISNULL(Group_Participants.[End_Date], GETDATE()) 
AND Group_Participants.[Group_ID] IN 
(	SELECT gp.Group_ID FROM Group_Participants GP 
	INNER JOIN Participants P ON GP.Participant_ID = P.Participant_ID 
	INNER JOIN Contacts C ON P.Contact_ID = C.Contact_ID 
	WHERE C.user_account = dp_UserID AND GETDATE() <= ISNULL(GP.End_Date, GETDATE()))

Group Participant Attributes

This view can show all participants in a specific ministry or group along with the notes about allergies and special needs.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • SELECT TOP

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Group Participants.
Field List
Enter this SQL code to add a field that shows whether a group participant has a specific attribute. Replace the value with the appropriate Allergy and Special Need ID.
Participant_ID_Table_Contact_ID_Table.[Nickname] AS [Nickname] 
, Participant_ID_Table_Contact_ID_Table.[Last_Name] AS [Last Name] 
, Group_ID_Table.[Group_Name] AS [Group Name] 
, Group_Role_ID_Table.[Role_Title] AS [Role Title] 
, Group_ID_Table_Ministry_ID_Table.[Ministry_Name] AS [Ministry Name] 
, Group_ID_Table_Congregation_ID_Table.[Congregation_Name] AS [Congregation Name] 
,(SELECT TOP 1 CA.Notes FROM Contact_Attributes CA JOIN Attributes A ON CA.Attribute_ID = A.Attribute_ID AND Attribute_Type_ID = 1 WHERE CA.Contact_ID = Participant_ID_Table.Contact_ID) AS [Allergy & Special Need]
View Clause
Specific ministry: Enter this criteria to limit the list to a specific ministry. Replace the value with the appropriate Ministry ID.
Group_ID_Table_Ministry_ID_Table.[Ministry_ID] = 2 
AND GetDate() BETWEEN Group_Participants.Start_Date 
AND ISNULL(Group_Participants.End_Date,GetDate())
Specific group: Enter this criteria to limit the list to a specific group. Replace the value with the appropriate Group ID.
Group_ID_Table.[Group_ID] = 1 
AND GetDate() BETWEEN Group_Participants.Start_Date AND ISNULL(Group_Participants.End_Date,GetDate())

Group Participant Form Submissions

This view can show the participants in a specific group and the date (if applicable) that they filled out a specific form.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • SELECT MAX

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Group Participants.
Field List
Enter this SQL code to add a field that shows whether a group participant has filled out a specific form. Replace the value with the appropriate Form ID.
Participant_ID_Table_Contact_ID_Table.[Display_Name] AS [Display Name] 
, Group_ID_Table.[Group_Name] AS [Group Name] 
, (SELECT MAX(FR.Response_Date) FROM Form_Responses FR JOIN Contacts C 
ON C.Contact_ID = FR.Contact_ID WHERE FR.Form_ID = 1 
AND C.Participant_Record = Group_Participants.Participant_ID) AS [Church Agreement]
View Clause
Enter this criteria to limit the list to a specific group. Replace the value with the appropriate Group ID.
Group_ID_Table.[Group_ID] = 292
Participants with a specific role type: To limit the list to those who have a certain role type, add this code and replace the value with the appropriate Role Type ID.
Group_Role_ID_Table_Group_Role_Type_ID_Table.[Group_Role_Type_ID] = 1

Group Participants in a Selection

This view can show the group participants that match a selection of participants.

Difficulty: Advanced

Assumptions: The selection must be on the Participants page, created by the logged in user, and be the Current/Unsaved selection.

SQL Functions and Techniques Used:
  • GETDATE
  • ISNULL
  • Subqueries
  • ID IN SELECT
  • A selection to control a view
Tip: Want to create this view quickly? From the Participants page, open Advanced Search, and paste the code into the Filter Clause on the SQL Layout tab. Leave Field List empty. Then, to use this view, you would create a selection of participants, navigate to the Group Participants page, then select this view from the drop-down menu.

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields. To use this view, you would create a selection of participants, navigate to the Group Participants page, then select this view from the drop-down menu.

Page
Select Group Participants.
Field List
Leave this blank to use the default fields.
View Clause
Enter this SQL code to list all the people in the Current/Unsaved (default) selection on the Group Participants page.
Group_Participants.Participant_ID IN 
(	SELECT SR.Record_ID FROM dp_Selections S 
	JOIN dp_Selected_Records SR ON S.Selection_ID = SR.Selection_ID 
	WHERE User_ID = dp_UserID AND Selection_Name = 'dp_Default' 
	AND Page_ID = 
	(	SELECT TOP 1 P.Page_ID FROM dp_Pages P 
		WHERE P.Table_Name = 'Participants' AND P.Filter_Clause IS NULL))
Named selections:
  • To use something other than Current/Unsaved, like selections created with the Transfer Selection button two stacked arrows, one pointing right and one pointing left or Transfer Selection Tool, you can change dp_Default to the name of the selection. Make sure it is in single quotes. For example, Selection_Name = 'Name 1'.
  • To use a list of named selections (so that the view shows all records matching all the selections), use the IN comparison. For example, Selection_Name IN ('Name 1', 'Name 2', 'Name 3').

Selections from other users: To use a selection generated by another user, you can change dp_UserID to the ID of the use who will maintain the selection. For example, User_ID = 108.

Active groups and group participants: You can use GETDATE to limit the list to a current group and current participant.
Group_Participants.Participant_ID IN 
(	SELECT SR.Record_ID FROM dp_Selections S 
	JOIN dp_Selected_Records SR ON S.Selection_ID = SR.Selection_ID 
	WHERE User_ID = dp_UserID AND Selection_Name = 'dp_Default' 
	AND Page_ID = 
	(	SELECT TOP 1 P.Page_ID FROM dp_Pages P 
		WHERE P.Table_Name = 'Participants' AND P.Filter_Clause IS NULL)) 
AND Group_Participants.Group_ID IN 
(	SELECT G.Group_ID FROM Groups G WHERE GETDATE() BETWEEN G.Start_Date AND ISNULL(G.End_Date,GETDATE()+1)) 
AND GETDATE() BETWEEN Group_Participants.Start_Date AND ISNULL(Group_Participants.End_Date,GETDATE()+1)

Groups Over Capacity

This view can show groups that are at or over their target size.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • COUNT
  • GETDATE

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Groups.
Field List
Enter this SQL code to display the standard fields.
[Group_Name] AS [Group Name] , [Target_Size] AS [Target Size] , 
(	SELECT Count(*) FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID 
	AND GetDate() BETWEEN GP.Start_Date 
	AND ISNULL(GP.End_Date,Getdate())) AS Current_Participants , ISNULL(Groups.Target_Size,0)-
(	SELECT Count(*) 
	FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID 
	AND GetDate() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,Getdate())) AS Space_Available 
, Group_Type_ID_Table.[Group_Type] AS [Group Type] 
, Groups.[End_Date] AS [End Date], [Group_is_Full] AS [Group Full]
View Clause
Enter this criteria to limit the list to groups where the number of active participants matches or exceeds the group's Target Size.
Groups.End_Date IS NULL AND ISNULL(Groups.Target_Size,0)-
(	SELECT Count(*) FROM Group_Participants GP 
	WHERE GP.Group_ID = Groups.Group_ID AND GetDate() BETWEEN GP.Start_Date 
	AND ISNULL(GP.End_Date,Getdate())) <= 0
Specific group type: To only show groups of a specific type, add this criteria and substitute the appropriate Group Type ID.
Groups.Group_Type_ID = 1

Groups Under Capacity

This view can show groups that are under their target size.

Difficulty: Intermediate

SQL Functions and Techniques Used:
  • COUNT
  • GETDATE

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Groups.
Field List
Enter this SQL code to display the standard fields.
[Group_Name] AS [Group Name] , [Target_Size] AS [Target Size] , 
(	SELECT Count(*) FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID 
	AND GetDate() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,Getdate())) AS Current_Participants , ISNULL(Groups.Target_Size,0)-
(	SELECT Count(*) FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID 
	AND GetDate() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,Getdate())) AS Space_Available 
, Group_Type_ID_Table.[Group_Type] AS [Group Type] , Groups.End_Date AS [End Date] , [Group_is_Full] AS [Group Full]
View Clause
Enter this criteria to limit the list to groups where the number of active participants is less than the group's Target Size.
Groups.End_Date IS NULL AND ISNULL(Groups.Target_Size,0)-
(	SELECT Count(*) FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_ID 
	AND GetDate() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,Getdate())) >= 1
Specific group type: To only show groups of a specific type, add this criteria and substitute the appropriate Group Type ID.
Groups.Group_Type_ID = 1

Heads of Households with Minor Children

This view can show active heads of household who have minor children in their household.

Attention: This view example is offered as is and may need to be altered to fit your specific needs. For assistance, contact your SPoC; if you are a SPoC, contact Support for a Professional Services estimate (billed at an hourly rate).

Difficulty: Intermediate

SQL Functions and Techniques Used:
  • EXISTS

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Contacts.
Field List
Enter this SQL code to display the standard fields.
Household_ID_Table.[Household_Name] AS [Household Name], Contacts.[Display_Name] AS [Display Name], Contacts.[First_Name] AS [First Name], Contact_Status_ID_Table.[Contact_Status] AS [Contact Status] , Household_ID_Table_Congregation_ID_Table.[Congregation_Name] AS [Congregation Name] , Household_Position_ID_Table.[Household_Position] AS [Household Position] , Participant_Record_Table_Participant_Type_ID_Table.[Participant_Type] AS [Participant Type]
View Clause
Enter this criteria to limit the list to heads of household with minor children.
Contact_Status_ID_Table.[Contact_Status] = 'Active' AND Contacts.Household_Position_ID = 1 
AND EXISTS 
(	SELECT 1 FROM contacts c2 WHERE c2.household_id = Contacts.Household_ID 
	AND c2.Household_Position_ID = 2 AND c2.__Age < 18)

Household Donations

This view can show both heads of household and their household giving for this year and last year.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • GETDATE
  • ISNULL
  • EXISTS
  • SELECT TOP 1
  • SUM

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Households.
Field List
Enter this SQL code to create the appropriate fields.
Heads of Household fields: Displays the household name followed by two fields for the heads. You can add any additional fields as needed.
Households.[Household_Name] AS [Household Name],
(	SELECT TOP 1 First_Name FROM Contacts C 
	WHERE C.Household_ID = Households.Household_ID AND Household_Position_ID = 1 
	ORDER BY Gender_ID) AS Head_1_Name,
(	SELECT TOP 1 First_Name FROM Contacts C 
	WHERE C.Household_ID = Households.Household_ID AND Household_Position_ID = 1 
	AND EXISTS 
	(	SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1 
		AND C2.Household_ID = C.Household_ID 
		GROUP BY Household_ID HAVING Count(*) > 1) 
	GROUP BY Contact_ID, First_Name, Gender_ID 
	ORDER BY Gender_ID DESC) AS Head_2_Name
Giving totals: Displays fields for prior year giving, current year giving, and the difference between those amounts.
, (	SELECT SUM(Do.Donation_Amount) FROM Contacts C 
	JOIN Donations Do ON Do.Donor_ID = C.Donor_Record 
	WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
	AND YEAR(Do.Donation_Date) = YEAR(GETDATE())-1) AS [Prior Year Giving] ,
(	SELECT SUM(Do.Donation_Amount) FROM Contacts C 
	JOIN Donations Do ON Do.Donor_ID = C.Donor_Record 
	WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
	AND YEAR(Do.Donation_Date) = YEAR(GETDATE())) AS [Current Year Giving] , (
(	SELECT SUM(Do.Donation_Amount) FROM Contacts C 
	JOIN Donations Do ON Do.Donor_ID = C.Donor_Record 
	WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
	AND YEAR(Do.Donation_Date) = YEAR(GETDATE())-1) - 
(	SELECT SUM(Do.Donation_Amount) FROM Contacts C 
	JOIN Donations Do ON Do.Donor_ID = C.Donor_Record 
	WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
	AND YEAR(Do.Donation_Date) = YEAR(GETDATE()))) AS [Giving Difference]
View Clause
Enter this criteria to limit the list to households that have given in the current or previous year.
EXISTS
(	SELECT 1 FROM Contacts C JOIN Donations Do ON Do.Donor_ID = C.Donor_Record 
	WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 
	AND YEAR(Do.Donation_Date) >= YEAR(GETDATE())-1)

New Donors in the Last 7 Days

This view can show donors who have given for the first time in the last seven days.

Note: For an advanced version of this view using the previous calendar week, see New Donors in the Last Week.

Difficulty: Intermediate

SQL Functions and Techniques Used:
  • GETDATE
  • EXISTS
  • STUFF

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Donors.
Field List
Leave this blank to use the default fields. Alternatively, you can add any of the following SQL code to show helpful details.
Total donations: Add this SQL code to create a Total Donations field. Be sure to use a comma to separate this from any other fields.
(	SELECT SUM(Do.Donation_Amount) FROM Donations Do 
	WHERE Do.Donor_ID = Donors.Donor_ID) AS [Total Donations]
Distribution list: Add this SQL code to show a list of programs that the donations have been distributed to. Be sure to use a comma to separate this from any other fields. White space is optional.
, STUFF(
(	SELECT ', ' + P.Program_Name FROM Donation_Distributions DD 
	JOIN Programs P ON DD.Program_ID = P.Program_ID 
	JOIN Donations D ON DD.Donation_ID = D.Donation_ID 
	WHERE D.Donor_ID = Donors.Donor_ID FOR XML PATH('')), 1, 2, '') AS [Distributions]
View Clause
Enter this criteria to limit the list to first-time donations in the previous seven days.
_First_Donation_Date > GETDATE()-7
Filter by giving amount: To only show first-time donors who have given a minimum amount or higher, use AND to combine the following with existing criteria. You can change the amount to suit your needs.
(	SELECT SUM(Do.Donation_Amount) FROM Donations Do WHERE Do.Donor_ID = Donors.Donor_ID) > 5
Filter by household giving: To only show first-time donors for a household, change the View Clause to the following SQL code. White space is optional.
EXISTS
(	SELECT 1 FROM Donors D JOIN Contacts C ON D.Contact_ID = C.Contact_ID 
	WHERE D.Donor_ID = Donors.Donor_ID AND C.Household_ID IN 
	(	SELECT C2.Household_ID FROM Donors D2 
		JOIN Contacts C2 ON D2.Contact_ID = C2.Contact_ID 
		GROUP BY C2.Household_ID HAVING MIN(D2._First_Donation_Date) > GETDATE()-7 ))
AND Donors._First_Donation_Date IS NOT NULL

New Donors in the Last Week

This view can show donors who have given for the first time in the last calendar week.

Note: For an intermediate version of this view using the previous seven days, see New Donors in the Last 7 Days.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • GETDATE
  • DATEPART
  • EXISTS
  • STUFF
  • MIN

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Donors.
Field List
Leave this blank to use the default fields. Alternatively, you can add any of the following SQL code to show helpful details.
Total donations: Add this SQL code to create a Total Donations field. Be sure to use a comma to separate this from any other fields.
(	SELECT SUM(Do.Donation_Amount) FROM Donations Do 
	WHERE Do.Donor_ID = Donors.Donor_ID) AS [Total Donations]
Distribution list: Add this SQL code to show a list of programs that the donations have been distributed to. Be sure to use a comma to separate this from any other fields. White space is optional.
, STUFF(
(	SELECT ', ' + P.Program_Name  FROM Donation_Distributions DD 
	JOIN Programs P ON DD.Program_ID = P.Program_ID 
	JOIN Donations D ON DD.Donation_ID = D.Donation_ID 
	WHERE D.Donor_ID = Donors.Donor_ID  FOR XML PATH('')), 1, 2, '') AS [Distributions]
View Clause
Enter this criteria to limit the list to first-time donations in the previous calendar week.
DATEPART(wk, _First_Donation_Date) = DATEPART(wk, GETDATE()-7) AND _First_Donation_Date > GETDATE()-14
Filter by giving amount: To only show first-time donors who have given a minimum amount or higher, use AND to combine the following with existing criteria. You can change the amount to suit your needs.
(	SELECT SUM(Do.Donation_Amount) FROM Donations Do WHERE Do.Donor_ID = Donors.Donor_ID) > 5
Filter by household giving: To only show first-time donors for a household, change the View Clause to the following SQL code. White space is optional.
EXISTS
(	SELECT 1 FROM Donors D JOIN Contacts C ON D.Contact_ID = C.Contact_ID 
	WHERE D.Donor_ID = Donors.Donor_ID AND C.Household_ID IN 
	(	SELECT C2.Household_ID FROM Donors D2 
		JOIN Contacts C2 ON D2.Contact_ID = C2.Contact_ID 
		GROUP BY C2.Household_ID 
		HAVING DATEPART(wk, MIN(D2._First_Donation_Date)) = DATEPART(wk, GETDATE()-7) 
		AND MIN(D2._First_Donation_Date) > GETDATE()-14 )) 
AND Donors._First_Donation_Date IS NOT NULL

Participant Heads

This view can show both heads of household and their contact information associated with the participant.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • STUFF
  • ISNULL

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Participants.
Field List
Enter this SQL code to display names of all associated heads of household, a field with their phone numbers, and a field with their email addresses.
STUFF(
(	SELECT ', '+ C.First_Name + ' ' + C.Last_Name FROM Contacts C 
	WHERE C.Household_ID = Contact_ID_Table_Household_ID_Table.Household_ID 
	AND C.Household_Position_ID = 1 
	ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Heads]
, STUFF(
(	SELECT ', '+ ISNULL(C.Email_Address,'N/A') FROM Contacts C 
	WHERE C.Household_ID = Contact_ID_Table_Household_ID_Table.Household_ID 
	AND C.Household_Position_ID = 1 
	ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Head Emails]
, STUFF(
(	SELECT ', '+ ISNULL(C.Mobile_Phone,'N/A') FROM Contacts C 
	WHERE C.Household_ID = Contact_ID_Table_Household_ID_Table.Household_ID 
	AND C.Household_Position_ID = 1 
	ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Head Mobiles]
Minor children: To show all minor children, change the Household Position ID to 2.
STUFF(
(	SELECT ', '+ C.First_Name + ' ' + C.Last_Name FROM Contacts C 
	WHERE C.Household_ID = Contact_ID_Table_Household_ID_Table.Household_ID 
	AND C.Household_Position_ID = 2 
	ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Heads]
All household members: To show all household participants, remove the criteria for Household Position.
STUFF(
(	SELECT ', '+ C.First_Name + ' ' + C.Last_Name FROM Contacts C 
	WHERE C.Household_ID = SELECT Contact_ID_Table_Household_ID_Table.Household_ID 
	ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [HH Members]
Households page: You can change this view to work on the Households page. Use this criteria:
STUFF(
(	SELECT ', '+ C.First_Name + ' ' + C.Last_Name FROM Contacts C 
	WHERE C.Household_ID = Households.Household_ID AND C.Household_Position_ID = 1 
	ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Heads]
, STUFF(
(	SELECT ', '+ ISNULL(C.Email_Address,'N/A') FROM Contacts C 
	WHERE C.Household_ID = Households.Household_ID AND C.Household_Position_ID = 1 
	ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Head Emails]
, STUFF(
(	SELECT ', '+ ISNULL(C.Mobile_Phone,'N/A') FROM Contacts C 
	WHERE C.Household_ID = Households.Household_ID AND C.Household_Position_ID = 1 
	ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Head Mobiles]
View Clause
There is no set filter for this view, so enter any criteria you want to refine your view.

Participants Not Serving on a Ministry Team

This view can show the small group participants who are not currently serving on a ministry team.

Attention: This view example is offered as is and may need to be altered to fit your specific needs. For assistance, contact your SPoC; if you are a SPoC, contact Support for a Professional Services estimate (billed at an hourly rate).

Difficulty: Advanced

SQL Functions and Techniques Used:
  • EXISTS
  • GETDATE

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Participants.
Field List
Enter this SQL code to display the standard fields.
Contact_ID_Table.[Last_Name] AS [Last Name], Contact_ID_Table.[First_Name] AS [First Name], Contact_ID_Table.[Email_Address] AS [Email Address], Contact_ID_Table.[Mobile_Phone] AS [Mobile Phone], Contact_ID_Table_Participant_Record_Table_Participant_Type_ID_Table.[Participant_Type] AS [Participant Type]
View Clause
Enter this criteria to limit the view. Replace the values with the IDs you are filtering for.
Contact_ID_Table_Household_Position_ID_Table.[Household_Position]='Head of Household' 
AND Contact_ID_Table_Household_ID_Table_Congregation_ID_Table.[Congregation_ID] = 1 
AND EXISTS 
(	SELECT Group_Participant_ID FROM Group_Participants GP 
	JOIN GROUPS G ON G.Group_ID=GP.Group_ID 
	WHERE GP.Participant_ID=Participants.Participant_ID 
	AND G.Group_Type_ID = 1 AND ISNULL(GP.End_Date,GETDATE() +1)>GETDATE()) 
AND NOT EXISTS 
(	SELECT Group_Role_Type_ID FROM Group_Roles GR 
	JOIN Group_Participants GP ON GP.Group_Role_ID=GR.Group_Role_ID 
	JOIN Groups G ON G.Group_ID=GP.Group_ID 
	WHERE Participants.Participant_ID=GP.Participant_ID 
	AND GR.Group_Role_Type_ID IN (1,3) 
	AND G.Ministry_ID IN (22,29) 
	AND ISNULL(GP.End_Date,GETDATE() +1)>GETDATE())

Phone Numbers for a Household

This view can list all phone numbers for both heads in a household.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • TOP
  • EXISTS

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Households.
Field List
Enter this SQL code to display the standard fields.
Households.Household_Name,
(	SELECT Top 1 First_Name FROM Contacts C WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 ORDER BY Gender_ID) AS Head_1_Name,
(	SELECT Top 1 First_Name FROM Contacts C WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 AND EXISTS 
	(	SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1 AND C2.Household_ID = C.Household_ID GROUP BY Household_ID HAVING Count(*) > 1) 
GROUP BY Contact_ID, First_Name, Gender_ID ORDER BY Gender_ID Desc) AS Head_2_Name,Congregation_ID_Table.[Congregation_Name] AS [Congregation],Households.Home_Phone AS [Home Phone],
(	SELECT Top 1 Mobile_Phone FROM Contacts C WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 ORDER BY Gender_ID) AS Head_1_Mobile,
(	SELECT Top 1 Mobile_Phone FROM Contacts C WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 AND EXISTS 
	(	SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1 AND C2.Household_ID = C.Household_ID GROUP BY Household_ID HAVING Count(*) > 1) 
GROUP BY Contact_ID, Mobile_Phone, Gender_ID ORDER BY Gender_ID Desc) AS Head_2_Mobile,
(	SELECT Top 1 Company_Phone FROM Contacts C WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 ORDER BY Gender_ID) AS Head_1_Company_Phone,
(	SELECT Top 1 Company_Phone FROM Contacts C WHERE C.Household_ID = Households.Household_ID and Household_Position_ID = 1 AND EXISTS 
	(	SELECT 1 FROM Contacts C2 WHERE C2.Household_Position_ID = 1 AND C2.Household_ID = C.Household_ID GROUP BY Household_ID HAVING Count(*) > 1) 
GROUP BY Contact_ID, Company_Phone, Gender_ID ORDER BY Gender_ID Desc) AS Head_2_Company_Phone,Address_ID_Table.[Address_Line_1] AS [Street Address],Address_ID_Table.[City] AS [City],Address_ID_Table.[State/Region] AS [State],Address_ID_Table.[Postal_Code] AS [Zip Code]
View Clause
There is no set filter for this view, so enter any criteria you want to refine your view. For example, you could use the following criteria to filter for a specific congregation.
Congregation_ID_Table.Congregation_Name = 'Central Campus'

Security Roles with Page Permissions

This view can show all pages that have been granted permissions per security role. You can also see the number of users per security role.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • STUFF
  • EXISTS
  • Uses the Role Pages system tables

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Security Roles.
Field List
Enter this SQL code to display a list of pages for each permission level.
dp_Roles.Role_Name,
STUFF(
(	SELECT ', ' + P.Display_Name FROM dp_Role_Pages RP 
	JOIN dp_Pages P ON RP.Page_ID = P.Page_ID WHERE RP.Role_ID = dp_Roles.Role_ID 
	AND RP.Access_Level = 3 FOR XML PATH('')),1,2,'') AS [Full],
STUFF(
(	SELECT ', ' + P.Display_Name  FROM dp_Role_Pages RP 
	JOIN dp_Pages P ON RP.Page_ID = P.Page_ID  WHERE RP.Role_ID = dp_Roles.Role_ID 
	AND RP.Access_Level = 2 FOR XML PATH('')),1,2,'') AS [Mass],
STUFF(
(	SELECT ', ' + P.Display_Name  FROM dp_Role_Pages RP 
	JOIN dp_Pages P ON RP.Page_ID = P.Page_ID  WHERE RP.Role_ID = dp_Roles.Role_ID 
	AND RP.Access_Level = 1 FOR XML PATH('')),1,2,'') AS [Edit],
STUFF(
(	SELECT ', ' + P.Display_Name  FROM dp_Role_Pages RP 
	JOIN dp_Pages P ON RP.Page_ID = P.Page_ID  WHERE RP.Role_ID = dp_Roles.Role_ID 
	AND RP.Access_Level = 0 FOR XML PATH('')),1,2,'') AS [Read],
STUFF(
(	SELECT ', ' + P.Display_Name  FROM dp_Role_Pages RP 
	JOIN dp_Pages P ON RP.Page_ID = P.Page_ID  WHERE RP.Role_ID = dp_Roles.Role_ID 
	AND RP.Quick_Add = 1 FOR XML PATH('')),1,2,'') AS [Quick],
STUFF(
(	SELECT ', ' + P.Display_Name  FROM dp_Role_Pages RP 
	JOIN dp_Pages P ON RP.Page_ID = P.Page_ID  WHERE RP.Role_ID = dp_Roles.Role_ID 
	AND RP.Data_Exporter = 1 FOR XML PATH('')),1,2,'') AS [Export],
STUFF(
(	SELECT ', ' + P.Display_Name  FROM dp_Role_Pages RP 
	JOIN dp_Pages P ON RP.Page_ID = P.Page_ID  WHERE RP.Role_ID = dp_Roles.Role_ID 
	AND RP.File_Attacher = 1 FOR XML PATH('')),1,2,'') AS [Attach],
STUFF(
(	SELECT ', ' + P.Display_Name  FROM dp_Role_Pages RP 
	JOIN dp_Pages P ON RP.Page_ID = P.Page_ID  WHERE RP.Role_ID = dp_Roles.Role_ID 
	AND RP.Secure_Records = 1 FOR XML PATH('')),1,2,'') AS [Secure]
Sub-pages: To show sub-page permissions, use the following field list.
dp_Roles.[Role_Name] AS [Role Name],
(	SELECT STUFF(
	(	SELECT ', ' + P.Display_Name + '/' + SP.Display_Name  FROM dp_Role_Sub_Pages RSP 
		JOIN dp_Sub_Pages SP ON RSP.Sub_Page_ID = SP.Sub_Page_ID 
		JOIN dp_Pages P ON SP.Page_ID = P.Page_ID  WHERE RSP.Role_ID = dp_Roles.Role_ID 
		AND RSP.Access_Level = 3 FOR XML PATH('')),1,2,'')) AS [Full],
(	SELECT STUFF(
	(	SELECT ', ' + P.Display_Name + '/' + SP.Display_Name  FROM dp_Role_Sub_Pages RSP 
		JOIN dp_Sub_Pages SP ON RSP.Sub_Page_ID = SP.Sub_Page_ID  
		JOIN dp_Pages P ON SP.Page_ID = P.Page_ID  WHERE RSP.Role_ID = dp_Roles.Role_ID 
		AND RSP.Access_Level = 2 FOR XML PATH('')),1,2,'')) AS [Mass],
(	SELECT STUFF(
	(	SELECT ', ' + P.Display_Name + '/' + SP.Display_Name  FROM dp_Role_Sub_Pages RSP 
		JOIN dp_Sub_Pages SP ON RSP.Sub_Page_ID = SP.Sub_Page_ID  
		JOIN dp_Pages P ON SP.Page_ID = P.Page_ID  WHERE RSP.Role_ID = dp_Roles.Role_ID 
		AND RSP.Access_Level = 1 FOR XML PATH('')),1,2,'')) AS [Edit],
(	SELECT STUFF(
	(	SELECT ', ' + P.Display_Name + '/' + SP.Display_Name  FROM dp_Role_Sub_Pages RSP 
		JOIN dp_Sub_Pages SP ON RSP.Sub_Page_ID = SP.Sub_Page_ID  
		JOIN dp_Pages P ON SP.Page_ID = P.Page_ID  WHERE RSP.Role_ID = dp_Roles.Role_ID 
		AND RSP.Access_Level = 0 FOR XML PATH('')),1,2,'')) AS [Read]
User count: To show the number of users per security role, add the following field to the view.
, (	SELECT COUNT(*) FROM dp_User_Roles UR WHERE UR.Role_ID = dp_Roles.Role_ID) AS User_Count
View Clause
Enter this criteria to limit the list of security roles to only those with page permissions.
EXISTS (SELECT 1 FROM dp_Role_Pages RP WHERE RP.Role_ID = dp_Roles.Role_ID)
Sub-pages: Enter this criteria to limit the list of security roles to only those with sub-page permissions.
EXISTS (SELECT 1 FROM dp_Role_Sub_Pages RSP WHERE RSP.Role_ID = dp_Roles.Role_ID)
User count: Enter this criteria to limit the list of security roles to those that have any number of users.
EXISTS (SELECT * FROM dp_User_Roles UR WHERE UR.Role_ID = dp_Roles.Role_ID)
Order By
You can order the list by security role name.
Role_Name

Security Roles with Reports Permitted

This view can show the reports that have been granted permissions per security role.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • STUFF
  • EXISTS
  • Uses the Role Reports system tables

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Security Roles.
Field List
Enter this SQL code to display a list of reports for each security role.
dp_Roles.Role_Name,
STUFF(
(	SELECT ', ' + R.Report_Name FROM dp_Role_Reports RR 
	JOIN dp_Reports R ON RR.Report_ID = R.Report_ID 
	WHERE RR.Role_ID = dp_Roles.Role_ID FOR XML PATH('')),1,2,'') AS [Reports]
View Clause
Enter this criteria to limit the list of security roles to those with report permissions.
EXISTS
(	SELECT 1 FROM dp_Role_Reports RR 
	JOIN dp_Reports R  ON RR.Report_ID = R.Report_ID 
	WHERE RR.Role_ID = dp_Roles.Role_ID)
Order By
You can order the list by security role name.
dp_Roles.Role_Name

Security Roles with Tools Permitted

This view can show the tools that have been granted permissions per security role.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • STUFF
  • EXISTS
  • Uses the Role Tools system tables

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Security Roles.
Field List
Enter this SQL code to display a list of tools for each security role.
dp_Roles.Role_Name,
STUFF(
(	SELECT ', ' + T.Tool_Name FROM dp_Role_Tools RT 
	JOIN dp_Tools T ON RT.Tool_ID = T.Tool_ID 
	WHERE RT.Role_ID = dp_Roles.Role_ID FOR XML PATH('')),1,2,'') AS [Tools]
View Clause
Enter this criteria to limit the list of security roles to those with tool permissions.
EXISTS 
(	SELECT 1 FROM dp_Role_Tools RT 
	JOIN dp_Tools T ON RT.Tool_ID = T.Tool_ID  WHERE RT.Role_ID = dp_Roles.Role_ID)
Order By
You can order the list by security role name.
Role_Name

Tasks Attached to Contacts

This view can show contacts who have tasks.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • EXISTS
  • dp_UserID
  • Uses the Task system table

Other Resources: See "Task & Submission Tables" on Database Relationships.

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Contacts.
Field List
Leave this blank to use the default fields.
View Clause
Enter this criteria to limit the list of contacts to those with tasks.
EXISTS 
(	SELECT 1 FROM dp_Tasks T WHERE T.Completed = 0 
	AND T._Record_ID = Contacts.Contact_ID AND T._Table_Name = 'Contacts' )
Other pages: To use this view in other pages, replace the references to Contacts with the table for the page you want. For example, here's what you would enter to use the view on the Households page.
EXISTS 
(	SELECT 1 FROM dp_Tasks T WHERE T.Completed = 0 
	AND T._Record_ID = Contacts.Contact_ID AND T._Table_Name = 'Households' )
My assigned tasks: To only show tasks assigned to the logged in user, add the Assigned User ID clause to your filter.
EXISTS 
(	SELECT 1 FROM dp_Tasks T WHERE T.Completed = 0 
	AND T._Record_ID = Contacts.Contact_ID AND T._Table_Name = 'Contacts' 
	AND T.Assigned_User_ID = dp_UserID )

Top 50 Donors

This view can show the 50 donors who have given the most. You can filter by date range, program, and other criteria.

Difficulty: Advanced

SQL Functions and Techniques Used:
  • ID IN SELECT
  • SELECT TOP N
  • SUM
  • GETDATE
  • EXISTS
  • ISNULL
  • Configuration settings

In the navigation menu, click System Setup > Page Views. Click New View, and fill out the fields.

Page
Select Donors.
Field List
Leave this blank to use the default fields. Alternatively, you can add any of the following SQL code to show helpful details.
Donation totals: Add this SQL code to create a Donation Total field. Be sure to use a comma to separate this from any other fields.
(	SELECT SUM(D.Donation_Amount) FROM Donations D 
	WHERE D.Donor_ID = Donors.Donor_ID) AS Donation_Total
Filter by date range: If you update your View Clause to show donations for a particular date range, you must change the Donation Total field to use the same calculation for date. For example, here's what you would enter to see donations from the previous calendar year.
(	SELECT SUM(D.Donation_Amount) FROM Donations D   
	WHERE D.Donor_ID = Donors.Donor_ID 
	AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 ) AS Donation_Total
Filter by program: If you update your View Clause to show giving for a program, you must change the Field List to use the same criteria.
(	SELECT SUM(DD.Amount) FROM Donations D 
	JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
	WHERE D.Donor_ID = Donors.Donor_ID 
	AND DD.Program_ID = 3 AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 ) AS Donation_Total
Filter by statement type: If you update your View Clause to show giving for a statement type, you must change the Field List to use the same criteria.
(	SELECT SUM(DD.Amount) FROM Donations D 
	JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
	JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID 
	WHERE D.Donor_ID = Donors.Donor_ID AND Prog.Statement_Header_ID = 1 
	AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 ) AS Donation_Total
Filter for soft credit: If you update your View Clause to consider soft credit donations, you must change the Field List to use the same criteria.
(	SELECT SUM(DD.Amount) FROM Donations D 
	JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
	JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID 
	WHERE ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) = Donors.Donor_ID AND Prog.Statement_Header_ID = 1 
	AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 ) AS Donation_Total
View Clause
Enter this criteria to limit the list to the top 50 donors. This uses the configuration settings to omit anonymous donors.
Donors.Donor_ID IN 
(	SELECT TOP 50 D.Donor_ID FROM Donations D 
	WHERE D.Donor_ID NOT IN 
	(	SELECT CONVERT(int,CS.Value) 
		FROM dp_Configuration_Settings CS WHERE ISNUMERIC(CS.Value) = 1 
		AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
		AND CS.Application_Code = 'COMMON' AND CS.Domain_ID = D.Domain_ID)   
	GROUP BY D.Donor_ID  
	ORDER BY SUM(D.Donation_Amount) DESC)
Number of donors: You can change the number of top donors displayed by changing the number in the TOP statement. For example, to show the top 100 donors, you would use the following.
SELECT TOP 100 D.Donor_ID FROM Donations D

Filter by date range: To only show donations for a particular date range, you can compare the Donation_Date. If you include the Donation Total field, it should include the same calculation for date.

For example, here's what you would enter to see donations from the previous calendar year.
Donors.Donor_ID IN 
(	SELECT TOP 50 D.Donor_ID FROM Donations D 
	WHERE D.Donor_ID NOT IN 
	(	SELECT CONVERT(int,CS.Value) FROM dp_Configuration_Settings CS 
		WHERE ISNUMERIC(CS.Value) = 1 
		AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
		AND CS.Application_Code = 'COMMON' AND CS.Domain_ID = D.Domain_ID) 
	AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
	GROUP BY D.Donor_ID 
	ORDER BY SUM(D.Donation_Amount) DESC)
Filter by program: To show giving for a program, add a JOIN for Donation Distribution and the amount calculated from the distribution rather than the donation. The Field List should include the same calculation.
Donors.Donor_ID IN 
(	SELECT TOP 50 D.Donor_ID FROM Donations D 
	JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
	WHERE D.Donor_ID NOT IN 
	(	SELECT CONVERT(int,CS.Value) FROM dp_Configuration_Settings CS 
		WHERE ISNUMERIC(CS.Value) = 1 
		AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
		AND CS.Application_Code = 'COMMON' AND CS.Domain_ID = D.Domain_ID) 
	AND DD.Program_ID = 3 
	AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
	GROUP BY D.Donor_ID 
	ORDER BY SUM(DD.Amount) DESC)
Filter by statement type: To show giving for a statement type, such as Tithes, add a JOIN for the Program that determines the Statement Type and filter for the Statement Type. Program ID is omitted.
Donors.Donor_ID IN 
(	SELECT TOP 50 D.Donor_ID FROM Donations D 
	JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
	JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID 
	WHERE D.Donor_ID NOT IN 
	(	SELECT CONVERT(int,CS.Value) FROM dp_Configuration_Settings CS 
		WHERE ISNUMERIC(CS.Value) = 1 
		AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
		AND CS.Application_Code = 'COMMON' AND CS.Domain_ID = D.Domain_ID) 
	AND Prog.Statement_Header_ID = 1 
	AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
	GROUP BY D.Donor_ID 
	ORDER BY SUM(DD.Amount) DESC)
Filter for soft credit: If needed, you can change the view to consider soft credit donations in your calculations.
Donors.Donor_ID IN 
(	SELECT TOP 50 ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) FROM Donations D 
	JOIN Donation_Distributions DD ON D.Donation_ID = DD.Donation_ID 
	JOIN Programs Prog ON DD.Program_ID = Prog.Program_ID 
	WHERE ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) NOT IN 
	(	SELECT CONVERT(int,CS.Value) FROM dp_Configuration_Settings CS 
		WHERE ISNUMERIC(CS.Value) = 1 
		AND CS.Key_Name IN ('DefaultDonorID','UnassignedDonorID','AnonymousCheckID') 
		AND CS.Application_Code = 'COMMON' AND CS.Domain_ID = D.Domain_ID) 
	AND Prog.Statement_Header_ID = 1 
	AND YEAR(D.Donation_Date) = YEAR(GETDATE())-1 
	GROUP BY ISNULL(DD.Soft_Credit_Donor,D.Donor_ID) 
	ORDER BY SUM(DD.Amount) DESC)
Order By
If you added the Donation Total field, you can order the list by that amount.
Donation_Total DESC
portal
MinistryPlatform

Last updated: February 14, 2025