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.

Participant Heads

 

This View shows both Heads of Household and their contact information associated with the Participant.

Difficulty: Advanced
Purpose: Creates a Field with a List of all associated Heads of Households, a field with their phone numbers and a field with their email addresses.
Page: Participants

Heads of Household Fields

Here are three fields which display the Names, Emails and Mobile Phones for the Heads of Household:

,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]

Variations

Minor Children

To View 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 View 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]
Household Page

To create the same fields on the Households Page, you can use this version. The highlighted portion is the part that has been altered for use in Households:

,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]

Techniques