Free cookie consent management tool by TermsFeed

Query Recipes

Overview

This page provides a set of recipes designed to show how you can use the a!queryRecordType() and a!queryEntity() functions to retrieve, aggregate, filter, and sort data.

To create queries using a guided experience, see Using the Query Editor.

Recipes using a!queryRecordType()

The recipes in this section show how to perform common data lookups using the a!queryRecordType() function.

The recipes can be worked on in no particular order. However, make sure to read the Setup section before you begin.

Setup

To work with a common set of data, we will use the Appian Retail application, available for free in Appian Community Edition. To follow along with this pattern, log on to Appian Community and request the latest Appian Community Edition site.

The sample expressions provided in each recipe can be copied into an expression rule and tested as written. You can modify these examples as you experiment further with the concepts and techniques explained in the recipe.

Return data for all record fields

Goal: Access the data for all fields on a record.

When you query a record type using the a!queryRecordType() function, the result includes a data parameter containing the information you specified in the query (usually as a List of the record type). The result includes record fields and any custom record fields you've defined in the record type.

Expression

In this example, we'll look up all Customer information for the person with the CustomerID of 29484. Note that the fields keyword is not included in the function, so all fields are returned by default. To have the expression only return the queried data, add .data to the end of the a!queryRecordType() function.

1
2
3
4
5
6
7
8
9

a!queryRecordType(
  recordType: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer',
  filters: a!queryFilter(
      field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{aaa21651-214a-4462-918f-fbe369e86b94}CustomerID',
      operator: "=",
      value: 29484
    ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
).data

Query test output - data only

Expected result

The query will return the following data:

[Customer CustomerID=29484, personId=291, storeId=292, territoryId=5, accountNumber=0, salesByCustomer=1118051, productDistinctCount=1098, latestOrderDate=4/30/2021 12:00 AM GMT+00:00, uniqueNumberOfProducts=45, totalNumberOfProducts=1098, sumOfSales=1118051]

Return data from a single record field

Goal: Access the data in a single field on a record.

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

Query test output - record field only

Expression

1
2
3
4
5
6
7
8
9
10
11

a!queryRecordType(
  recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
  fields: { 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{0eb0b768-dd12-46cb-8b1d-306d97534326}dueDate'
},
  filters: a!queryFilter(
    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{262bb249-cf34-4171-a573-54831d0958dd}orderId',
    operator: "=",
    value: 43659
),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 500)
).data['recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{0eb0b768-dd12-46cb-8b1d-306d97534326}dueDate']

Expected result

The query will return the following data: 6/12/2019 12:00 AM GMT+00:00

Return the distinct values for a field

Goal: Use an aggregation to get the unique values for a record field.

When adding a selection component to an interface, you may want to set the values for a field as the choiceLabels and choiceValues. For example, an Appian Retail customer may want to filter products by color. This could be presented as a checkbox component with the available color options.

Filter product example with distinct values

To get the list of colors, you need to query the Product record type and return all of the values used in the color field. In the fields parameter, create a grouping on the color field. To get the list of colors, use dot notation to access the .data.color values.

Expression

1
2
3
4
5
6
7
8
9
10
11

