Events in Series
This view example displays a list of Events which are part of a Series.
Difficulty: Advanced
Purpose: To Show the Events from a Series.
Page: Events
Filters
Event In Specific Series
The following SQL must be altered and used for the criteria. Replace the Event_ID of one of the Events in the Series:
Events.Event_ID IN (SELECT SR1.Record_ID
FROM dp_Sequence_Records SR1 WHERE Sequence_ID IN
(SELECT SR2.Sequence_ID FROM dp_Sequence_Records SR2
WHERE SR2.Table_Name = 'Events' AND SR2.Record_ID = 0 ))
Events In Any Series
To list Events that are part of any Series, use the following criteria:
Events.Event_ID IN (SELECT SR.Record_ID FROM dp_Sequence_Records SR WHERE SR.Table_Name = 'Events')
Events In Selected Series
This variation uses the default Selection to determine which Series to return. To use, select an Event (leave it unsaved so it is the default Selection) and choose the View from the list. You will get a list of Events in the Series matching your Selection:
Events.Event_ID IN(SELECT SR1.Record_ID FROM dp_Sequence_Records SR1 WHERE Sequence_ID IN (SELECT SR2.Sequence_ID FROM dp_Sequence_Records SR2 WHERE SR2.Table_Name = 'Events' AND SR2.Record_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.Display_Name = 'Events' AND P.Filter_Clause IS NULL) ) ))
Fields
Series ID
To add the Series ID to the view as a field, you can use the following SQL:
(SELECT SR.Sequence_ID FROM dp_Sequence_Records SR WHERE SR.Table_Name = 'Events' AND SR.Record_ID = Events.Event_ID) AS [Series ID]
Techniques
This View uses the following SQL Functions and techniques:
- SELECT TOP 1
- SELECT IN
Other Resources