a!queryFilter() Function

Creates a value of type QueryFilter for use with a!pickerFieldRecords(), a!queryRecordType(), a!recordData(), a!relatedRecordData(), a!recordFilterListOption(), or a!query().

Related functions:

• a!queryRecordType(): Filter record data in queries.
• a!recordData(): Filter record data in grids and charts.
• a!relatedRecordData(): Filter related record data.
• a!recordFilterListOption(): Define user filter options on record lists.
• a!query(): Filter data from a data store entity.

Parameters

a!queryFilter( field, operator, value, applyWhen )

Returns

QueryFilter

Using the function

The following sections cover important details about the a!queryFilter() function.

Using the operator parameter

The following tables define the behavior of each operator and their compatibility with Appian data types.

Comparison and logic operators:

String matching operators:

AI-powered and full-text search operators:

See where to use these search operators.

Operator compatibility by data type: The following table shows which operators can be applied to each data type.

The in operator versus the includes operator

The in operator accepts an array of values and returns records where the field matches at least one of those values as a complete phrase. For example, filtering with {"Retail", "Real Estate"} only returns records where the field value is "Retail" or "Real Estate" in full.

The includes operator accepts a single value and returns records that contain any portion of that value. For example, filtering with "R" returns any record where the field contains the letter "R" anywhere in the value.

Use in when you know the specific values to match. Use includes when you want to find records based on a partial text match.

Filter record data

You can filter record data in:

• Records-powered charts
• Records-powered grids
• User filters
• a!queryRecordType()
• a!measure()

Filter record data in a chart or grid

When applying a filter to a records-powered chart or grid, the filter must reference record fields or related record fields from the record type specified in the recordType parameter of a!recordData().

For example, if a!recordData() references the Employee record type, the filter must reference a field starting from recordType!Employee:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
  a!pieChartField(
    data: a!recordData(
!     recordType: recordType!Employee,
      filters: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
!           field: recordType!Employee.relationships.employeeStatus.fields.isActive,
            operator: "=",
            value: true
          )
        },
        ignoreFiltersWithEmptyValues: true
      )
    ...

Filter record data in a query

When applying a filter in a!queryRecordType(), the filter must reference record fields or related record fields from the record type specified in the recordType parameter.

For example, if you are querying the Employee record type, the filter must reference a field starting from recordType!Employee:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
a!queryRecordType(
! recordType: recordType!Employee,
  fields: { 
     recordType!Employee.fields.fullName,
     recordType!Employee.fields.username,
     recordType!Employee.fields.officeNumber
  },
  filters: a!queryFilter(
!   field: recordType!Employee.fields.isRemote,
    operator: "=",
    value: false
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1, batchSize: 400
  )
).data

Filter record data in a!measure

When using the a!measure() function, you can add filters to control which values are included in the calculation. The field reference in the filter must start from the record type being aggregated.

For example, let's say you have a Customer record type that has a one-to-many relationship with the Order record type. To calculate the number of orders for each customer that include the order item "Printer", the filter references a field starting from recordType!Order because that is the record type being aggregated in the field parameter:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  a!measure(
    function: "COUNT",
!   field: recordType!Customer.relationships.orders.fields.id,
    alias: "count_of_id",
    /* Since the Order record type is the related record type used in the field parameter, 
    use that record type reference in the filter. */
    filters: {
      a!queryFilter(
!       field: recordType!Order.relationships.orderItem.fields.itemName,
        operator: "=",
        value: "Printer"
      )
    }
  )

Case sensitivity on synced record types

When filtering a synced record type with a!queryFilter(), filter values are case sensitive for the = and <> operators. This applies whether the filter is in an a!queryRecordType() expression, a records-powered chart, or a records-powered grid.

For example, if you want to return cases with a status of "Open", the filter value must match that exact capitalization. Setting the value to "open" (lowercase) will not return the expected results:

1
2
3
4
5
6
7
8
9
10
a!queryRecordType(
  recordType: recordType!Case,
  fields: recordType!Case.fields.title,
  filters: a!queryFilter(
    field: recordType!Case.relationships.status.fields.value,
    operator: "=",
    value: "Open"
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 500)
)

All other operators, like in, starts with, and not in, are not case sensitive.

Search record data

You can use search operators in a!queryFilter() to find records based on semantic meaning or full text matches.

The following search operators are available:

See an example using smart search in a!queryFilter.

Combining semantic and lexical search

You can apply both the "semantic search" and "lexical search" operators in the same query. When a row matches both operators, it appears only once in the results. Appian uses the highest score as the primary similarity score for that row.

Because lexical scores are always greater than 1 and semantic scores are always less than 1, lexical matches always produce the higher score. If you sort by the primary similarity score in descending order, rows with lexical matches appear above rows that only matched semantically.

