queryrecord() Function

This function executes a user-defined query on a given record type and returns the result.

Syntax

queryrecord( recordType, query )

recordType: (RecordType) The record type to query.

query: (Query) The query definition of the report including grouping, aggregation, filtering, paging, and sorting configurations.

Returns

DataSubset

Notes

The DataSubset returned by this function is used to generate report grids or graphs based on record data.

The recordType value should be given as a constant of type RecordType. The query value must be entered as a type constructor.

The recordType value can not reference a expression-backed RecordType. Only process-backed and entity-backed RecordTypes are supported as values for this function.

The logicalExpression|filter|search field of the query object can only be set to a LogicalExpression or a QueryFilter type. Search types are not yet supported.

The expression will fail to evaluate and an error will occur if the record type reference is not provided, invalid, or null, the user does not have permission to view the RecordType, the query value is not provided, invalid, incomplete, or an inconsistent query object.

If called multiple times in the same expression with the same parameters, only one query is made to the record when the expression is evaluated and the result is reused for each identical call. This caching only applies within a single expression evaluation. Return values are not cached in separate expressions, or in separate evaluations of the same expression.

Examples

You can copy and paste these examples into the Expression Rule Designer to see how this works.

Example 1

NOTE: Since the record type "SalesRecordType" is invented for the purpose of illustration, this example will not evaluate in your Test Rules interface. Use it only as a reference.

    =queryrecord(
      cons!SalesRecordType,
      a!query(
        aggregation:a!queryAggregation(
          columns:{
            a!queryAggregationColumn(
              field:"date.quarter",
              alias:"qt",
              visible:true(),
              isgrouping:true()
            ),
            a!queryAggregationColumn(
              field:"revenueFromSale",
              alias:"tot",
              visible:true(),
              function:"SUM"
            ),
            a!queryAggregationColumn(
              field:"revenueFromSale",
              alias:"avg",
              visible:true(),
              function:"AVG"
            )
          }
        ),
        pagingInfo: a!pagingInfo(
          startIndex:1,
          batchSize:100,
          sort:a!sortInfo(
            field:"qt",
            ascending:true()
          )
        )
      )
    )

Returns

Dictionary
 startIndex: 1
 batchsize: 100
 sort: List of SortInfo: 1 item
  SortInfo
    field: "qt"
    ascending: true
 totalCount: 7
 data: List of Dictionary: 7 items
  Dictionary
    qt: "2010 Q4"
    tot: 456.89
    avg: 4.38
  Dictionary
    qt: "2011 Q1"
    tot: 756.79
    avg: 7.37
  Dictionary
    qt: "2011 Q2"
    tot: 56.79
    avg: 4
  Dictionary
    qt: "2011 Q3"
    tot: 1756.09
    avg: 127.3
  Dictionary
    qt: "2011 Q4"
    tot: 56.12
    avg: 3.22
  Dictionary
    qt: "2012 Q1"
    tot: 75.97
    avg: 1.37
  Dictionary
    qt: "2012 Q2"
    tot: 4959.55
    avg: 122.42
 identifiers: List of Text String: 7 items
  "2010 Q4"
  "2011 Q1"
  "2011 Q2"
  "2011 Q3"
  "2011 Q4"
  "2012 Q1"
  "2012 Q2"

Example 2

The following example shows how to aggregate record data by year and month. This functionality is also available in a!queryEntity(). See a!queryAggregationColumn().

This example is dependent on the Employee record type created in the Records Tutorial. You will also need a constant that points to that record type and call it EMPLOYEE_RECORD_TYPE.

queryrecord(
  cons!EMPLOYEE_RECORD_TYPE,
  a!query(
    aggregation: a!queryAggregation(
      aggregationColumns: {
        a!queryAggregationColumn(
          field: "startDate",
          alias: "startDate_year",
          isGrouping: true,
          groupingFunction: "YEAR"
        ),
        a!queryAggregationColumn(
          field: "startDate",
          alias: "startDate_month",
          isGrouping: true,
          groupingFunction: "month"
        ),   
        a!queryAggregationColumn(
          field: "id",
          alias: "id_count",
          aggregationFunction: "COUNT"
        )
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 100,
      sort: a!sortInfo(
        field: "startDate_year",
        ascending: true
      )
    )
  )
)

Returns

DataSubset
 startIndex: 1
 batchSize: 100
 sort: List of SortInfo: 1 item
  SortInfo
    field: "startDate_year"
    ascending: true
 totalCount: 8
 data: List of Dictionary: 8 items
  Dictionary
    startDate_year: 2017
    startDate_month: 10
    id_count: 2
  Dictionary
    startDate_year: 2018
    startDate_month: 2
    id_count: 3
  Dictionary
    startDate_year: 2018
    startDate_month: 4
    id_count: 1
  Dictionary
    startDate_year: 2018
    startDate_month: 5
    id_count: 4
  Dictionary
    startDate_year: 2018
    startDate_month: 6
    id_count: 4
  Dictionary
    startDate_year: 2018
    startDate_month: 7
    id_count: 1
  Dictionary
    startDate_year: 2018
    startDate_month: 9
    id_count: 3
  Dictionary
    startDate_year: 2018
    startDate_month: 10
    id_count: 2
 identifiers: List of Variant: 0 items

See Also

  • RecordType: Additional information on the data type accepted for the recordType value.
  • Query: Additional information on the data type accepted for the query value and how to create a type constructor for it including which fields are required/optional.
  • Query Recipes - A list of examples of how to use the Query object to meet common use cases.
  • DataSubset: Additional information on the structure of this data type.
  • Constructing Data Type Values: Additional information on creating the query value via a type constructor.
  • a! queryAggregationColumn() Function: Additional information on grouping Date and Date and Time fields by year and month.
FEEDBACK