a!queryRecordType(
  recordType: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product',
  fields: a!aggregationFields(
    a!grouping(field: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product.fields.{ce2f192d-ffe4-4a39-8fa5-236f09e82291}color', alias: "color")
  ),
  filters: a!queryFilter(
    field: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product.fields.{ce2f192d-ffe4-4a39-8fa5-236f09e82291}color',
    operator: "not null"
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 100)
).data.color

Expected result

The query will return the following data:

Black; Blue; Grey; Multi; Red; Silver; Silver/Black; White; Yellow

Aggregate data from a field

Goal: Group and measure data based on the values of a selected field.

An aggregation groups data based on the values of a field and optionally measures those grouped sets. Aggregations are set in the fields parameter with a!aggregationFields().

Imagine you are in charge of inventory for a clothing store. You may want to know how many products of each size you have, as well as whether you offer colorful options across the range of sizes. In this example, we group product records by their size, count the number of products in each group, and filter out any black or white products.

Expression:

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

a!queryRecordType(
  recordType: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product',
  fields: a!aggregationFields(
    groupings: a!grouping(
      field: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product.fields.{4d5bb735-5a67-49e0-9ac1-a5467ba4b6b3}size',
      alias: "size"
    ),
    measures: a!measure(
      field: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product.fields.{4d5bb735-5a67-49e0-9ac1-a5467ba4b6b3}size',
      function: "COUNT",
      alias: "count",
      filters: a!queryFilter(
        field: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product.fields.{ce2f192d-ffe4-4a39-8fa5-236f09e82291}color',
        operator: "not in",
        value: {"Black", "White"}
      )
    )
  ),
  filters: a!queryFilter(
    field: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product.fields.{4d5bb735-5a67-49e0-9ac1-a5467ba4b6b3}size',
    operator: "not null"
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 100
  )
).data

Expected result

The query will return the following data:

[size:38,count:7]; [size:40,count:7]; [size:42,count:10]; [size:44,count:18]; [size:46,count:9]; [size:48,count:14]; [size:50,count:9]; [size:52,count:9]; [size:54,count:9]; [size:56,count:2]; [size:58,count:10]; [size:60,count:9]; [size:62,count:8]; [size:70,count:1]; [size:L,count:6]; [size:M,count:6]; [size:S,count:4]; [size:XL,count:2]

Aggregate data using date or time

Goal: Perform a time-based aggregation on all values of a field.

Aggregations that group data based on date or time information use the interval field in a!grouping() to specify how data is grouped. In this example, we count the number of products made available for sale in a given year.

Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

a!queryRecordType(
  recordType: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product',
  fields: a!aggregationFields(
    groupings: a!grouping(
      field: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product.fields.{726cc562-42a3-47b7-b8c7-ddcdc3e26540}sellStartDate',
      alias: "yearAdded",
      interval: "YEAR"
    ),
    measures: a!measure(
      field: 'recordType!{dee327a7-3854-45e5-a952-d3d7d3edcc82}Product.fields.{acd3b1b4-a697-4909-800b-f841e79586c1}productId',
      function: "COUNT",
      alias: "numberOfProducts"
    )
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 100
  )
).data

Expected result

The query will return the following data:

[yearAdded:2008,numberOfProducts:211]; [yearAdded:2011,numberOfProducts:72]; [yearAdded:2012,numberOfProducts:85]; [yearAdded:2013,numberOfProducts:136]

Filter out data with null values

Goal: Check for fields with null values and only return records with complete data.

For situations where you only want to return complete records (i.e., records that are not missing values), add a filter that excludes records with a null value for a specific field.

For example, an Appian Retail online order is not considered done until the customer's credit card is charged and the order is shipped. You can filter out in-progress or otherwise incomplete orders by checking if the record type's shipDate and creditCardApprovalCode fields are null.

Tip:  You can also exclude records that do not have any related records by filtering by a relationship reference. Learn how.

Expression

There are two ways to build this expression depending on the level of detail required for your query. First, you could use a list of a!queryFilter to filter records where both fields are null (the AND operator is used to evaluate lists of filter functions).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

a!queryRecordType(
  recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
  filters: {
    a!queryFilter(
      field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{21640862-1dae-4c1a-907e-9eae8a7e37c6}shipDate',
      operator: "not null"
    ),
    a!queryFilter(
      field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{15da517f-2c35-4658-b8d1-da8bd387e493}creditCardApprovalCode',
      operator: "not null"
    )
  },
  pagingInfo: a!pagingInfo(
    startIndex: 1, 
    batchSize: 10
  )
).data

A more refined query uses an a!queryLogicalExpression() with the OR operator to filter out records with either or both of the null fields. This creates a more accurate list of in-progress orders since an order could be charged but not shipped.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

a!queryRecordType(
  recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
  filters: a!queryLogicalExpression(
    operator: "OR",
    filters: {
    a!queryFilter(
      field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{21640862-1dae-4c1a-907e-9eae8a7e37c6}shipDate',
      operator: "not null"
    ),
    a!queryFilter(
      field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{15da517f-2c35-4658-b8d1-da8bd387e493}creditCardApprovalCode',
      operator: "not null"
    )
  }
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1, 
    batchSize: 10
  )
).data

Expected result

The query will return Orders with the following orderIds:

43659; 43660; 43661; 43662; 43663; 43664; 43665; 43666; 43667; 43668

Return data between two dates

Goal: Use a date range to filter the query results.

There are many cases where a user of your application will want to know about time-bound records. For example, an Appian Retail user may want to see the orders created over a weekend so they can start filling those orders at the start of the next week. To get this information in your query, you add a filter that defines the time range in question so only those records are returned.

Once you've built the basic a!queryRecordType() function and defined the recordType, fields, and pagingInfo, you can add the filters needed to refine the results. In the a!queryFilter() function, add the required parameters with the following values:

  • field - the record type being filtered.
  • operator - the string "between". This means that both the lower and upper values are included as matches when the query is run.
  • value - an a!todatetime() function whose arguments are the start and end dates of the range.

Filter by date range

The between operator takes an inclusive range of values.

Filter with between operator

Expression

Tip:  Record fields containing date or time information can be configured using one of three data types: Date, Date and Time, or Time. This example filters on a Date and Time field, but the concepts apply to any of the three types.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

a!queryRecordType(
  recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
  fields: { 
    'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{3f2ba6f1-4afd-4a21-afd1-f399eb6c18e0}orderNumber',
    'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue',
  },
  filters: a!queryFilter(
    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
    operator: "between",
    value: todatetime({"06/08/2019", "06/09/2019"})
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 10
  )
).data

Expected result

The query will return Orders with the following orderIds:

43728; 43729; 43730; 43731; 43732; 43733; 43734; 43735; 43736; 143728

Query data not matching a list of values

Goal: Create a filter that excludes records with a field containing one of a specific set of values.

For example, the Appian Retail Company ships to several countries (the US, Canada, Australia, Germany, France, and Great Britain), and the head of sales wants to keep track of volume in each. The Country record type has a countryRegionCode field we can use to filter orders by location.

To retrieve the 100 most recent sales in North America, we need to exclude countries in the two other regions, Europe and the Pacific. This can be done by using the not in operator with a list of values to exclude from the results.

The not in operator is a simpler way to write multiple <> filters. You could filter out the countries individually as in the following example:

Multiple not equal statements

To make the expression rule easier to read and understand, replace the list of filters with a single a!queryFilter() that uses the not in operator.

Not if filter

Expression

1
2
3
4
5
6
7
8
9
10
11

a!queryRecordType(
  recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
  fields: {
  },
  filters: a!queryFilter(
    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{ae938f73-f61f-483d-8ca0-65d3c911abae}salesRegion.fields.{93103f55-2e37-4e74-adf5-e26dcc5c4911}countryRegionCode',
    operator: "not in",
    value: {"FR", "DE", "AU", "GB"}
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 10)
).data

Expected result

The query will return Orders with the following orderIds:

43660; 43661; 43662; 43663; 43664; 43665; 43666; 43667; 43668; 43669

Search using multiple fields

Goal: Retrieve records based on user-provided search criteria. Criteria that are left blank by the user should not be included in the query.

Some queries take user input and use those values to return the data needed by the user at that moment. In this example, we need to create three rule inputs:

  • firstName, a Text input
  • lastName, a Text input
  • storeId, a Number (Integer) input

These rule inputs are used as the value in our expression's filters. We also add the ignoreFiltersWithEmptyValues parameter so users are able to enter one, two, or three inputs and still get a list of results.

Expression

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

a!queryRecordType(
  recordType: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer',
  fields: {
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{ab3f9d64-6b2e-4bd6-9289-a03dc5e7d386}person.fields.{120551bc-43ca-4057-8d03-dd69fcd018ed}fullName',
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{7f5b6a8f-f6a0-49e7-a7b2-7f9da3a8247c}latestOrderDate',
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{3cce119f-1740-41d3-9150-2ad86b886c94}storeId',
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{5bf683af-7b53-4f65-a761-3f1c77bda3d3}sumOfSales'
  },
  filters: a!queryLogicalExpression(
    operator: "OR",
    filters: {
      a!queryFilter(
      field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{ab3f9d64-6b2e-4bd6-9289-a03dc5e7d386}person.fields.{d2c68d60-1583-41eb-a37c-8a2cba28025f}firstName',
      operator: "includes",
      value: ri!firstName
    ),
    a!queryFilter(
      field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{ab3f9d64-6b2e-4bd6-9289-a03dc5e7d386}person.fields.{17d36772-417e-4658-92e9-5deabf2d4553}lastName',
      operator: "includes",
      value: ri!lastName
    ),
    a!queryFilter(
      field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{3cce119f-1740-41d3-9150-2ad86b886c94}storeId',
      operator: "=",
      value: ri!storeId
    ),
   },
   ignoreFiltersWithEmptyValues: true
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1, 
    batchSize: 50
  )
).data

Sort on multiple fields in a selection

Goal: Sort on multiple fields when performing a selection query.

Selections return the data specified in the fields of a!queryRecordType. Organizing these query results with sorting is a powerful way to present usable data to users without requiring them to interact with a grid or chart to make their own sorting choices.

Imagine the Appian Retail sales team is interested in seeing the customers that purchase the widest variety of products and who spends the most overall. This example shows how you can select some useful fields and sort them based on user needs.

Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

a!queryRecordType(
  recordType: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer',
  fields: {
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{aaa21651-214a-4462-918f-fbe369e86b94}CustomerID',
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{7f5b6a8f-f6a0-49e7-a7b2-7f9da3a8247c}latestOrderDate',
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{7662e543-fb54-468c-8014-b688abdca4fb}uniqueNumberOfProducts',
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{5bf683af-7b53-4f65-a761-3f1c77bda3d3}sumOfSales',
    'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{ab3f9d64-6b2e-4bd6-9289-a03dc5e7d386}person.fields.{120551bc-43ca-4057-8d03-dd69fcd018ed}fullName'
  },
  pagingInfo: a!pagingInfo(
    startIndex: 1, 
    batchSize: 10,
    sort: {
      a!sortInfo(
        field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{7662e543-fb54-468c-8014-b688abdca4fb}uniqueNumberOfProducts'
      ),
      a!sortInfo(
        field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{5bf683af-7b53-4f65-a761-3f1c77bda3d3}sumOfSales'
      )
    }
  )
).data

Expected result

The query will return Customers with the following CustomerIds:

29722; 29950; 30107; 30048; 29712; 29734; 29702; 29744; 29559; 29996

Sort on multiple fields in an aggregation

Goal: Sort on multiple aggregated fields.

The results of an aggregation can be sorted to present an organized view of the groupings or measures created from the queried record.

Appian Retail's sales team wants to know which territories had the most online orders. To get this information, you'll create a query that gets the total number of online orders for each sales region. To quickly see the top performing territories, you'll sort the results by the online order count in descending order.

Expression

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

a!queryRecordType(
  recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
  filters: a!queryFilter(
    field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{ae938f73-f61f-483d-8ca0-65d3c911abae}salesRegion.fields.{93103f55-2e37-4e74-adf5-e26dcc5c4911}countryRegionCode',
    operator: "=",
    value: "US"
  ),
  fields: a!aggregationFields(
    groupings: {
      a!grouping(
        field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.relationships.{ae938f73-f61f-483d-8ca0-65d3c911abae}salesRegion.fields.{2aadab0c-4cbe-4ed4-ad0a-d6da6e08e2f2}name',
        alias: "region"
      )
    },
    measures: a!measure(
      field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{5bade7d5-5fbc-4cc4-807f-907f8f65969b}onlineOrderFlag',
      function: "COUNT",
      filters: a!queryFilter(
        field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{5bade7d5-5fbc-4cc4-807f-907f8f65969b}onlineOrderFlag',
        operator: "=",
        value: 1
      ),
      alias: "onlineOrderCount"
    )
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 10,
    sort: {
      a!sortInfo(field: "onlineOrderCount"),
      a!sortInfo(field: "region")
    }
  )
).data

Expected result

The query will return the following list of data:

[region:Southwest,onlineOrderCount:44546]; [region:Northwest,onlineOrderCount:32956]; [region:Southeast,onlineOrderCount:138]; [region:Northeast,onlineOrderCount:80]; [region:Central,onlineOrderCount:72]

Filter by one-to-many data

Goal: Return records that have at least one related record that meets the filter criteria.

When you filter by a related record field from a one-to-many relationship (that is, the "many" side of the relationship), the filter will return all records from the base record type (the "one" side of the relationship) that have at least one related record that meets the filter condition. In this example, the query returns the customers who made a purchase greater than $100,000 from a specific salesperson (in this case, with the ID 275).

If a query with related record types is not returning what you expect, it may help to consider what will not be returned based on the filters. In our example, if a customer made a purchase from a different salesperson or if their purchase was less than the dollar amount specified, that customer will not appear in the results. Remember that filters are applied with the AND operator, so the record type must meet all filter conditions to appear in the results.

Expression

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

a!queryRecordType(
  recordType: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer',
  fields: a!aggregationFields(
    groupings: a!grouping(
      field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{ab3f9d64-6b2e-4bd6-9289-a03dc5e7d386}person.fields.{120551bc-43ca-4057-8d03-dd69fcd018ed}fullName',
      alias: "customerName"
    )
  ),
  filters: {
    a!queryFilter(
      field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order.fields.{9872b047-0f22-4b84-9892-b4fc3c737b7b}salesPersonId',
      operator: "=",
      value: 275
    ),
    a!queryFilter(
      field: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order.fields.{f4f2ef33-2a2b-4947-a6f2-11603994ed9f}totalDue',
      operator: ">",
      value: 100000
    )
  },
  pagingInfo: a!pagingInfo(
    startIndex: 1, 
    batchSize: 100
  )
).data

Expected result

The query will return the following list of names:

Barbara J. Calone; Dave P. Browning; Elizabeth J. Sullivan; Helen J. Dennis; Janet M. Gates; Kirk DeGrasse; Robert R. Vessa; Stacey M. Cereghino; Valerie M. Hendricks

Recipes using a!queryEntity()

The recipes in this section show how to perform common data lookups using the a!queryEntity() function.

The recipes can be worked on in no particular order. However, make sure to read the Setup section before you begin.

Tip:  You can experiment with all of these query recipes in the query editor. After you copy and paste a query recipe into an empty expression rule, Ctrl+Click (Cmd+Click on Mac) the a!queryEntity() function to open it in the query editor.

Note that if you change the data store entity in the query editor, all of query configurations will be lost and you will be starting over with a new query.

Setup

To copy and paste these recipes, you’ll need the Employee data store entity (DSE) and a constant pointing to this DSE.

To create these two objects, complete the Use the Write to Data Store Entity Smart Service Function on an Interface pattern.

Retrieve the data for all fields

Goal: Retrieve the data for all fields of an entity.

When you execute a query, it pulls back the data for all of the fields of the entity. This recipe replicates that functionality using a!queryEntity().

To retrieve all fields of the entity being queried, omit both the selection and aggregation parameters from a!query(). When using this approach, you should cast the result to the appropriate type to ensure that it works smoothly in process models and rules that use it. This is because a!queryEntity() always returns a DataSubset that includes a dictionary.

In this example, we retrieve the employee whose id is 8.

Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

cast(
  type!Employee,
  a!queryEntity(
    entity: cons!EMPLOYEE_ENTITY,
    query: a!query(
      logicalexpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: "id",
            operator: "=",
            value: 8
          )
        }
      ),
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: 1
      )
    )
  ).data
)

