A subquery is an inner, nested query within an enclosing query.

A subquery export—as opposed to a subquery filter, or a subquery join—is a type of subquery that returns export data.

Like regular exports, subquery exports let you define a column expression. What sets them apart from regular exports is their ability to return different kinds of output. These outputs include:

• Rank: Return a given rank for data with multiple entries, like email addresses.

• Coalesce: Return only one of two or more possible exports.

• Concatenate: Combine multiple exports.

• Aggregate: Perform arithmetic operations on exports and output the result.

• Dictionary: Return a formatted array available for Liquid looping.

• Existence: Return a certain output when existence conditions for some data are fulfilled.

• Split: Splits a string into rows of substrings, based on a specified separator character.

• Formula: Use SQL to perform operations on exported values.

• XML: Converts data to XML for use in integrations.

• JSON: Converts data to JSON for use in integrations.

Subquery exports vastly expand your query toolbelt. They can prove useful for formatting data just the way you want it.

Example: Concatenate

Expand the section below to get a taste for what subquery exports can do, or continue to explore all of the subquery export output types in more detail.

🏔️ Summit 2024 Features

Summit 2024 brought a number of quality-of-life improvements to subqueries, 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 & preview results
  A count of Matching Rows shows the number of records returned by the subquery.
  Select Preview Results in a subquery to see a representative list of results.

  

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

  

Rank

The rank subquery export output pulls one row from a related table. Which row is pulled is dictated by a sort.

Some Slate tables store data such that multiple rows or records may be related to the same record in another table. For example, the School, Address, and Decision tables. These tables are on the many end of the one-to-many relationship.

These tables calculate and store a rank value. When such tables need to be joined in queries, and only one row out of many possible rows is needed, this rank value specifies which row should be used in the join.

While there are system-wide standard methods to determine the ranking for a particular table, the Rank output in a Subquery allows values to be returned based on a desired custom ranking.

📝 Note: Some tables do not calculate table ranks and as a result need to be manually sorted. See Ranking Exclusions headings in Database Structure & Determination of Table Ranks.

Example: Return school based on most recent date for conferred degree

Say a student received a Bachelors’ Degree from one institution, and later took a course at another institution. The latter institution ranks higher overall than the degree-granting institution.

This happens because schools are table-ranked by default based on certain attributes, like Priority, or Dates Attended.

An export or join specifying Rank 1 School may, in this example, return a mixed set, where some rank 1 schools conferred a degree and others did not.

So, how do we specify what we actually mean when we say a school is “rank 1”: that it is the student’s most recent degree-conferring school? We can do this with the rank subquery export output:

1. In a person-scoped query, select Subquery Export.

2. Enter a name for the subquery export, like “Rank 1 School”.

3. From the Output list, select Rank (selected by default).

4. Under Filters, select Join.

5. Select Schools.

6. Select Continue. This join returns all schools attended.

7. Select Export.

8. Under Schools, select Name.

9. Select Continue.

10. Select Sort.

11. Select the direct export by which the schools should be custom-ranked. In this example, we want to rank the schools by the date a degree was conferred. So, select Conferred Date.

12. Select Continue.

13. Select Edit (or double-click) on the sort value.

14. To order the schools from the most recent conferred date, select Descending from the Sort Order list.

15. Select Save.

16. Click Save to save the subquery export.
    

In the following example, display values from School Rank 1 (blue) and School Rank 2 (red) use the standard School table ranking.

This person attended a school after a degree was granted earlier by a different institution, but because the original rank 1 school has no conferred date, the column Rank 1 School (by conferred date) (green) returns data from the degree-granting institution instead.

Coalesce

The coalesce subquery export output returns the first non-null value of at least two potential exports.

If more than one value among the potential exports are non-null, Slate decides which value to pull by the order you place them in.

Example: Pull either TOEFL or IELTS scores, but prefer TOEFL if both are present

Say your school’s preferred English Proficiency Test is TOEFL, but you’ll accept IELTS if no TOEFL score exists.

You can use a coalesce subquery export to choose only the TOEFL score if both are present, and to fall back to an IELTS score if that’s all that’s available.

To do this:

1. In a person-scoped query, select Subquery Export.

2. Enter a name for the subquery export, like “ELP Score Coalesced”.

3. From the Output list, select Coalesce.

4. Select Subquery Export.

5. Configure the following settings:

   • Name: “TOEFL Total”

   • Output: Rank

   • Joins: Select Tests - TOEFL - Internet-based Test (iBT).

   • Exports: Under TOEFL - Internet-based Test (iBT), select Total.
     

6. Select Save.

7. Select Subquery Export.

8. Configure the following settings:

   • Name: “IELTS Total”

   • Output: Rank

   • Joins: Select Tests - IELTS.

   • Exports: Under Tests - IELTS, select IELTS Overall Band Score.
     

9. Select Save.

10. Click Save to save the subquery export

When you preview results (in this case, filtered to just records with IELTS or TOEFL scores for illustrative purposes), we see that the coalesce output has chosen TOEFL scores where present, IELTS scores when absent:

Concatenate

The concatenate subquery export output joins multiple values together in one expression.

To configure a subquery to concatenate multiple values, make sure that the necessary joins are included within the subquery part. These joins are not configured on the main query.

You can style your concatenate output using the following settings:

• Row Limit:

• Row Separator:

• Row Offset:

• Export Separator:

• Literal:

Example: Custom Label with Name and School Information

1. Click or drag Subquery into the Exports section. Give the subquery part a Name.

2. In the Edit Part window, select Concatenate for Output.

3. In the Add Filter section, click Join.
   To use school data in the concatenation, select one of the School joins. In the following example, we use School by Level of Study, Rank, with the following configuration:

   • Name: Rank 1 High School

   • Level of Study: High School

   • Rank: 1

4. We can now add School exports that return values for the Rank 1 High School. Since the main query uses a Person base, Person exports are already directly available in the subquery part.

5. To define the concatenation, we can drag the exports into the desired order and insert literals for any other characters or strings as needed:

   

6. Click Save.

The Custom Label column now displays concatenated values:

Example: Return a comma-separated list of schools attended

1. In the Edit Part window, select Concatenate for Output.

2. In the Add Filter section, click Join.

3. Select Schools to look across the many possible related rows. The join name may be customized if desired.

4. To return values for school names, add an export within the subquery. Select the Name export from the School direct exports.

5. In Row Separator, enter: , (make sure to enter a space to add spaces between the comma separated values)

6. Click Save.

   

The column now returns a comma separated list of school names, if there is more than one school attended.

Sample Output: Richard Milburn Academy - West, Cypress Charter High School

Aggregate

The Aggregate output returns values based on a specified function:

• Count

• Count Distinct

• Average

• Minimum

• Maximum

• Sum

In the Edit window of a new subquery part, select Aggregate as the Output.

Within the subquery, the joins with the multiple icon are used. “Multiple” indicates that more than one record can be returned by this join (i.e. a one-to-many relationship). This is required in the Aggregate subquery as multiple records are aggregated or grouped in this part to return a single value.

Example: Count of number of schools on the student record

1. On the Aggregate dropdown in the subquery part, select Count.

2. Click Join to add School data for the subquery, and select Schools. The join can be renamed if desired.

   

3. Click Save. It is not necessary to add exports within the subquery.

In this example, the No. of Schools column returns a count of how many schools there are on each student record.

Dictionary

Dictionary output is used with Liquid Markup (for example, in Deliver mailings, with Looping, or with portals, etc). Learn more about Dictionary here.

Example: Merge a list of missing application requirements in a mailing

1. In the Deliver mailing’s Application-based recipient list query, add a new subquery part.

2. Give the part a Name. It is recommended to use a concise, computer-friendly name as it will be referenced as a merge field when creating the contents of the message.

3. Select Dictionary in the Output dropdown.

4. To add checklist data, click Join. Select Checklists, which will return all checklist items per application in the same column.

5. In Add Export, add checklist exports that display the desired information that should be conveyed to applicants (e.g. Subject displays the name of the checklist item). Similar to the subquery name, the exports should be renamed to be computer friendly so they can be referenced later when creating the message.

6. Add a new Filter. A Status filter should be available under Checklist direct filters. This allows the subquery values to return only information on missing items.

   

