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
To View all minor children, change the Household Position ID to 2:
- STUFF SQL Function
- ISNULL SQL Function