a!queryFilter() Function

Function

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

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

Parameters

Returns

QueryFilter

Usage considerations

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, you must reference record fields or related record fields from the record type specified in a!recordData().

For example, lets say you want to create a pie chart to show the number of employees per department, and you want to filter by employee status. Since a!recordData() references the Employee record type in the recordType parameter, 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 to a query, you must reference record fields or related record fields from the recordType parameter of the a!queryRecordType() function.

For example, if you're querying data from the Employee record type, your 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

Use the search operator with a!queryRecordType()

The search operator implements a smart search on the record field specified in a!queryFilter. This allows you to return the data you need, even if you're not sure of the exact terms.

This is a specialized operator designed to work exclusively with the a!queryRecordType() function and the a!gridField component. It cannot be used with other functions or combined with other operators, and should not be used on fields from a many-to-one relationship.

To use this operator in a!queryFilter(), the field must have smart search enabled.

Here is an example expression with the search operator:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
a!queryRecordType(
 recordType: recordType!Case,
 fields: recordType!Case.fields.description,
 filters: 
   a!queryFilter(
    field: recordType!Case.fields.description,
+    operator: "search",
+    value: "power outage"
   ),
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 100,
    sort: {
      a!sortInfo(
        field: recordType!Case.searchResults.allSearchFields.similarityScore,
        ascending: false
      )
    }
  )
)

Filter record data in a!measure

When your record type has data sync enabled, you can add filters to the a!measure() function to determine which values are calculated.

You can filter on a record field or a related record field, and the field reference must start from the aggregated record type.

For example, you have a Customer record type that has a relationship with the Order record type. In a report, you want to calculate the number of orders for each customer that include the order item "Printer". Since you're aggregating on the Order record type, the field reference in the filter must start from recordType!Order.

The a!measure() function would look something like this:

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, 
    we'll use that record type reference in the filter.*/
    filters: {
      a!queryFilter(
!     field: recordType!Order.relationships.orderItem.fields.itemName,
      operator: "=",
      value: "Printer"
        )
      }
    )

Case sensitivity on record types with data sync enabled

If you are querying a record type with data sync enabled—whether it's in an a!queryRecordType expression, records-powered chart, or records-powered grid—note that filter values are case sensitive when using the = or <> operators.

For example, if you only want to return cases that have a status of "Open", your filter value must also be capitalized. If you set the filter value to "open" (lowercase), the filter 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.

"At least one" filter behavior

When you filter by a related record field from a one-to-many relationship (i.e., the "many" side of the relationship), the filter will return all records from the base record type (the "one" side of the relationship) that have at least one related record that meets the filter condition.

For example, let's say you have the Order record type and the Order Item record type, and you want to know the number of orders that have at least one order item purchased with a promo code.

Since there is a one-to-many relationship between Order and Order Item (one order can have many items purchased with or without a promo code), you can create a query to count all orders that have at least one order item has appliedPromoCode = true.

The expression would look something like this:

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

This "at least one" behavior applies when there is a one-to-many relationship in the path between where you are selecting data or aggregating data, and where you are filtering.

In this example, we are aggregating on the Order record type, and filtering on the Order Item record type (Order ---< Order Item), which is why we observe the "at least one" behavior.

Now, let's say we change our example so promo codes are stored in the Promo Code record type, which has a many-to-one relationship with the Order Items record type (Order Item >--- Promo Code).

If you change the above expression to filter on the Promo Code record type, you would still observe the "at least one" behavior because there is a one-to-many relationship in the path between Orders (where you are aggregating) and Promo Codes (where you are filtering). The path looks like this Orders ---< Order Item >--- Promo Code.

The expression would look something like this:

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

See a!queryLogicalExpression() to learn how to filter by multiple fields from the same one-to-many relationship using the "AND_ALL" operator. This is the recommended operator to use when filtering by fields from the same one-to-many relationship.

Filter related record data returned from a one-to-many relationship

When a query or grid returns data from a one-to-many relationship, up to 10 related records will be returned. You can filter the related records returned from a one-to-many relationship using the a!relatedRecordData() function.

See Filtering and sorting the related record set for more information.

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

Using the operator parameter

The following tables explains the behavior of each available operator:

The following table shows which operators can be applied to each data type.

Examples

The following examples illustrate how to filter data in a!queryRecordType() for a sample Case record type.

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

Record type references are specific to each environment. If you copy and paste these examples into your interface, they will not evaluate. Use them as a reference only.

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 a query 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
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,
  },
  filters: {
    a!queryFilter(
     field: recordType!Case.fields.title
     operator: "search",
     value: "software update"
   ),
    a!queryFilter(
     field: recordType!Case.fields.description
     operator: "search",
     value: "software update"
   ),
  },
  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