Ideas to Impact Conference: May 28-31, 2019

Experience over 150 classes, inspiring speakers, software and ministry experts, and church staff sharing ideas.

Page tree

While you can usually filter events based on a combination of the calendars, dates, and report settings you choose, you might need to filter your events based on more advanced criteria. In these situations, you can use advanced queries.

Advanced queries contain one or more conditions. Queries with more than one condition use keywords to determine whether any or all of the conditions must be met. Complex queries can be built by putting conditions into groups.

When you process the query, ACS Facility Scheduler looks at every event in your database and returns those events that match the conditions you specified. After processing a query, you can print the results or export the results to a file. 

 To create an advanced query
  1. On the Reports tab, click Advanced Queries.
  2. In the Actions group, click New Query.
  3. In the Find Events for: drop-down list, select an event date option. The Event Date query field updates automatically with your selection.
    • This Week – The current week starting on Sunday and ending on Saturday, including today.
    • Next Week – The upcoming week starting on Sunday and ending on Saturday.
    • Next 7 Days – Today plus 7 days.
    • Next 14 Days – Today plus 14 days.
    • Next 30 Days – Today plus 30 days.
    • Next 12 Months – Today plus 12 months.
    • Last 30 Days – The last 30 days to today.
    • Last 12 Months – The last 12 months to today.
    • Custom – Select to query a custom date range.
  4. To add conditions, click .
  5. Continue adding and deleting conditions, changing keywords, and adding and deleting groups. For more information on each, see below.
  6. To process the search, in the Actions group, click Run.
  7. To save the query for future use, in the Actions group, click Save.
  8. Enter a Name for the query and click OK.

With advanced queries, you create conditions that look for specific data. For example, you can create a condition that looks for all events that are flagged as “All Day” events. Conditions contain a query field, an operator, and a value.

The figure below shows the parts of a condition statement. In this example, the query returns all events that have a status of pending.

The query field is the type of data by which you want to filter events. For example, an event start time or booking name.

The operator is a mathematical operator that determines how you want to compare the data with the value. If you want to find events with a start time of 11:00 a.m., you would use the Equals operator. If you want to find events that start on or after 11:00 a.m., you would use the Is greater than or equal to operator.

 To create a condition
  1. On the Query tab, click . A generic condition displays.
  2. Click All Day to select a query field.
  3. Click Equals to select an operator. The operators available depend on the query field selected.
  4. Click <enter a value> to enter a value. The value is the specific value by which you want to filter events. For example, the church van, the sanctuary, or 11:00 a.m. The values available depend on the query field selected.
 To delete a condition
  • On the Query tab, click next to the condition you want to delete.

Queries can contain multiple conditions. You can use the And keyword if you want to find events that match all the conditions in the query. You can use the Or keyword to find events that match any of the conditions in the query.

And queries

The figure below shows an advanced query that returns all youth events that occur between 1/22/2016 and 2/5/2016.

This query has two conditions: an event date between 1/22/2016 and 2/5/2016, and a calendar that equals youth calendar. Because the And keyword is used, events must meet both conditions to be included in the results.

 

If the event is a spanning event, the actual start date of the spanning event must be included within the conditions.

Or queries

The figure below shows an advanced query that returns all events that occur on Christmas Eve or Christmas. This query has conditions: a start date that is December 24, 2013 and a start date that is December 25, 2013.

Because the Or keyword is used, events can meet either condition to be included in the results.

 To change a keyword
  1. On the Query tab, click the keyword you want to change.
  2. Select the keyword you want to use.

 Conditions can be grouped together to form complex queries. By creating groups of conditions, you can use both the And and Or keywords in the same query.

The figure below shows an advanced query that returns all events that use the wedding event type, and have a status of approved or pending. The two status conditions are in a group so that they can use the Or keyword. The And keyword is used at the top level to ensure that the event type condition is matched and that at least one of the statuses is matched.

 To add a group
  1. On the Query tab, click the keyword under which you want to add a group.
  2. Click Add Group.
 To delete a group
  1. On the Query tab, click the keyword for the group you want to delete.
  2. Click Remove Group.

Here are some examples of queries you can use in ACS Facility Scheduler.

Example 1:  List of events being held within a specific period of time, but not in the Sanctuary.

 

Example 2:  List of events being held within a specific period of time and on the Make a Joyful Noise calendar.

 

Example 3List of events being held within a specific period of time, on the Make a Joyful Noise calendar and in the Choir Rehearsal Hall.

 

Example 4:  List of events being held within a specific period of time for specific tags.

 

Example 5:  List of events being held within a specific period of time on the listed calendars.

Example 6:  List of published events being held within a specific period of time on the listed calendars.

 

Example 7List of published events being held within a specific period of time including the selected Bookings.

 

Example 8:  List of events needing Chairs and found on the Kids for Action Calendar.

 

Example 9:  List of events by selected Bookings.