Function

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

Creates a value of type QueryFilter for use with a!pickerFieldRecords when defining filter options for expression-backed records, filtering a a!queryRecordType() or a!queryEntity() function call before any grouping or aggregation is computed, or using the a!recordData() function to define additional filters when referencing a set of records from a record type.

See also: a!queryRecordType(), a!recordData(), a!relatedRecordData(), a!query(), a!recordFilterListOption(), queryfilter

Parameters

Returns

QueryFilter

Usage considerations

Filter record data and related record data

You can filter record data and related record data in:

• Records-powered charts
• Records-powered grids
• 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

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"
        )
      }
    )

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

You can also filter related record data from a one-to-many relationship using the a!relatedRecordData() function.

When applying a filter to the a!relatedRecordData() function, you must reference record fields or related record fields from the related record type specified in the relationship parameter of the function.

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

"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

Filter by multiple fields from a one-to-many relationship

When you filter by multiple related record fields from the same 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 or different related records that meet each filter condition.

For example, let's say you want to return all customers that have at least one active order that has a total cost over $100. In this scenario, customers have a one-to-many relationship with orders.

If you created a query like the one below, it would return any customers that have at least one order that is both active and has a total cost of over $100; it would also return any customers that have at least one active order and at least one other order that has a total cost of over $100.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
a!queryRecordType(
  recordType: recordType!Customer,
  filters: a!queryLogicalExpression(
    operator: "AND",
    filters: {
      a!queryFilter(
        field: recordType!Customer.relationships.orders.fields.isActive,
        operator: "=",
        value: true
      ),
      a!queryFilter(
        field: recordType!Customer.relationships.orders.fields.totalCost,
        operator: ">",
        value: 100
      )
    }
  )
)

Instead, to only return customers that have at least one active order costing over $100, we would recommend creating a new custom record field on the Order record type (the "many" side of the relationship) that combines the fields you are filtering. Then, you can filter your query based on the custom record field.

In this example, you'd create a new custom record field on the Order record type using the Write Your Own Expression template. You would then enter the following expression:

1
2
3
4
5
if(
 and(rv!record[recordType!Order.fields.isActive], rv!record[recordType!Order.fields.totalCost] > 100)
 true,
 false
)

This way, the custom record field will evaluate whether an order meets both conditions (true) or not (false). For example, if Order 1 has isActive = true and totalCost: 150, the custom record field would be true.

Once you have your new field, you can create a filter on the new custom record field. In this example, you could use the expression below to only query customers that have at least one active order that is over $100.

1
2
3
4
5
6
7
8
9
10
11
a!queryRecordType(
  recordType: recordType!Customer,
  filters: {
  /* Filter on the new custom record field on the Order record type */
    a!queryFilter(
      field: recordType!Customer.relationships.orders.fields.isActiveAndCostly,
      operator: "=",
      value: true
    )
  }
)

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

1
2
3
4
5
	=a!queryFilter(
	  field: recordType!Case.fields.status,
	  operator: "=",
	  value: "active"
	)

See Query Recipes for examples of filtering data from a data store entity.