There are two different ways to retrieve data from an RDBMS in an expression, a!queryEntity()
and a!queryRecordType
. This page describes some of the properties of these queries and details the differences between them.
These queries can be called from any expression, so they can be reused across interface expressions, process models, and record type definitions. Parameters can be used to filter or page the array of data returned.
An a!queryEntity() is an expression function that takes a Data Store Entity and a Query as parameters and executes the query against that Entity. You can easily create simple entity queries using the Query Editor.
An a!queryRecordType() is an expression function that takes a process-backed or entity-backed Record Type and executes the query against that Record Type.
See the following table for information on the filter operators that can be used with a!queryEntity()
and a!queryRecordType()
.
Case-sensitivity or insensitivity for text comparisons is determined by your RDBMS settings - not a setting configured in Appian.
Operator | Description | Supported Data Types |
---|---|---|
= | The case-insensitive condition returns True if the field value exactly matches the compared value. |
Boolean, Date, Date and Time, Time, Numbers |
< | The condition returns True if the field value is less than the compared value. | Boolean, Date, Date and Time, Time, Numbers, Text |
<= | The condition returns True if the field value is less than or equal to the compared value. | Boolean, Date, Date and Time, Time, Numbers, Text |
> | The condition returns True if the field value is greater than the compared value. | Boolean, Date, Date and Time, Time, Numbers, Text |
>= | The condition returns True if the field value field value is greater than or equal to the compared value. | Boolean, Date, Date and Time, Time, Numbers, Text |
<> | The condition returns True if the field value does not match the compared value. | Boolean, Date, Date and Time, Time, Numbers, Text |
starts with | The condition returns True if the field value begins with the text string listed as your comparison value. Special characters do not have special meaning as wildcards. If the field value is null, the item will not be returned regardless of the rule input value. |
Text |
ends with | The condition returns True if the field value ends with the text string specified as your comparison value. If the field value is null, the item will not be returned regardless of the rule input value. |
Text |
includes | The condition returns True if the field value contains the string specified as the comparison value. If the field value is null, the item will not be returned regardless of the rule input value. |
Text |
in | The condition returns True if the field value exactly matches any one of the values in the compared list of values. | Boolean and list of Boolean, Date and list of Date, Date and Time and list of Date and Time, Time and list of Time, Numbers and list of Numbers, Text and list of Text |
not in | The condition returns True if the field value does not match any one of the values in the compared list of values. | Boolean and list of Boolean, Date and list of Date, Date and Time and list of Date and Time, Time and list of Time, Numbers and list of Numbers, Text and list of Text |
You can limit the number of results returned by your query by passing an optional paging parameter of type PagingInfo. This allows you to specify how many items should be returned and the sort of those items.
When a paging parameter is specified, a value of type DataSubset is returned. Use the a!pagingInfo() function to construct the paging parameter.
The following rules apply when applying multiple sorts:
The queries you define are limited in how long they wait to return results (10 seconds). This setting can be configured by a system administrator.
There is also a limit to the amount of memory that can be returned by a single query. This is set by the conf.data.query.memory.limit
value. The system will display an error with code APNX-1-4164-024
if this error is reached by a query. Use the paging parameter to return less data (or return data in batches) to avoid the limit.
See also: Configuring Query Limits
It is important that you tailor your queries to provide only desired information, especially when substantial growth in the data-sets queried is expected.
When designing queries, keep the following considerations in mind:
By default, queries do not return a constrained subset of matching data records, unless you configure query conditions and filters and call the query using the PagingInfo parameter.
The following improper design example illustrates how not to effectively implement queries.
Given a form that also displays the value of a previous item using a query, the following configuration might be used:
It is possible to implement a query with an additional rule input to function as a boundary for the attribute that you're filtering on.
For example, instead of id < currentId, you could implement two query conditions:
Id < currentId
— and —
Id > minimumBoundary
The query would then use two rule inputs GetPrior(currentId, minimumBoundary). This allows you to nest the query in one or more if statements within your expression.
For example:
1
if(length(GetPrior(currentId, currentId-1)) > 0, GetPrior(currentId, currentId-1), GetPrior(currentId, currentId-10))