Group Participant Attributes
This View example displays the Group Participants in a specific Ministry and the notes field of the Allergies and Special Needs field.
Difficulty: Advanced
Page: Group Participants
Features: Adds a field to show whether a Group Participant has a specific Attribute.
Purpose: To create a View that lists all the Group Participants from a Ministry and whether they have an Allergy or Special Needs.
Field List
Replace the value with 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
Replace the value with 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())
Variations
View All Members of a Specific Group Rather than a Ministry
View Clause (Replace the value with appropriate role type ID):
Group_ID_Table.[Group_ID] = 1 AND GetDate() BETWEEN Group_Participants.Start_Date AND ISNULL(Group_Participants.End_Date,GetDate())
Techniques
- SELECT TOP