Subquery Exports & Outputs

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: Return one segment of a string by choosing a separator and a one-based offset.

• 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.

✨ Stuck? Ask Slate AI for help!

Slate AI can help you decide the right output type and help you configure it to return the data you need.

⭐ Get Inspired

This article was adapted in part from a post by Technolutions staff in the Slate Community Forums' Get Inspired space. Have a great idea for a Get Inspired post? Let us know!

Example: Creating a subquery export

A common use case for subquery exports is stitching together multiple exports in a single column. Let’s say you want a single column in your query to return a student’s first and last names, their rank-1 high school, and that high school’s CEEB code.

We’ll start by making a Quick Query using Configurable Joins on the person base.

In a query, select the subquery icon next to Export to create a subquery export.

In the Edit Part menu, you can get to other data, like the student’s high school, by adding a join to another base. Here, we’ll join to School by Level of Study, Rank.

The join exists only in this subquery. If you instead want that school data to be accessible to every other query part, you can create a join at the base level. For more information, see Joins.

We select the output type, in this case Concatenate.

Next, select Export to add first name, last name, school name, and school code. Use Export Separator when the same delimiter, such as a comma or space, should appear between every export. Add Literal exports only for fixed text or position-specific punctuation, such as a label, colon, or parentheses.

When we save the subquery export, we can preview our results and see a single column of data that contains four exports worth of information. Your particular situation may call for other kinds of output.

Subquery copy/paste

Open the existing subquery export, select a blank area inside its dialog (not a text field), and press Ctrl+C in Windows or Command+C in macOS. Open a new subquery export using the same base and press Ctrl+V or Command+V inside its dialog to paste the internal query parts.

Pasting parts does not copy the source subquery's Name, Output, or separator settings. Set these in the destination before saving. For example, multiple exports pasted into the default Rank output produce a one-export warning. To combine those exports, select Concatenate, set the required Row Separator and Export Separator, and check Preview Results.

💡 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.

Row Limit

Some output types include a Row Limit option. This setting controls how many rows are returned from a joined table in the subquery export.

For example, joining to Addresses may return multiple rows per record — one for each address. To return only one address, set the Row Limit to 1.

Rank

The rank subquery export output pulls one row from a related table. A sort dictates the row that is pulled.

📝 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.

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.

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 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.

The final configuration is:

• Output Rank

• Row Offset 1

• Export Schools Name

• Join Schools

• Sort Schools Conferred Date in Descending order.

Use Preview Results to check the selected school.

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.

This example assumes that at least one school has a Conferred Date. Sorting does not exclude undated schools: if all dates are blank, a school can still be returned. Do not treat that result alone as proof that a degree was conferred. If dates tie, this date sort alone does not identify a unique school.

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.

Use the following settings to control concatenate output:

• Row Limit

• Row Separator

• Row Offset

• Export Separator

• Literal

Export Separator places the same delimiter between exports in one row. Row Separator separates values returned from multiple related rows. Use Literal exports for static text or separators that vary by position.

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

• Median

• Minimum

• Maximum

• Sum

• Standard Deviation

• Variance

For Sum and the other value-based functions, add one numeric export inside the subquery. Filters determine which rows are included. To calculate a subtotal for each category, also add a Group; see Groups.

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.

Ranking

Use the Ranking subquery export output to assign a row number, rank, dense rank, or distribution bucket to rows returned by a subquery. Ranking values are based on the sort you define in the subquery export.

Ranking outputs can help identify top donors, top event attendees, leaderboard positions, and segmented groups based on values such as lifetime giving, fiscal year giving, or event registration counts.

Ranking functions

📝 Note

Ranking depends on the sort order in the subquery export. Confirm the selected sort before using ranking values for reporting, segmentation, or automation.

Try a Slate example 💼

Copy this Suitcase ID and paste it in Database → Suitcase Import to import a pre-made example query:

9725b079-59bd-4bda-94ac-36a520bdc6ac:slate-advancement-showcase

Configuring a ranking output

1. In a query, add a Subquery Export.

2. From the Output list, select Ranking.

3. From the Function list, select Row Number, Rank, Dense Rank, or Distribution.

4. Add the exports and joins that define the rows to rank.

5. Add the sort that determines the ranking order.

6. If you select Distribution, enter an interval. For example, enter 4 for quartiles or 10 for deciles.

7. Select Save.

The following example configures a Dense Rank function.

The Distribution function uses the interval to assign each row to a segment. An interval of 4, for example, creates four quartile groups based on the selected sort; name the export accordingly.

When records have the same lifetime giving value, Rank and Dense Rank both assign the same value to tied rows. Rank then skips the next position, while Dense Rank continues with the next number.

