Subquery Filters

A subquery filter looks across the 'many' of the one-to-many relationships in Slate to determine which records to return in a query. Several aggregate functions can be used to filter records based on values on other query bases, including:

  • Exists
  • Not Exists
  • Count
  • Average
  • Minimum
  • Maximum
  • Sum
  • Comparison
  • Formula


The Exists function can limit query results based on the existence of a related record on another base, similar to the Slate Template Library 'Has' filters. For example, to filter for person records that have an application, the subquery filter should include a join to Applications:


Not Exists

Similarly, there is a Not Exists function, which is used to limit the query results to those where there are no matching records on the related table or no value for the specified export. For example, No Address:



The Count Aggregate function is used to return records based on the specified count of related records, such as querying for person records that have greater than or equal to three test scores:



Subquery filters can also perform calculations on exports from related bases, such as limiting the query results of person records to those where the average school GPA is greater than or equal to a 3.0:



Aggregate functions can also be used to limit records based on the minimum value. In this example, person records are being returned when the lowest gift amount is less than $100:



Similarly, a function type of Maximum may be selected. For example, a query of person records can be filtered based on the maximum ping duration:



An aggregate function can also evaluate the sum of values from related records, such as returning person records where the sum of work experience across multiple jobs is greater than 60 weeks:



Subquery filters may also be used to compare two values of the same type, such as dates, integers, reals, or strings. For example, the comparison filter may be used to return records where the Application Major and the Person Major are not the same:



Finally, formulas may be used to limit the results of a query. To recreate the Slate Template Library filter for Last Name Contains, for example, add the export of Person Last. In combination with the LIKE operator, use A% to find names that start with A or [A-F]% to find names that start with A-F:



Type @ in the Formula box to see a list of suggested exports and # to see a list of suggested functions.


For example, Date Difference (day) could be used to limit the results of the query to records that applied at least 3 days after being created as person records in the system:


The Formula box also accepts other T-SQL functions, such as using the LEN function to identify strings over a certain length:


Was this article helpful?
18 out of 26 found this helpful