A subquery is an inner or nested query within a main or enclosing query. The Subquery part allows a column expression to be defined that can return different types of output:

• Concatenations

• Aggregates

• Dictionary

• Custom Ranked Values

• Existence Values

• Formulas

• Nested XML Elements

• JSON

Concatenate

Concatenation allows multiple values to be joined 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.)

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.

Rank

For Slate tables that store data where multiple rows or records may be related to the same record in another table (i.e. the “many” end of a one-to-many relationship such as School, Address, Decision, etc), triggers are used to 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 can specify 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

Schools are table-ranked based on a few attributes, e.g. Priority, Dates Attended.

This means that in a scenario where a student has, for example, received a Bachelors’ Degree from one institution, but afterwards took one course at another institution, the institution where the course was taken would rank higher overall than the degree-granting institution. An export or join specifying Rank 1 School would then return a mixed set where some schools had conferred the degree to the corresponding person record, while other schools had not.

“Rank 1 School” can be redefined to be more consistent within a query by using the Rank subquery part.

1. In the Edit Part window, select Rank on the Output dropdown.

2. Click Join. Select Schools.
   This join first returns all schools attended. The next settings then specify how multiple schools should be sorted, and finally, which single school should be returned based on a given rank value.

3. In Add Export, add the School Name direct export.

4. In Add Sort, add the direct export by which the schools should be custom-ranked. In this example, we want to rank schools based on date the degree was conferred (i.e. School Conferred Date).

5. Edit or double-click the Sort value. To order the schools from the most recent conferred date, select Descending in the Sort dropdown.

6. In Row Offset, enter 1.

7. Click Save.

   

To illustrate the custom ranking, the query results set above displays values from School Rank 1 and School Rank 2 based on the standard School table ranking in Slate. 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 Rank 1 School (by conferred date) column returns data from the degree-granting institution instead.

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

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: