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().
| Keyword | Type | Description |
|---|---|---|
|
|
Any Type |
The name of the field or relationship to be filtered. When filtering record data, use the |
|
|
Text |
Filter operator to apply to the data. Valid values: |
|
|
Any Type |
The value to compare to the given field using the given operator. Can be entered as an expression. Optional if the operator value is |
|
|
Boolean |
Determines whether the filter is applied on the query. When set to false, the filter is not evaluated. Default: true. |
You can filter record data in:
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
)
....
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
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"
)
}
)
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
a!queryRecordType(
recordType: recordType!Customer,
filters: a!queryFilter(
field: recordType!Customer.fields.status,
operator: "=".
value: "Open"
),
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 500)
)
All other operators, like in, starts with, and not in are not case sensitive.
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.
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.
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
Note: You can only use a relationship reference to filter your data when configure the filter in expression mode. You cannot filter by a relationship reference in the query editor, or in design mode from any records-powered component.
The following tables explains the behavior of each available operator:
| Operator | Description |
|---|---|
= |
Equal to |
<> |
Not equal to |
< |
Less than |
> |
Greater than |
<= |
Less than or equal |
>= |
Greater than or equal |
in |
Matches a value in a list of values |
not in |
Does not match a value in a list of values |
starts with |
Begins with |
not starts with |
Does not begin with |
ends with |
Ends with |
not ends with |
Does not end with |
includes |
Includes the specified character(s) |
not includes |
Does not include the specified character(s) |
is null |
Is null |
not null |
Is not null |
between |
Is greater than or equal to a minimum value and less than or equal to a maximum value |
Tip: The between operator requires a list of two values, with the lower value as the first item and the upper value as the second. For example: {10,20}. You can set up one rule input or local variable that contains a list of two values, or create a list of two values in expression mode.
Tip: Some databases can store empty text values as either null or as a zero-length string (usually described as ""). When querying against the Cloud Database using the is null operator on a text field, the result set will only return results that are explicitly set to null. In contrast, other databases may treat these zero-length strings as equivalent to null and thus return results for both null and empty values. Appian expressions do not distinguish between null and empty regardless of the underlying database. This means that when data is synced from a text field, values that were null or empty are both stored as null in the synced record type. Thus, when querying from a synced record type, the is null operator will then return values that were both null and empty in the database.
See Query Recipes for an example of how to use a!queryFilter() and a!queryLogicalExpression to query for both empty and null values.
The following table shows which operators can be applied to each data type.
| Data Type | Valid Operators |
|---|---|
| Text | =, <>, in, not in, starts with, not starts with, ends with, not ends with, includes, not includes, is null, not null |
| Integer, Float, Time | =, <>, >, >=, <, <=, between, in, not in, is null, not null |
| Date, Date and Time | =, <>, >, >=, <, <=, between, in, not in, is null, not null |
| Boolean | =, <>, in, not in, is null, not null |
The following examples illustrate how to filter data in a!queryRecordType() for a sample Case record type.
See Query Recipes for more examples filtering data from a record type or from a data store entity.
Record type object references are specific to each environment. If you copy and paste these examples into your interface, they will not evaluate. Use them as a references only.
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
a!queryRecordType(
recordType: recordType!Case,
filters:
a!queryFilter(
field: recordType!Case.fields.createdon,
operator: "between",
value: /* Year-to-Date */{ date(year(today()), 1, 1), today() }
),
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
).data
Tip: If you use the query editor to create your query, you can select from a list of Date Presets to easily choose the time frame you want to filter by.
The following query returns all cases for customers who are in either the Retail or Real Estate industry.
1
2
3
4
5
6
7
8
9
a!queryRecordType(
recordType: recordType!Case,
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
The in operator allows you to provide an array of values, so only records that match one of the provided values are returned. This operator looks for exact matches only. This is different from the includes operator, which does not accept an array of values, and will return any records that contain any portion of the provided value.
For example, the following query would return any cases for customers who have an R in the industry name.
1
2
3
4
5
6
7
8
9
a!queryRecordType(
recordType: recordType!Case,
filters: a!queryFilter(
field: recordType!Case.relationships.customer.relationships.industry.fields.label,
operator: "includes",
value: "R"
),
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
).data
This means that if there are four types of industries: Retail, Real Estate, Healthcare, and Hotels, the query would only return cases for customers in the Retail, Real Estate, and Healthcare industries since they all contain the letter R.
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
a!queryRecordType(
recordType: recordType!Case,
filters: a!queryFilter(
field: recordType!Case.fields.status,
operator: "<>",
value: "Closed"
),
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
).data
| Feature | Compatibility | Note |
|---|---|---|
| Portals | Compatible | |
| Offline Mobile | Partially compatible | Can be used with offline mobile if it is loaded at the top of the form. |
| Sync-Time Custom Record Fields | Incompatible | |
| Real-Time Custom Record Fields | Incompatible | Custom record fields that evaluate in real time must be configured using one or more Custom Field functions. |
| Process Reports | Incompatible | Cannot be used to configure a process report. |
| Process Events | Incompatible | Cannot be used to configure a process event node, such as a start event or timer event. |
a!queryFilter() Function