See an example using both semantic and lexical search in a!queryFilter.

"At least one" filter behavior

When you filter by a related record field from a one-to-many relationship, the query returns all base records that have at least one related record matching the filter condition. It does not require all related records to match.

How it works

When you filter on a field from the "many" side of a one-to-many relationship, the query checks each base record to see if any of its related records satisfy the condition. If at least one does, the base record is included in the results.

For example, consider an Order record type with a one-to-many relationship to an Order Item record type. A single order can have many order items, some purchased with a promo code and some without. If you filter by appliedPromoCode = true, the query returns every order that has at least one item with a promo code, even if other items on the same order do not have a promo code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
a!queryRecordType(
  recordType: recordType!Order,
  fields: a!aggregationFields(
    groupings: a!grouping(
      field: recordType!Order.fields.orderDate,
      interval: "YEAR",
      alias: "orderDate"
    ),
    measures: a!measure(
      field: recordType!Order.fields.orderId,
      function: "COUNT",
      alias: "ordersWithPromos",
      /* Only count orders that have at least one item
      purchased with a promo code */
      filters: a!queryFilter(
        field: recordType!Order.relationships.orderItems.fields.appliedPromoCode,
        operator: "=",
        value: true
      )
    )
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1, batchSize: 100
  )
).data

When this behavior applies

The "at least one" filter behavior applies whenever the relationship path between the record type you're querying and the field you're filtering on passes through a one-to-many relationship. This is true regardless of how many relationships are in the path, or whether the final relationship is many-to-one.

The rule is if there is any one-to-many relationship in the path from the queried record type to the filtered field, the filter uses "at least one" matching.

For example, suppose you have three record types with the following relationships:

Order ---< Order Item >--- Promo Code

Order has a one-to-many relationship with Order Item, and Order Item has a many-to-one relationship with Promo Code. If you query the Order record type and filter on a Promo Code field, the "at least one" behavior still applies because the path passes through the one-to-many relationship between Order and Order Item. The query returns every order that has at least one order item linked to a matching promo code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
a!queryRecordType(
  recordType: recordType!Order,
  fields: a!aggregationFields(
    groupings: a!grouping(
      field: recordType!Order.fields.orderDate,
      interval: "YEAR",
      alias: "orderDate"
    ),
    measures: a!measure(
      field: recordType!Order.fields.orderId,
      function: "COUNT",
      alias: "ordersWithPromos",
      /* Only count orders that have at least one item
      purchased with a promo code */
      filters: a!queryFilter(
        field: recordType!Order.relationships.orderItems.relationships.promoCodes.fields.codeName,
        operator: "<>",
        value: "No Promo Code"
      )
    )
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1, batchSize: 100
  )
).data

Filtering where all related records must match

By default, filtering on a one-to-many relationship uses "at least one" matching: a base record is included if any of its related records meet the condition. If you need all related records to meet the condition instead, use the "AND_ALL" operator in a!queryLogicalExpression().

The "AND_ALL" operator is the recommended approach when filtering by multiple fields from the same one-to-many relationship.

Filter which related records appear in results

You can use a!queryFilter() in the a!relatedRecordData() function to control which related records are included in the output for each base record.

When a query or grid returns data from a one-to-many relationship, up to 10 related records are returned per base record. With a!relatedRecordData(), you can configure a!queryFilter() to filter or apply smart search on the related record set.

Using a relationship reference in the field parameter

You can use a record type relationship reference in the field parameter when the operator is set to "is null" or "not null". This allows you to only return records that do or do not have any related records.

For example, the following query will only return employees that are assigned to a team.

1
2
3
4
5
6
7
8
9
10
a!queryRecordType(
  recordType: recordType!Employee,
  filters: a!queryFilter(
    field: recordType!Employee.relationships.teams,
    operator: "not null"
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1, batchSize: 100
  )
).data

Examples

The following examples demonstrate the a!queryFilter() function.

These examples reference record types from another environment and will error if you try to use them in yours. Use them as a reference only.

See Recipes for Querying Records for more examples filtering data from a record type or from a data store entity.

Return cases created this year

The following query returns all cases created at the beginning of the year until today's date.

For example, if today's date was March 24, 2020, then the query would return all cases created on January 1, 2020 until March 24, 2020.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
a!queryRecordType(
  recordType: recordType!Case,
  fields: {
    recordType!Case.fields.title,
    recordType!Case.fields.assignedEngineer,
    recordType!Case.relationships.priority.fields.value
  },
  filters: 
    a!queryFilter(
      field: recordType!Case.fields.createdOn,
      operator: "between",
      /* Year to Date */
      value: { date(year(today()), 1, 1), today() }
      ),
  pagingInfo: a!pagingInfo(
    startIndex: 1, 
    batchSize: 100)
).data

Return cases for customers in specific industries

The following query uses the in operator to return all cases for customers who are in either the Retail or Real Estate industry.

The in operator returns records that match the full value of at least one item in the array. See The in operator versus the includes operator for more on how this differs from the includes operator.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
a!queryRecordType(
  recordType: recordType!Case,
  fields: {
    recordType!Case.fields.title,
    recordType!Case.fields.assignedEngineer,
    recordType!Case.relationships.priority.fields.value
  },
  filters: a!queryFilter(
    field: recordType!Case.relationships.customer.relationships.industry.fields.label,
    operator: "in",
    value: { "Retail", "Real Estate" }
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
).data

Return cases using a partial text match

The following query uses the includes operator to return any cases for customers who have an R in the industry name. Given four industries—Retail, Real Estate, Healthcare, and Hotels—the query returns cases for customers in Retail, Real Estate, and Healthcare since they all contain the letter R.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
a!queryRecordType(
  recordType: recordType!Case,
  fields: {
    recordType!Case.fields.title,
    recordType!Case.fields.assignedEngineer,
    recordType!Case.relationships.priority.fields.value
  },
  filters: a!queryFilter(
        field: recordType!Case.relationships.customer.relationships.industry.fields.label,
        operator: "includes",
        value: "R"
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
).data

Return all open and in progress cases

The following query returns all cases that have a status of "Open" or "In progress". Since there are three total status: Open, In Progress, or Closed, you can use the <> operator to return any cases that do not equal "Closed".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
a!queryRecordType(
  recordType: recordType!Case,
  fields: {
    recordType!Case.fields.title,
    recordType!Case.fields.assignedEngineer,
    recordType!Case.relationships.priority.fields.value
  },
  filters: a!queryFilter(
    field: recordType!Case.relationships.status.fields.value,
    operator: "<>",
    value: "Closed"
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
).data

Return cases based on smart search

The following example illustrates how to filter an a!queryRecordType() expression using smart search and return the search results in the query output.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
a!queryRecordType(
  recordType: recordType!Case,
  fields: {
    recordType!Case.fields.id,
    recordType!Case.fields.title,
    recordType!Case.fields.description,
   /* Returns similarityScore and sourceContent for the title field */
    recordType!Case.searchResults.title,
   /* Returns similarityScore and sourceContent for the description field */
    recordType!Case.searchResults.description,
    /* Returns similarityScore, sourceContent and sourceField for the field with the best semantic match */
    /* In this case, could be title or description, because we are searching those two fields */
    recordType!Case.searchResults.allSearchFields,
  },
  /* Return cases with a semantic match to "software update" */
  /* in either the title or description field  */
  filters: a!queryLogicalExpression(
    operator: "OR",
    filters: {
      a!queryFilter(
        field: recordType!Case.fields.title,
        operator: "semantic search",
        value: "software update"
      ),
      a!queryFilter(
        field: recordType!Case.fields.description,
        operator: "semantic search",
        value: "software update"
      ),
    },
    ignoreFiltersWithEmptyValues: true
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 2,
    sort: {
      /*Sort by similarity score so the best matches are returned at the top*/
      a!sortInfo(
        field: recordType!Case.searchResults.allSearchFields.similarityScore,
        ascending: false
      )
    }
  )
)

Sample output from the query above:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
data (List of Case)
Case
  id: 2
  title: "Software update failure"
  description: "Attempted to update ERP system, update failed midway. Rolled back to previous version."
  searchResults.allSearchFields
    similarityScore: 0.8
    sourceContent: "Attempted to update ERP system, update failed midway. Rolled back to previous version."
    sourceField: description
  searchResults.title
    similarityScore: null
    sourceContent: null
  searchResults.description
    similarityScore: 0.8
    sourceContent: "Attempted to update ERP system, update failed midway. Rolled back to previous version."
Case
  id: 44
  title: "Application errors after update"
  description: "Shortly after applying a software update to one of our internal web apps, users started reporting errors when accessing certain features. We've had to roll back the update but some issues persist."
  searchResults.allSearchFields
    similarityScore: 0.5
    sourceContent: "Shortly after applying a software update to one of our internal web apps, users started reporting errors when accessing certain features. We've had to roll back the update but some issues persist."
  searchResults.title
    similarityScore: null
    sourceContent: null
  searchResults.description
    similarityScore: 0.5
    sourceContent: "Shortly after applying a software update to one of our internal web apps, users started reporting errors when accessing certain features. We've had to roll back the update but some issues persist."

Feature compatibility

Feedback