Distribution groups rows rather than preserving ties as one rank: equal sort values can fall into different groups. Note that a limited preview may show only the first group.

Dictionary

Dictionary output is used with Liquid Markup, like, for example, in Deliver mailings, with Liquid looping, or with portals.

📖 The For Tag and Liquid Looping

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.

Nesting dictionary exports

You can create a dictionary subquery export inside another dictionary subquery export—in fact, you can go to as many layers of depth as you need.

Say you want to see a list of applications, and, for each application, you want to see its associated decision codes. You can do this by:

1. Creating a dictionary subquery that joins to Applications.

2. This dictionary subquery export has two exports of its own: a GUID and another dictionary subquery export.

3. The nested dictionary subquery export joins to Decisions and returns an export for decision codes.

In a mailing or portal, you can access each of these layers with nested Liquid for loops:

<body>
  {% for app in apps %}
  <div>
    App GUID: {{app.guid}}
    <ul>
      {% for decision in app.decisions %}
      <li>
        Decision Code: {{decision.code}}
      </li>
      {% endfor %}
    </ul>
  </div>
  {% endfor %}
</body>

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.

⚠️ Existence outputs are text

Numeric values, such as 1 and 0, are returned as text. Adding raw Existence exports in a Formula can concatenate them, so that 1 + 1 returns 11 instead of 2. To count flags, convert each export to an integer in the formula, for example convert(int, @Flag-A) + convert(int, @Flag-B). Preview both true and false cases before using the result in a filter.

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. Enter @ as the Export Separator. Use Offset 1 for the username or 2 for the domain.

3. Click Save.

Offsets are one-based. In this example, Offset 1 returns the value to the left of @. Offset 2 returns the value to the right, such as email.com.

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.

📖 Learn more about formulas in Slate: Calculated Fields in Forms

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.

5. Click Date Difference (day).

6. Insert datediff(day, @..., @...).

7. Type @ to display the list of available exports.

8. Use the created date as the first date argument and the submitted date as the second: datediff(day, @Person-Created-Date, @Rank-1-Application-Submitted-Date). Use the export names shown by your query's autocomplete if you renamed the join or exports.

   The result counts day boundaries from prospect creation to submission. A same-day submission returns 0; a missing submitted date returns no value. A submitted date earlier than the created date returns a negative result.

9. Click Save.

Using a subquery output with a Translation Code

A subquery can shape or select a value before an outer export passes that value through a Translation Code. For example, a Rank subquery can return one application major and the outer export can translate that major.

1. Select Subquery Export.

2. Add the direct export that supplies the translation key.

3. Configure an output that returns the intended value. For example, select Rank with Row Offset 1 to return one value. Name and save the subquery.

4. Edit the outer export. Set Export Value to Translation Code, then select the Translation Key and Translation Value.

5. Save and select Preview Results. Confirm both mapped values and the translation default for an unmapped value.

💡 Field Width is applied after translation

Do not use the outer export's Field Width to shorten the lookup key. If the key must be split or otherwise reformatted, do that inside the subquery before the outer Translation Code is applied.

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 JSON subquery returns its exports as properties in an array of objects. The subquery part name becomes the property name in the enclosing JSON web-service output, and each export name becomes a property inside each array item.

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 JSON property names. Use stable, API-appropriate names without spaces when another system will consume the output.

7. Click Save.

For a subquery named Addresses with exports named type, city, and country, the enclosing JSON contains an array in this shape:

{"Addresses":[{"type":"Home","city":"New Haven","country":"US"},{"type":"Mailing","city":"Boston","country":"US"}]}

Preview or call the web service and confirm the property names, null handling, and array cardinality before another system depends on the payload.

Groups

Add a Group to return one result for each distinct value. When another export is aggregated, the Group defines the category for each subtotal.

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. Edit Gifts Amount and set Aggregate to Sum. To display currency, set its Format Type to Money. The Sum setting is inside the amount export; it is not visible in the outer configuration shown below.

4. Select Group and choose Funds Name. For the pictured fiscal-year example, filter Gifts / Date to on or after 07/01/2019 and before 07/01/2020, and set Gifts / Reversal/Correction to Not Reversal/Correction Entry. The image uses a - Literal between fund name and amount and a | Row Separator between funds. Choose your institution's reporting period and credit/status scope consistently.

5. Click Save.

   

The Funds Name group and the Sum applied to Gifts Amount return one subtotal for each fund instead of one entry for each gift. Select Preview Results and check the included gifts and subtotals.

If you also export an overall giving total, use the same date, credit, payment/status, and reversal filters for that total and the grouped subtotals before comparing them. Totals with different scopes are not directly comparable.