How to Build a Query with Configurable Joins

Standard queries allow users to access certain data using pre-defined joins between the many related tables of a Slate database. With a Configurable Joins query, joins are determined by the user, based on the exact information needed. This article describes how to join data in a Configurable Joins query as well as the circumstances under which different types of filters and exports should be added in order to achieve the desired results.

  Tip

For a detailed introduction to the fundamentals of Configurable Joins and an overview of the available functionality, review the articles in the Configurable Joins section of the Knowledge Base.

Creating a New Configurable Joins Query
  1. Navigate to Queries / Reports and select either New Query or Quick Query.
  2. Select a Type of Configurable Joins Library and a Category:
    • Records - Query bases for records (e.g. person records, application records)
    • Related - Query bases that relate to a parent record (e.g. schools, tests, sports)
    • System - Query bases for system tables in Slate (e.g. tags, bins, rounds)
  3. Choose the appropriate Base from the category. The base determines what each row of the query will represent.
Adding Exports and Filters

As with any standard query in Slate, exports determine the columns while filters limit the rows. Unlike a standard query, however, several different kinds of exports and filters can be added to a Configurable Joins query based on what data is needed:

  • Data from the same table: if a field is scoped to the query base, it will be available automatically for use as a direct export/filter.
  • Data from a related table: if a field is scoped to a different, but related, query base, it can be accessed either via an extended export/filter or by adding a join to the query itself.
    • One-to-One relationships exist between some of these tables, such as School to Person.
    • One-to-Many relationships exist between others, such as Person to Applications. In order to add filters and exports in these cases, it is necessary to select which one of the possible many related records is needed.
  • Calculated data or data about the many: if the data needed is an aggregate of many related records, the result of a formula or concatenation, an existence, or other type of calculation, then subquery exports or subquery filters should be used.
Adding Exports or Filters for Data from the Same Table

direct_only.JPG

Click or drag Export to add direct exports:

  • Direct Exports are used to add columns for data belonging to the base of the query (e.g. exporting person-scoped fields when querying for person records).

      Tip

    Trying to get an export for a system field with multiple Export Values like Prefix? Set up a Translation Code table and use it within your Configurable Join.

Click or drag Filter to add direct filters:

  • Direct Filters are used to limit the rows of the query using data belonging to the base of the query (e.g. filtering using person-scoped fields when querying for person records).
Adding Exports or Filters for Data from a Related Table

extended_only.JPG

Click or drag Export to add extended exports:

  • Extended Exports are used to add columns for data belonging to other bases that are related to the base of the query and are one step removed (e.g. exporting school-scoped fields when querying for person records).

Click or drag Filter to add extended filters:

  • Extended Filters are used to limit the rows of the query using data belonging to other bases that are related to the base of the query and are one step removed (e.g. filtering using school-scoped fields when querying for person records).

Click or drag Join to add a join to a related table:

  • Add a join when exports or filters are needed for one specific row on another table. Once a join is added, direct exports and filters about the selected related record will be available in the query.

When adding filters or exports for data on a related table, select which one of the possible one-to-many relationships should be joined. 

choose_join.JPG

Important!

Adding a join does not change the number of rows returned in the query.

One way to approach the selection of the join is to ask yourself two questions:

  • How should the possible rows on the related table be sorted?
  • When the table is sorted in a particular way, which row is needed (i.e. the top row, the next highest, and so on)?

By default, Slate ranks, or sorts, each table using certain pre-defined ranking criteria, as explained in the article on Determination of Table Ranks. In the Slate Template Library, many filters and exports make use of these Slate-defined table rankings to return data about specific rows, such as School 1 or School by Level of Study, Rank.

Joins for Rank, Rank Overall or Type, Rank make use of these pre-defined Slate rankings, and are known as Library Joins. Choose one of these options when the objective is to sort the related table using the default ranking criteria. Once selected, these join options will prompt you to select the specific row based on that ranking.

  • Example: Join in data about the Rank 1 Overall School:

    library_join.JPG

Alternatively, if the table should be sorted based on different, user-defined criteria rather than the Slate ranking criteria, and then the row selected based on that custom ranking, select the join option without 'Rank' (e.g. Addresses, Decisions, Schools, Relations, etc.). These are known as Subquery Joins, which allow you to specify a sort for those cases where the related table needs to be sorted in a custom way, and then use the Row Offset to indicate whether the top row, the next highest, or some other row from the table should be joined when the table is sorted using those criteria. 

  • Example: Join in data about the top (row offset 1) school when sorting schools by degree conferred date (desc):

    subquery_join_conferred.JPG

  Best Practice: Rename all joins to be descriptive. Always include a sort if using a custom ranking.

By default, the name of the join will reflect the join type (e.g. Application by Rank, Application by Rank Submitted). Because the name of the join will be added to each export or filter that uses it, providing a descriptive name (e.g. Rank 1 Application) helps to prevent confusion by specifying which one is being joined. If using a custom ranking, always include a sort and then rename the join accordingly.

  When should I add a Join to the query and when should I use an Extended Export/Filter?

  • Extended Exports and Filters allow you to see, at a glance, all of the fields that are available on a related base. This can be helpful when, as your first step, you want to decide what data points you want to add and then decide which of the possible one-to-many relationships those data points should come from.
  • Joins allow you to see all the possible ways in which a different base relates to the base of your query.
    • This can be helpful when, as your first step, you want to decide which of the possible one-to-many relationships to make available for use in the query, and then decide what data points from that joined relationship to include as filters or exports.
    • In addition, if the data you need is more than one step removed from the base of the query, adding the necessary joins to the query will likely be the most efficient way to access the data you need. For example: From a Person query, join to the Rank 1 Application and then from the Rank 1 Application join to the Rank 1 Decision.

The end result will be the same: whether you first add an extended export/filter or a join, Slate will prompt you to define which records from a related table should be used in this query.

Example 1: Export the Name of the Rank 1 Overall School in a Person query

As an Extended Export:

extended_first.gif

As a Join:

join_first.gif

Example 2: Export details about the rank 1 decision for a person's rank 1 application
Adding Exports and Filters for Calculated Values or Data about the Many

Add any Subquery Exports or Subquery Filters as desired:

sub_buttons.JPG

  • Add a subquery export when multiple values need to be combined into a single column, through an aggregate function or a concatenation. Example: For each person record, insert a column that contains a comma-separated list of events attended.
  • Add a subquery filter when the query results need to be limited based on a one-to-many relationship. Example: Limit the query results to include only those person records that have an application.


A subquery filter or subquery export creates a query within a query, and then performs an action, such as a calculation, on the results of that query. Subqueries make it possible to look across the many of the one-to-many relationships in Slate to determine which values to return.

For more details on the outputs of subquery exports and the aggregate functions of subquery filters, see the corresponding articles in the Configurable Joins category of the Knowledge Base.

  Tip

When using a subquery filter, determine first whether the aggregate function should be performed on related records, on a field value on the same table, or on a field value on a related table.

  • To filter the query results based on an aggregate of related records, a join must be added within the subquery filter. For example, to filter for person records that have an application in a specific round, a join from Person to Applications  is needed, followed by a join from Applications to Lookup Round.

    has_app_round.gif

  • If the goal is to perform the function on a field value on the same table, no join is needed. Instead, an export for that field should be added within the subquery filter. For example, to filter for person records where a Birthdate exists, an export of Birthdate should be added.

    has_bd.gif

  • Finally, in order to reference field values on a related table, both a join and an export need to be added to the filter. For example,  to filter for person records that have a School GPA below a 3.0 for at least one of the schools they attended, a join from Person to Schools is needed, along with an export for GPA.

    min_gpa.gif

Important!

Historically, the Prospects base excluded by default any person records where the Opt Out tag was set. The Configurable Joins base of Person does NOT automatically exclude records with the Opt Out tag: instead, it replicates the functionality of the historical Prospects (including opt-outs) base, which included all person records.

To exclude person records with the Opt Out tag from a query on the Person base, use a subquery filter with a join from Person to Tag and Tag to Lookup Tag, and filter for Lookup Tag / Name IN Opt Out. Finally, choose the Aggregate of Not Exists:

opt_out_example.JPG

Was this article helpful?
28 out of 41 found this helpful