This View example displays the Group Participants that match a Selection of Participants.
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) )
The Selection must be the default selection on Participants created by the User who is logged in.
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')
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
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)