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

🏔️ Summit 2024 Features

Summit 2024 brought a number of quality-of-life improvements to subquery filters, including:

• Subquery copy/paste
  Use ctrl/cmd + c to copy an existing subquery export. Paste into a new subquery export with ctrl/cmd + v.
  
  📝 Note: Check your browser's clipboard settings if you find you can’t copy/paste subqueries.

  

• Subquery counts
  A count of Matching Rows shows the number of records returned by the subquery.
  

• Subquery check logic
  Select the Check Logic cogwheel and enter the name of a record to see whether it meets your subquery export’s filter criteria.

  

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:

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:

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:

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:

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:

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:

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:

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:

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: