CONTACT USBack to MinistryPlatform Site
Your Knowledge Base is moving on 3/25/24! Our new Help Center provides all the articles you know and love (plus so much more) in a one-stop shop. Ask your SPoC for details!

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. The highlighted portion must be replaced with 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 ))
Quick Tip: For immediate use, find an Event_ID, open Advanced Search, paste the SQL in the criteria with the Event_ID. Leave fields empty.
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')
Tip: You could combine this with additional criteria to narrow down the list to current or Future Events to make it more useful.
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)
        )
    )
)
Tip: You could simplify this by hard-coding the Page ID for your Events page (replace the last select statement and parentheses with an ID).

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