Group Participants in Selection
This View example displays the Group Participants that match a Selection of Participants.
Difficulty: Advanced
Page: Group Participants
Features: Uses a Selection to control a View.
Group Participants in Default Participant Selection
To create a View that lists all the Group Participants in a selection, the following SQL may be used for the criteria:
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))
How to Use the View
Create a Selection of Participants. Navigate to Group Participants, and select this View from the list.
Limits
The Selection must be the default selection on Participants created by the User who is logged in.
Variations
Named Selections
For use with other Selections, you can change dp_Default to the name of the selection. Make sure it is in single quotes:
Selection_Name = 'dp_Default'
To use a list of named selections (the View will show all records matching all the Selections), use the IN comparison:
Selection_Name IN ('Name 1', 'Name 2', 'Name 3')
Other User Selections
If a User other than the logged in User will generate the Selection, you can change dp_UserID to the ID of the User who will maintain the Selection:
User_ID = 108
Active Groups and Group Participants
This variation uses 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)
Techniques
- GETDATE SQL Function
- ISNULL SQL Function
- Subqueries
- ID IN SELECT