7. Use Add Sort to sort the data returned based on the selected subquery exports.

   

8. Click Save.

When previewing the results of the query, the subquery column returns values in nested PKV tags.

In the Edit Message section of the Deliver mailing, the subquery part is now available as a merge field. The merge field can be additionally formatted as desired.

Existence

The Existence output returns values based on whether a related row or export value exists or does not exist.

In the Edit window of a new subquery part, select Existence as the Output. Enter the desired outputs for Value If Exists and Value If Not Exists.

Example: Existence of an Address

1. Select Existence as the output.

2. Click Join to add Address data for the subquery, and select Addresses. The join can be renamed if desired.

3. Enter the value that should be output if any address exists, and the value that should be output if no address exists:

   

4. Click Save. It is not necessary to add exports within the subquery, unless the intent is to output a value based on a specific field from the related table (e.g. adding an export for Addresses Street 2 will cause the Value If Exists to be output only when an address with a Street 2 value exists).

Split

The Split output may be used to separate out part of a string value based on a specified separator character.

Example: Exporting the “username” section of an organizational email address

1. Select Split as the output.

2. Under Exports, select the desired email address field and enter ‘@’ as the Export Separator with an Offset of 1:
   
   

3. Click Save.

In this example, the output is value 1 (i.e. the value to the left of the ‘@’ symbol).

Formula

Formulas may be used to perform mathematical calculations, write if/then statements, and otherwise manipulate data in the query builder.

In the Edit window of a new subquery part, select Formula as the Output.

Example: Date difference between prospect created date and application submitted date

1. Select Formula as the output.

2. Click Join to add Rank 1 Application data for the subquery, and select Application by Rank. The join can be renamed if desired.

3. Add exports for Rank 1 Application Submitted Date and Person Created Date

4. Type # in the Formula box to see a list of suggested formulas. Click Date Difference (day) to insert datediff(day, @..., @...) .

5. Type @ to display the list of available exports. Add @Rank-1-Application-Submitted-Date and @Person-Created-Date to the formula.

   

6. Click Save.

XML

A Subquery part can return nested XML elements for an XML web service.

Example: Web Services with Address Nodes

1. In the Edit Web Service settings of the query, select XML as the Service Type. Click Save.

2. In Exports, add a new Subquery part. The Name of the part will be returned in the output as an enclosing tag or node for nested elements.

3. Select XML as Output for the subquery.

4. Add a Join for Multiple: Address.

5. In Add Export, add direct exports as desired. These exports are the child elements for the nested address elements.

6. Edit or double-click each export to rename according to their corresponding tags.

   

7. Finally, in the Node setting within the subquery part, enter the tag name (in this example "address") of the element.

8. Click Save.

In this sample output, address is the nested element.

<Addresses> is a parent node. Each nested element within Addresses has an <address> tag.

Each <address> element has child elements for <type>, <city>, <country>, etc.

Example: XML Attributes

To define attributes for XML elements, follow steps above. However, an @ symbol must be added when renaming the exports.

Instead of nested elements, the address element now has attributes based on the selected exports.

JSON

A Subquery can also be used to return values in JSON format. The Exports represent name/value pairs.

Example: Addresses Array

1. In the Edit Web Service settings of the query, select JSON as the Service Type. Click Save.

2. Under Exports, add a new Subquery part. The Name of the part will be returned in the output as a property name.

3. Within the subquery, select JSON as Output.

4. Add a Join for Addresses. This returns all addresses associated with the record.

5. In Add Export, add direct exports as desired. These denote the properties for the address objects in the Addresses array.

6. Edit or double-click each export to set the property names.

7. Click Save.

Other Subquery Export Settings: Groups

Add a Group to a subquery export to group distinct values together.

Example: Total FY Giving by Fund

1. Select Concatenate as the Output.

2. Add a join from Person to Gifts and a join from Gifts to Funds.

3. Insert exports for Funds Name and Gifts Amount.

4. Click Group and select Funds Name from the list of exports.

5. Click Save.

   

Instead of returning a concatenated list of all a person's gifts, the grouping makes it possible to see the gift total by fund: