Transitioning to Configurable Joins
  • 21 Nov 2023
  • 3 minute read
  • Dark
    Light
  • PDF

Transitioning to Configurable Joins

  • Dark
    Light
  • PDF

Article summary

Configurable Joins are a powerful tool to query your database. As you transition from Local or Slate Template Library (STL) queries to queries that use Configurable Joins, you'll encounter a few differences in features and terminology. This article will provide an overview of familiar aspects of Local and STL queries, along with their equivalents in Configurable Joins.

Bases

Due to the flexibility and power of Configurable Joins, there are many more bases than in the STL. We provide a list of every base and what it does. However, it's worth highlighting some commonly-used STL bases and their equivalent in Configurable Joins:

Local / STL

Configurable Joins

Prospects base

Person base; add a filter to exclude opt-outs

Prospects (including opt-outs) base

Person base

Prospects and Applicants base

Person base; join to Application by Rank (Rank = 1)

Saved Query Parts

In Local/STL queries, exports and filters could be saved to a "pinned" section at the top of the Insert Query Part popup window, making it easy to find commonly used query parts.

mceclip0.png

In Configurable Joins, you can use query libraries to achieve the same result. Library exports, filters, and joins are shown at the top of the Insert Query Part popup window.

mceclip1.png

Export Types

There are two types of exports that are slightly different in Configurable Joins:

Local / STL

Configurable Joins

Formula export
mceclip0.png

Subquery export - Set Output to Formula
mceclip1.png

Existence export
mceclip2.png

Subquery export - Set Output to Existence
mceclip1.png

Existence Filters

A common type of filter is one that checks for the existence of a field or related item.

Local / STL

Configurable Joins

Field Value Exists
mceclip4.png

Subquery filter - Add the relevant export
mceclip5.png

Has Related Item (for example, Has School Level of Study)
mceclip1.png

Subquery filter - Join to the relevant table and add a filter
mceclip2.png

Related Information

Base joins are the most straightforward way to link related information in the database. The STL provides information from many related tables in Slate. In Configurable Joins, this information can be exported by adding a base join. This section lists some common data points from the STL and what base join to choose to access this information in Configurable Joins.

Many of these joins use rank. Despite its name, rank has nothing to do with rating or grading records - it's how Slate determines which items are the most relevant. For example, a student's Rank 1 Application is the application that Slate thinks you're most likely to query on. You can read about how ranks are determined if you'd like to know the details.

Person Base Joins

Here are some common base joins for queries in the Person base. 

Local / STL

Base Join

Active Address

Address by Rank Overall (Rank = 1)

Interest #1

Interest by Rank (Rank = 1)

Job #1

Job by Rank (Rank = 1)

Mailing Address

Address by Type, Rank (Type = Mailing Address, Rank = 1)

Origin Source

Origin Source by Group and First/Last selection

Permanent Address

Address by Type, Rank (Type = Permanent Address, Rank = 1)

School #1

School by Rank Overall (Rank = 1)

School #1 Organization Details

Join from School by Rank Overall (Rank = 1) to Organizations

School #1 Address

Join from School by Rank Overall (Rank = 1) to Organizations; then join from Organizations to Address by Rank Overall (Rank = 1)

What about applications?

Wondering how to get application information from the Person query base? The Person base has one row per person record, but a person can have multiple applications. To add application information, you must specify which application should be joined. 

  • Application by Rank joins an application using its rank, regardless of whether it has been submitted. Adding this base join and setting Rank = 1 is equivalent to the "Prospects and Applicants" query base from the STL.

  • Application by Rank Submitted joins a submitted application using its rank. 

Application Base Joins

Here are some common base joins for queries in the Application base. 

Local / STL

Base Join

All Person information

Person
Once you've added this base join, you can add additional base joins for the information in the Person table above.

Application Details: Bin

Current Bin

Application Details: Round

Lookup Round

Application Details: Period

Join from Lookup Round to Lookup Period

Decision #1

Decision by Rank (Rank = 1)

Decision (First)

Decision by Rank Reverse (Rank = 1)

Decision Most Recent Confirmed

Decision by Rank Confirmed (Rank = 1)

Decision Most Recent Released

Decision by Rank Released (Rank = 1)

Decision Most Recent Released (Received)

Use a subquery (see next section)

Complex Logic

Many of the exports and filters in the STL use logic that's more complex than individual base joins. They may make complicated joins, perform calculations across multiple rows, or transform the data in other ways. In Configurable Joins, complex logic often requires subquery exports and subquery filters described in the linked articles.


Was this article helpful?

What's Next