Function

a!queryRecordType( recordType, fields, filters, pagingInfo, fetchTotalCount, relatedRecordData )

Executes a query on a given record type and returns the result.

See also:

• Record Type
• DataSubset
• Constructing Data Type Values
• Aggregation Fields Function
• a!relatedRecordData() function

Parameters

Types of queries

There are two methods of returning data using a!queryRecordType:

• A selection returns each record as a separate item in the response. Any field of the record type or related record type can be returned by providing the field within a list in the fields parameter. To filter, sort, or limit data returned from a one-to-many relationship, use a!relatedRecordData in the relatedRecordData parameter.
• An aggregation groups fields by common values and also allows using a function to produce a calculated result. An aggregation requires using supporting functions a!aggregationFields, a!grouping, and a!measure to define the query.

Returns

The return type of a!queryRecordType will be a Map data type with the following parameters:

• success (Boolean)
• data (List of Record Maps 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:

• When selecting data, the return type is a list of record map that matches the specified record type.
• When aggregating data, the return type is a list of map where the keys of the map match the aliases of each grouping or measure.

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>]

Error codes

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.

Usage considerations

Supported record types

• 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.

Querying record fields

• 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 that this will not return related record fields, only record fields from the record type specified in the recordType parameter.

Querying related record fields

• 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.

Using the relatedRecordData parameter

• This parameter allows you to filter, sort, and limit related record data from a one-to-many relationship using the a!relatedRecordData() function.
• This parameter is only supported on record types that have data sync enabled and have a one-to-many relationship with another record type.
• You cannot use this parameter when performing an aggregation.
• 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 the a!relatedRecordData() function for additional usage considerations.

Aggregating data

• When performing an aggregation, you can apply filters within a!measure().
  • You can filter on record fields or related record fields.
  • 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 on could be recordType!Case.fields.status. See Aggregation of all cases and cases with a status of critical for an example.

Using the pagingInfo parameter

• The batchSize in your pagingInfo for a!queryRecordType cannot be larger than 5000, and 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.
• Multiple sortInfos are not yet available for process-backed record types.

Examples

The following examples use the Customer record type, which has a one-to-many relationship with the Case record type.

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.

Selection of customer fields

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
a!queryRecordType(
  recordType: recordType!Customer,
  fields: {
    recordType!Customer.fields.Name,
    recordType!Customer.fields.LogoID,
    recordType!Customer.fields.Industry
  },
  filters: {
    a!queryFilter(
      field: recordType!Customer.fields.InactiveFlag,
      operator: "=",
      value: false
    )
  },
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 5000,
    sort: {
      a!sortInfo(
        field: recordType!Customer.fields.UpdatedOn,
        ascending: false
      ),
      a!sortInfo(
        field: recordType!Customer.fields.Name,
        ascending: true
      )
    }
  ),
  fetchTotalCount: true
)

Sample output from the query above:

Selection of customer fields and support case fields

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:

Aggregation of all cases and cases with a status of critical

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 "Critical"*/
      a!measure(
        field: recordType!Customer.relationships.cases.id,
        function: "COUNT",
        alias: "count_critical",
        label: "Critical cases",
        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: "Critical"
          )
      )
    }
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 100,
    sort: {
      a!sortInfo(
        field: "customer_name",
        ascending: true
      )
    }
  )
)

Sample output from the query above:

Old versions

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.

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.