Tip:  In this example, we are nesting a!queryFilter() inside a!queryLogicalExpression(), which makes it possible to combine multiple filters. This isn't required since we only have one filter, but it makes it easier for you to add more filters or edit it in the query editor.

This example should return the following fields: [id=8, firstName=Jessica, lastName=Peterson, department=Finance, title=Analyst, phoneNumber=555-987-6543, startDate=2004-11-01]. This value will be of type Employee.

If you expect your query to return multiple results, you should instead cast to a list of CDT. In this example, we will retrieve any employees whose first name begins with "A".

Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

cast(
  typeof({type!Employee()}),
  a!queryEntity(
    entity: cons!EMPLOYEE_ENTITY,
    query: a!query(
      logicalexpression: a!queryLogicalExpression(
        operator: "AND",
        filters: {
          a!queryFilter(
            field: "firstName",
            operator: "starts with",
            value: "A"
          )
        }
      ),
      pagingInfo: a!pagingInfo(
        startIndex: 1,
        batchSize: 5
      )
    )
  ).data
)

Tip:  In this example, we are nesting a!queryFilter() inside a!queryLogicalExpression(), which makes it possible to combine multiple filters. This isn't required since we only have one filter, but it makes it easier for you to add more filters and edit it in the query editor.

This example should return the value [id=17, firstName=Andrew, lastName=Nelson, department=Professional Services, title=Consultant, phoneNumber=555-789-4560, startDate=3/15/2005]; [id=4, firstName=Angela, lastName=Cooper, department=Sales, title=Manager, phoneNumber=555-123-4567, startDate=10/15/2005].

