This function executes a user-defined query on a given record type and returns the result.
queryrecord( recordType, query )
recordType: (RecordType) The record type to query. Use the recordType! domain to reference the record type object you want to query.
query: (Query) The query definition of the report including grouping, aggregation, filtering, paging, and sorting configurations.
DataSubset
The DataSubset returned by this function is used to generate report grids or graphs based on record data.
The recordType (RecordType) value should be referenced using the recordType! domain. 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 record types 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.
These examples are designed to illustrate how to use the queryrecord() function in the Expression Rule Interface.
NOTE: This uses the record type "Sales" for the purpose of illustration only. If you copy and paste the expression below into the Expression Rule Interface, it will not evaluate in your Test Rules interface. Use it 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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
=queryrecord(
/* Replace the record type reference below (recordType!Sales) with a valid
* record type reference in your environment.
*/
recordType!Sales,
a!query(
aggregation:a!queryAggregation(
aggregationColumns:{
a!queryAggregationColumn(
field:"date.quarter",
alias:"qt",
visible:true(),
isGrouping:true()
),
a!queryAggregationColumn(
field:"revenueFromSale",
alias:"tot",
visible:true(),
aggregationFunction:"SUM"
),
a!queryAggregationColumn(
field:"revenueFromSale",
alias:"avg",
visible:true(),
aggregationFunction:"AVG"
)
}
),
pagingInfo: a!pagingInfo(
startIndex:1,
batchSize:100,
sort:a!sortInfo(
field:"qt",
ascending:true()
)
)
)
)
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
45
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"
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 use the recordType! domain to reference the Employee record type.
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
queryrecord(
/* Replace the record type reference below (recordType!Employee) with a valid
* record type reference in your environment.
*/
recordType!Employee,
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
)
)
)
)
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
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
