- 18 Dec 2024
- 1 minute read
- Print
- DarkLight
- PDF
Filtering on Free-Text Fields
- Updated 18 Dec 2024
- 1 minute read
- Print
- DarkLight
- PDF
Filters are typically not used for free text fields, since they can store unlimited variations of a single response. Accordingly, filters for text-driven fields are not automatically generated. In most cases, only exports are needed for free text fields. However, there may be occasions when querying for a specific text string is required. This can be accomplished using Subquery Filters in a configurable joins query.
Using Formula (Aggregate)
One approach is to use a "Formula" Aggregate filter. Several versions of this technique are provided in the following examples
Organization Name is
This places the exact search string in the Formula.
Last Name Contains
This uses the LIKE operator, and can be used with or without a wildcard (%), to find specific text. In this example, the query matches records whose last name contains "Sam."
Last Name Starts with
Like the previous example, this searches for the last name that contains “Sam,” but because it uses the % wildcard at the end, it matches records whose last name start with “Sam” (and might include and other text afterward).
Last Name Starts with Letters A-F
This formula specifies a range of letters in square braces ([ ]), in this case, “A” through “F.”
Using Comparison (Aggregate)
Organization Name is
This technique uses a "Comparison" Aggregate filter:
In this example, a literal export was added to check against a specific school name ("An Example School").