Retrieve the data for a single field

Goal: Retrieve the data for a single field of an entity rather than all of the fields.

When you execute a query, it returns the data for all of the fields of the entity. The more data you retrieve from the database, the longer the query takes to run. A common way to restrict the amount of data returned by a query is to create several different data store entities that reference the same database table, each of which only contains some of the fields. Instead, using a!queryEntity() to select specific fields as shown below restricts the amount of returned data, is faster to develop, and has the advantage that the field or fields can be selected at run-time rather than design-time.

In this example we are going to retrieve the phone number, stored in the field phoneNumber, for the employee whose id is 8.

Expression

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

a!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    selection: a!querySelection(
      columns: {
        a!queryColumn(
          field: "phoneNumber"
        )
      }
    ),
    logicalexpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "id",
          operator: "=",
          value: 8
        )
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 1
    )
  )
).data.phoneNumber[1]

Tip:  In this example, we are nesting a!queryFilter() inside a!queryLogicalExpression(), which makes it possible to combine multiple filters. This isn't required since we only have one filter, but it makes it easier for you to add more filters and edit it in the query editor.

This example should return the value 555-987-6543.

To retrieve data for more than one field, you can add more a!queryColumn()s to the columns array.

Get the distinct values of a field

Goal: Retrieve the unique list of values in a given field.

