The Sequential Value Groups join lets you query for a consecutive span of values across related records. The common Advancement example is consecutive years of giving, but the join is not limited to gifts. You can use it anywhere a query can evaluate related rows that produce values such as 2022, 2023, and 2024, or 1, 2, and 3.
Use Sequential Value Groups when you need to answer questions like:
Which records have related activity in at least three consecutive years?
What is the first and last value in a consecutive span?
How long is the longest consecutive span returned by the related records?
How Sequential Value Groups work
The join evaluates one exported value from a set of related records, identifies values that appear in sequence, and exposes details about that sequence. For example, if a person has related rows with the values 2020, 2021, 2022, and 2024, the consecutive span is 2020 through 2022. The value 2024 starts a separate span because 2023 is missing.
The Sequential Value Groups join can return these values:
Count: The number of values in the consecutive span.
Min: The first value in the consecutive span.
Max: The last value in the consecutive span.
📝 Note
The sequence value should sort in the same order that you want Slate to evaluate it. For yearly sequences, format a date as a four-digit year, such as
yyyy. For other sequences, use a value that increments predictably, such as1,2, and3.
Choosing a sequence value
Before adding the join, decide which related records should be evaluated and which value from those records represents the sequence.
Question | Related records | Sequence value |
|---|---|---|
Has this person given in consecutive fiscal years? | Gifts | Gift fiscal year or gift date formatted as |
Has this applicant submitted applications in consecutive entry years? | Applications | Application entry year |
Has this student enrolled in consecutive terms? | Enrollments or term records | A term sequence number or another numeric term order value |
Has this record completed consecutive stages in a process? | Process, checklist, or workflow-related records | A stage order value, such as |
Has this person attended an annual event in consecutive years? | Form responses, events, or registrations | Event date formatted as |
The examples above are patterns, not required bases. Use the base and joins that match the records in your database.
Creating a sequential value filter
Add a Sequential Value Groups subquery filter when you want the query to return only records that meet a sequence requirement, such as at least three consecutive years or stages.
Create or open a Configurable Joins query.
In the Filters section, select Subquery Filter.
Give the subquery filter a clear name, such as
Consecutive Giving YearsorConsecutive Enrollment Terms.Select Join.
Select Sequential Value Groups, then select Continue.
Within the Sequential Value Groups join, select Join.
Select the join that contains the related records to evaluate. For example, select Gifts for consecutive giving years, Applications for consecutive application years, or the relevant dataset or form-response join for other sequence patterns.
Add any filters needed to limit the related rows before the sequence is evaluated. For example, you might limit gifts by credit type, applications by submitted status, or form responses by registration status.
Select Export.
Select the value that defines the sequence. For a date-based yearly sequence, select the date export and format it with the Format Mask
yyyy.Save the nested join and subquery filter.
Configure the filter criteria for the sequence requirement you need. For example, use the sequence count to return records with at least five consecutive values.
Exporting sequence details
Add a Sequential Value Groups subquery export when you want the query output to show details about the consecutive span.
In the Exports section, select Subquery Export.
Build the same Sequential Value Groups join pattern used for the filter: add Sequential Value Groups, join to the related records, filter the related rows as needed, and export the value that defines the sequence.
In the Sequential Value Groups section, select the sequence values to return:
Count to show the length of the consecutive span.
Min to show the first value in the span.
Max to show the last value in the span.
If you want all three values in one column, set Output to Concatenate and add literal text between the values.
Give the subquery export a clear name, then select Save.
Example export output
For a consecutive years of giving query, a concatenated export might return:
Consecutive years: 5 | Start: 2020 | End: 2024For a consecutive term enrollment query, the same pattern might return:
Consecutive terms: 3 | Start: 12 | End: 14Using the join across lifecycles
Sequential Value Groups can support any lifecycle when the related records can produce a meaningful sequence value.
Admissions & Enrollment: Query for applicants who applied, attended an annual event, or completed a recurring process in consecutive years.
Student Success: Query for students with consecutive enrollment terms, consecutive advising periods, or consecutive completed milestones when those records include a usable sequence value.
Advancement: Query for constituents with consecutive giving years, consecutive years of engagement, or consecutive pledge-payment years.
Operations: Query for records with consecutive workflow steps, recurring yearly forms, or process stages that are stored with sequential values.
Choosing between Sequential Value Groups and Group By
Use Sequential Value Groups when the order and continuity of values matters. Use Group By when you only need totals, counts, or sums for each distinct value.
Use this | When you need to know | Example |
|---|---|---|
Sequential Value Groups | Whether values are consecutive, how long the span is, and where the span starts and ends. | Which donors have at least five consecutive years of giving? |
Group By | How many rows or how much value exists for each distinct group. | How much did each donor give in each year? |