a!queryRecordType( recordType, fields, filters, pagingInfo, fetchTotalCount, relatedRecordData )
Executes a query on a given record type and returns the result.
See also:
Keyword | Type | Description |
---|---|---|
|
RecordType |
A reference to a record type. You must reference the record type directly from the |
|
Any Type |
Fields to be retrieved for the query. This parameter accepts different values depending on the type of query to run. When performing a selection, use a list of record field or related record field references defined with the |
|
Any Type |
A single logical expression or a list of query filters can be provided to apply additional filters to the record set. Queries also inherit the default filters defined on the referenced record type. Reference record fields or related record fields from the specified record type using the |
|
PagingInfo |
The paging and sorting configurations to apply when retrieving the record data. |
|
Boolean |
This parameter is only used when querying unsynced record types that use a database as the source. Synced record types automatically return the totalCount property. If set to |
|
List of RelatedRecordData |
When selecting one-to-many related record data, use |
There are two methods of returning data using a!queryRecordType()
:
Tip: Use a!queryRecordByIdentifier() to query a single record and its related data. This function's simpler syntax makes it quick to build summary views, related action forms, and other interfaces where a single record is the focus.
The return type of a!queryRecordType()
will be a Map data type with the following parameters:
success
(Boolean)data
(List of Records or List of Maps)startIndex
(Integer)batchSize
(Integer)sort
(List of SortInfo)totalCount
(Integer)identifiers
(List of Integer or List of Text)errorCode
(Text)This map will have the same named fields as a DataSubset (so that it can seamlessly be cast to that type when being passed around your application) with the addition of "success" and "errorCode" fields, like results from an Integration object, so app developers can build error-handling into their applications.
The type for the data parameter varies depending on the type of query:
You can refine the function's output by using dot notation (.) to access specific fields. To have the expression return just the queried data, add .data
to the end of the a!queryRecordType()
function. Then, to access the data contained in a particular field, add the field name with a record field reference: data[recordType!<record type name>.fields.<field name>]
a!queryRecordType()
returns a query result even if a runtime error happened. In these cases the "success" field of the result would have a value of false and the "errorCode" field will return an Appian Error Code indicating what has happened. You can use these codes to decide what to do next in your application.
Error Code | Description | Usage |
---|---|---|
APNX-1-4205-038 | The user does not have access to this record type, a related record type, or the underlying data source. This also applies if the user does not have access to a referenced related record type in a filter, sort, or aggregation. | This error code could be used to hide elements of an interface. |
APNX-1-4205-039 | The record type or a related record type has not been synced, or the existing synced data has been invalidated. | This error code could be used as a condition on which to query the data source instead of waiting for the sync issue to be resolved. |
APNX-1-4205-040 | The record type's data source or the related record type's data source could not be reached, or the query limit for that source has been reached. | While uncommon, for finicky data sources, this error code could be used to prevent an interface from breaking. |
You must update the target record type after upgrading to 20.3 in order to use this query.
Service-backed and Salesforce-backed record types without data sync enabled are not supported.
To reference a record field, use the recordType!
domain to reference a record field. For example, recordType!Customer.fields.firstName
returns the firstName
field from the Customer record type.
If you don't specify a selection of fields, all record fields are returned.
Note: This will not return related record fields, only record fields from the record type specified in the recordType parameter.
You can reference related record fields in the fields parameter and when sorting or filtering.
To reference a related record field, use the recordType!
domain to reference a relationship and select a related record field. For example, recordType!Customer.relationships.cases.fields.status
is using the relationship on the Customer record type to select the status
field from the Case record type.
You can also reference the record type relationship in the fields parameter to return all related record fields from the specified relationship. For example, using the relationship reference recordType!Customer.relationships.cases
in the fields parameter would return all fields from the Case record type.
If you only specify a relationship reference or a related record field reference in the fields parameter, the query will return all fields from the base record type and the selected related record fields. For example, the following query would return all fields from the Customer record type and the status
fields from the Case record type:
1
2
3
4
5
a!queryRecordType(
recordType: recordType!Customer,
fields: {recordType!Customer.relationships.cases.fields.status},
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 500)
).data
If you are querying one-to-many related record fields, the maximum number of related record data returned for each base record is 10. For example, when querying customers and their related support cases, the query will return a max of 10 related support cases for each customer.
To limit, filter, and sort your related one-to-many data, you can use the relatedRecordData parameter. Learn more about using this parameter.
When performing an aggregation, you can apply filters within a!measure().
You can filter by record fields or related record fields, but the field reference must start from the aggregated record type. For example, if the measure is on recordType!Customer.relationships.cases.fields.id
, the field to filter by could be recordType!Case.fields.status
.
See Aggregation of all cases and cases with a status of critical for an example.
The relatedRecordData parameter allows you to filter, sort, and limit related record data from a one-to-many relationship using the a!relatedRecordData() function.
For example, let's say you're querying the Customer record type and you only want to return the latest case associated with each customer. Since a customer can have many cases, by default, the query will return up to 10 cases related to each customer. In the relatedRecordData parameter, you could use the a!relatedRecordData()
function to limit and sort the cases returned from the related record type Case.
When applying a filter or sort to a!relatedRecordData()
, your record field or related record field reference must start from the related record type specified in the relationship parameter. See additional usage considerations for using the a!relatedRecordData()
function.
Note: You cannot use this parameter when performing an aggregation.
The batchSize in your pagingInfo for a!queryRecordType()
can be between 0
and 5000.
It cannot be -1
. If the record type isn't sorted by the identifier, a final identifier sort is added to ensure deterministic sorting when using a selection. See a!pagingInfo() for more information.
Note: Multiple sortInfos
are not available for process-backed record types.
For record types with data sync enabled, fetchTotalCount
is applied because Appian already knows the total number of records and therefore doesn't need to query the source to provide this information.
For record types without data sync enabled, there is a performance cost to getting the total number of records with a query. If you decide you want to retrieve the total count, set fetchTotalCount
to true
. The following table describes two scenarios and how to set the parameters required to achieve that result.
Scenario | Batch Size | Fetch Total Count | Value of totalCount |
---|---|---|---|
You want a subset of records, but also want to know the total number of records after filters are applied. Appian must run an extra query to get the total count. | Any positive number | true |
Total number of records after filters are applied |
You want a subset of rows and don't need to know the total number of rows after filters are applied. | Any positive number | false |
-1 |
The following examples use the Customer record type, which has a one-to-many relationship with the Case record type.
For additional examples, see:
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 reference only.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
a!queryRecordType(
recordType: recordType!Customer,
fields: {
recordType!Customer.relationships.person.fields.fullName,
recordType!Customer.fields.accountNumber,
recordType!Customer.relationships.person.fields.emailPromotion
},
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 5000,
sort: {
a!sortInfo(
field: recordType!Customer.relationships.person.fields.modifiedDate,
ascending: false
),
a!sortInfo(
field: recordType!Customer.relationships.person.fields.fullName,
ascending: true
)
}
),
fetchTotalCount: true
)
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
a!queryRecordType(
recordType: recordType!Customer,
fields: {
/*Fields from the Customer record type*/
recordType!Customer.fields.name,
recordType!Customer.fields.phoneNumber,
/*Related fields from the Case record type*/
recordType!Customer.relationships.cases.fields.title
},
relatedRecordData: {
/*Only return the latest support case for each customer*/
a!relatedRecordData(
relationship: recordType!Customer.relationships.cases,
sort: {
a!sortInfo(
field: recordType!Case.fields.createdOn,
ascending: false
)
},
limit: 1
)
},
pagingInfo: a!pagingInfo(
startindex: 1,
batchSize: 500
)
)
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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
a!queryRecordType(
recordType: recordType!Customer,
fields: a!aggregationFields(
groupings:
a!grouping(
field: recordType!Customer.fields.name,
alias: "customer_name"
),
measures: {
/* Count of all support cases */
a!measure(
field: recordType!Customer.relationships.cases.id,
function: "COUNT",
alias: "count_total",
label: "All cases"
),
/* Count of all support cases that have a status of "In Progress"*/
a!measure(
field: recordType!Customer.relationships.cases.id,
function: "COUNT",
alias: "casesInProgress",
label: "Cases in progress",
filters:
/* Since the aggregation is on the Case record type,
the field reference starts from the Case record type */
a!queryFilter(
field: recordType!Case.fields.status,
operator: "=",
value: "In Progress"
)
)
}
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 100,
sort: {
a!sortInfo(
field: "customer_name",
ascending: true
)
}
)
)
Sample output from the query above:
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. |
There are older versions of this function . You can identify older versions by looking at the name to see if there is a version suffix. If you are using an old version, be sure to refer to the corresponding documentation from the list below.
Old Versions | Reason for Update |
---|---|
a!queryRecordType_20r4 | Replaced selection with fields, which now allows for both selection and aggregation of record data when querying a given record type. |
To use the latest version of the function, replace the function with a version suffix with a new function reference.
To learn more about how Appian handles this kind of versioning, see the Function and Component Versions page.
a!queryRecordType() Function