It will almost always be significantly faster to have the data source do the uniqueness calculation before returning the data to Appian. This is especially true for large data sets. a!queryEntity() lets the data source perform the uniqueness calculation.

In this example, we are going to retrieve the list of departments that have employees using a!queryAggregation().

Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

a!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    aggregation: a!queryAggregation(
      aggregationColumns: {
        a!queryAggregationColumn(
          field: "department",
          isGrouping: true
        )
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1,
      sort: a!sortInfo(
        field: "department",
        ascending: true
      )
    )
  )
).data.department

This example should return a list containing "Engineering", "Finance", "HR", "Professional Services", and "Sales". Note that even though there is more than one employee in many of these departments, each department is only listed once in the result.

Aggregating on a field

Goal: Perform an aggregation or computation on all values of field.

In this example, we are going to count the number of employees in each department.

Expression

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

a!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    aggregation: a!queryAggregation(
      aggregationColumns: {
        a!queryAggregationColumn(
          field: "department",
          isGrouping: true
        ),
        a!queryAggregationColumn(
          field: "department",
          alias: "numberOfEmployees",
          aggregationFunction: "COUNT"
        )
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1,
      sort: a!sortInfo(
        field: "department",
        ascending: true
      )
    )
  )
)

This example should return one dictionary for each department where the keys in the dictionary are department and numberOfEmployees and the values match the following table.

department numberOfEmployees
Engineering 6
Finance 4
HR 2
Professional Services 4
Sales 4

Aggregating on year and month

Goal: Perform a time aggregation on all values of field.

In this example, we are going to count the number of employees that started on a specific month and year.

Expression

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

a!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    aggregation: a!queryAggregation(
      aggregationColumns: {
        a!queryAggregationColumn(
          field: "startDate",
          alias: "year_startDate",
          isGrouping: true,
          groupingFunction: "YEAR"
        ),
        a!queryAggregationColumn(
          field: "startDate",
          alias: "month_startDate",
          isGrouping: true,
          groupingFunction: "MONTH"
        ),
        a!queryAggregationColumn(
          field: "id",
          alias: "idCount",
          aggregationFunction:"COUNT"
        )
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1
    )
  ),
  fetchTotalCount: true
)

This example should return a list of dictionaries for each distinct year and month combination with the count of the employees that have a start date in that month and year.

Querying on multiple conditions

Goal: Retrieve data that meets at least one of two different conditions.

The only way to find entries that match at least one of two conditions is to run two different queries and combine the results. Using a logicalExpression inside the Query object, we can execute the same logic in a single call to the data source, resulting in faster performance.

In this example, we are going to retrieve the names of employees who either started within the last 2 years or have the word "Associate" in their title.

Expression

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

a!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    selection: a!querySelection(
      columns: {
        a!queryColumn(field: "firstName"),
        a!queryColumn(field: "lastName")
      }
    ),
    logicalExpression: a!queryLogicalExpression(
      operator: "OR",
      filters: {
        a!queryFilter(
          field: "startDate",
          operator: ">",
          value: date(year(now())-2, month(now()), day(now()))
        ),
        a!queryFilter(
          field: "title",
          operator: "includes",
          value: "Associate"
        )
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1
    )
  )
)

The exact list of results that is returned will to depend on when you run the example:

  • Before Jan 2, 2015: John Smith will be the only employee returned because of the start date condition.
  • On or after Jan 2, 2015: no employees will be included in the results because of their start date.

Elizabeth Ward, Laura Bryant and Stephen Edwards will be included in the result regardless of when you run the example as they are included because their title contains the word Associate.

Querying on nested conditions

Goal: Retrieve data based on complex or nested conditions.

In this example, we are going to retrieve the names of the senior members of the Engineering department where "senior" is defined as either having a title of "Director" or having a start date of more than 10 years ago.

Expression

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

a!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    selection: a!querySelection(
      columns: {
        a!queryColumn(field: "firstName"),
        a!queryColumn(field: "lastName")
      }
    ),
    logicalExpression: a!queryLogicalExpression(
      operator: "AND",
      filters: a!queryFilter(
        field: "department",
        operator: "=",
        value: "Engineering"
      ),
      logicalExpressions: {
        a!queryLogicalExpression(
          operator: "OR",
          filters: {
            a!queryFilter(
              field: "startDate",
              operator: "<",
              value: date(year(now())-10, month(now()), day(now()))
            ),
            a!queryFilter(
              field: "title",
              operator: "includes",
              value: "Director"
            )
          },
          ignoreFiltersWithEmptyValues: true
        )
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1
    )
  )
)

This example should return John Smith and Mary Reed. John Smith is included because he is a Director and Mary Reed is included because her start date is more than 10 years ago. Both of them are in the Engineering department.

Filtering for null values

Goal: Find entries where a given field is null.

In this example, we are going to find all employees who are missing either firstName, lastName, department, title, phoneNumber, or startDate.

Expression

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!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    selection: a!querySelection(
      columns: {
        a!queryColumn(field: "firstName"),
        a!queryColumn(field: "lastName")
      }
    ),
    logicalExpression: a!queryLogicalExpression(
      operator: "OR",
      filters: {
        a!queryFilter(field: "firstName", operator: "is null"),
        a!queryFilter(field: "lastName", operator: "is null"),
        a!queryFilter(field: "department", operator: "is null"),
        a!queryFilter(field: "title", operator: "is null"),
        a!queryFilter(field: "phoneNumber", operator: "is null"),
        a!queryFilter(field: "startDate", operator: "is null")
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1
    )
  )
)

This example does not return any results because none of the employees in our sample data are missing any of the specified fields.

Filtering for null or empty values

Goal: Find entries where a given field is null or empty.

In this example, we are going to find all employees who have a missing or empty value for firstName.

Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

a!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    selection: a!querySelection(
      columns: {
        a!queryColumn(field: "firstName")
      }
    ),
    logicalExpression: a!queryLogicalExpression(
      operator: "OR",
      filters: {
        a!queryFilter(field: "firstName", operator: "is null"),
        a!queryFilter(field: "firstName", operator: "in", value: {""})
      }
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1
    )
  )
)

This example does not return any results because none of the employees in our sample data have a null or empty value for firstName.

Searching on multiple fields

Goal: Retrieve data based on search criteria specified by end users e.g. when looking for employees by last name, title, or department. Search criteria that are left blank are not included in the query.

For an example on filtering for null values, see the recipe: Filtering for Null Values.

Expression

First, create an expression rule ucSearchEmployees with the following rule inputs:

  • lastName (Text)
  • title (Text)
  • department (Text)
  • pagingInfo (Any Type)

Enter the following definition for the rule:

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

a!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    logicalExpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "lastName",
          operator: "includes",
          value: ri!lastName
        ),
        a!queryFilter(
          field: "title",
          operator: "includes",
          value: ri!title
        ),
        a!queryFilter(
          field: "department",
          operator: "=",
          value: ri!department
        )
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: ri!pagingInfo
  ),
  /* The fetchTotalCount parameter should be set to true when the totalCount returned */
  /* in the datasubset is required. This example assumes the result will be used in a */
  /* grid where totalCount is required for paging. */
  fetchTotalCount: true
)

Test it out

Unlike the recipes above, this one is a rule with inputs. Rather than just getting a single result, let's take a look at several different results for different rule inputs.

First, let's try not specifying any fields except for pagingInfo:

1
2
3
4
5
6
7
8
9
10
11

  rule!ucSearchEmployees(
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 30,
      sort: a!sortInfo(
        field: "lastName",
        ascending: true
      )
    )
  )

This query will return the first 30 employees, sorted A-Z by last name.

Next, let's try specifying a department in addition to the pagingInfo:

1
2
3
4
5
6
7
8
9
10
11
12

  rule!ucSearchEmployees(
    department: "Sales",
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 30,
      sort: a!sortInfo(
        field: "lastName",
        ascending: true
      )
    )
  )

This expression will return a list of employees in the Sales department, sorted alphabetically by last name. In this example, that is: Angela Cooper, Laura Bryant, Stephen Edwards, and Elizabeth Ward.

searching on multiple fields query recipe

We can also combine multiple filters together. Let's try searching by both last name and department:

1
2
3
4
5
6
7
8
9
10
11
12
13

  rule!ucSearchEmployees(
    lastName: "Bryant",
    department: "Sales",
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 30,
      sort: a!sortInfo(
        field: "lastName",
        ascending: true
      )
    )
  )

This expression will return a list of employees that are in the Sales department and have a last name that contains Bryant. In this case that's a single employee: Laura Bryant.

To see an example of integrating this query into an interface, see the Interface Recipe: Searching on Multiple Fields

Sorting on multiple fields without using aggregations

Goal: Demonstrate how to sort on multiple columns when there is no field aggregation.

Using the a!querySelection function allows you to define a set of column selection configurations. When using this function, you can sort on any of the fields in the data entity, whether the fields are included in the selection or not.

In this example we are going to retrieve the department, first name, and last name of employees and sort them in ascending order. The data will be sorted first by department, and then by title, even though title is not part of the query selection.

Expression

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!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    selection: a!querySelection(columns: {
      a!queryColumn(field: "department"),
      a!queryColumn(field: "firstName"),
      a!queryColumn(field: "lastName"),
    }),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 20,
      sort: {
        a!sortInfo(
          field: "department",
          ascending: true
        ),
        a!sortInfo(
          field: "title",
          ascending: true
        )
      }
    )
  ),
  /* The fetchTotalCount parameter should be set to true when the totalCount returned */
  /* in the datasubset is required. This example assumes the result will be used in a */
  /* grid where totalCount is required for paging. */  
  fetchTotalCount: true
)

Sorting on multiple fields when aggregation is applied

Goal: Demonstrate how to sort on multiple fields when aggregation is performed in one or more fields.

In this example, we are going to retrieve the count of employees by department and title. We are also going to sort the results in ascending order; first by department, then by title.

Expression

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!queryEntity(
  entity: cons!EMPLOYEE_ENTITY,
  query: a!query(
    aggregation: a!queryAggregation(aggregationColumns: {
      a!queryAggregationColumn(field: "department", isGrouping: true),
      a!queryAggregationColumn(field: "title", isGrouping: true),
      a!queryAggregationColumn(field: "lastName", aggregationFunction: "COUNT"),
    }),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 20,
      sort: {
        a!sortInfo(
          field: "department",
          ascending: true
        ),
        a!sortInfo(
          field: "title",
          ascending: true
        )
      }
    )
  )
)

Note:  When using a!queryAggregation(, you can only sort on fields that are part of the query aggregation. Unlike when using a!querySelection(), you must aggregate on a field if you want to sort by it.

Feedback