CONTACT USBack to MinistryPlatform Site
Your Knowledge Base is moving on 3/25/24! Our new Help Center provides all the articles you know and love (plus so much more) in a one-stop shop. Ask your SPoC for details!

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