Subquery Filters
  • 23 Apr 2024
  • 1 minute read
  • Dark
    Light
  • PDF

Subquery Filters

  • Dark
    Light
  • PDF

Article summary

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

Exists

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

sub_filter_exists.JPG

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:

sub_filter_not_exists.JPG

Count

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:

sub_filter_count.JPG

Average

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:

sub_filter_average.JPG

Minimum

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:

sub_filter_min_gift.JPG

Maximum

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:

sub_filter_max.JPG

Sum

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:

job_duration.JPG

Comparison

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:

comparison_filter.JPG

Formula

Finally, formulas may be used to limit the results of a query. To create a 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:

mceclip1.jpg

Tip

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

filter_formula.JPG

formula_filter.JPG


Was this article helpful?