Query Editor

Overview

Queries are the unseen driver of the robust grids, charts, and other reporting features available to users, and the query editor ensures you can quickly and easily develop the interfaces needed for your application.

For example, let's say you need to build a form that lets customers update their billing address. Using the query editor, you can create a query that pre-populates the form with that customer's latest data so they can make the necessary changes. The query editor enables Appian developers to preview their query results and generate powerful queries and provides additional query-building guidance to novice Appian developers.

But queries can do more than just help you present data. They can also make your application simpler and easier to use. If your form captures customer data, you may need to know the customer's preferred pickup store. A basic version of this form lets the user type the store, but it would be easy for users to enter an incorrect or invalid location. Using a query, you can get the current list of stores to populate a dropdown component instead.

You can query a record type to return record data or you can query a database directly:

• For record data, you use the a!queryRecordType() function to get data from a record type that is filtered and sorted based on parameters you set.
• For data store entities, the a!queryEntity() function provides the same features to access and retrieve a specific set of data.

This page describes all of the features of the query editor and outlines its supported functionality and limitations.

For instructions on how to use the query editor, see Using the Query Editor.

Open the query editor

You can open the query editor using any of the following options:

See Supported Logic and Parameters for more information on what types of queries are supported.

Query records

There are many situations when you'd need to query data from your record types.

For example, in a case management application, warehouse employees need to report any safety concerns they have about their work location. As part of that form, you want users to easily pick the warehouse they are associated with. To use that data with a dropdown or radio button component, you'd need to create a query that returns the warehouses in the employee's state.

It's easy to query your records and related records with the query editor. You can use the editor to configure all aspects of your query:

• Select a record type to query.
• Select the fields with data you need to retrieve.
• Aggregate record data by applying groupings and measures.
• Apply filters for targeted results.
• Sort and limit the data so it is organized for your needs.

As you build a query, you can test it at any time and preview the results to verify your configuration.

Select a record type

Once you open the query editor, choose the data source you want to get data from.

To use a record type, select Record Type as the data source, then use the search to select your record type. Note that you cannot select the following record types:

• Service-backed record types that have data sync disabled
• Process-backed record types
• Legacy record types

By default, the Data Preview tab appears showing a snippet of the available data to confirm you've selected the correct record type. Select the Record Type Relationships tab to see the relationships available on the selected record type.

After selecting your record type, the query editor configuration screen appears. This screen provides the tools to select, aggregate, filter, and sort the data, as well as the ability to create rule inputs and preview the data returned by the query. Each section of the query editor corresponds to a different part of the generated query.

For selection queries, the query configuration screen is made up of five main sections:

Aggregation queries are made up of the same five sections as selection queries, however, the fields are configured on the Aggregate tab:

Fields

The fields section contains a field picker that allows you to select any fields from the record type.

If there are any record type relationships, you can click > to expand them and select related record fields to include in the query. The icon next to the related record field name indicates the type of data relationship.

• is a one-to-one relationship.
• is a one-to-many relationship.
• is a many-to-one relationship.

This example shows how to choose both record fields and related record fields for your query.

By default, only the fields from the base record type are selected. You can add or remove individual fields that you want your query to return. You can also add or remove all fields for a record type or a relationship using the checkbox next to the record type or relationship name.

See Using the Query Editor for more information on how to add or remove fields from your query.

The order of the columns in the preview pane changes as you select and remove fields from the list. If you clear a column and re-add it, it will be added as the last column.

Aggregate data

As an alternative to a selection query, the Aggregate section allows you to build an aggregation. In this section, you can create groupings that collect fields by common values and measures that produce a calculated result.

When building an aggregation, you can use any combination of groupings and measures or use only groupings or measures. Imagine you are working with an Order record type that has fields for order status, the total cost, and the sales region. The following examples show how to use various combinations of groupings and measures to aggregate this record data.

• To view the number of orders in each status, the aggregation needs one grouping for status and one measure to count the orders in each group.
• To view the number of orders in each status and the sum of these orders, the aggregation needs one grouping for status and multiple measures (one to count the orders and one to add up the total cost).
• To view the number of orders in each status and each sales region, the aggregation needs multiple groupings (one for status and one for region) and one measure to count the orders for each grouping.
• To view the total number of orders, the aggregation needs no groupings and one measure.

An alias is required for each grouping and measure. The alias is the short name by which the result of the grouping or the measure can be referenced in other parts of the a!queryRecordType() expression.

Rule inputs and variables

Rule inputs and local variables let you define context-specific values in your expression rule or interface.

The query editor parses these rule inputs and local variables so you can use them as part of your query. For example, you might use a rule input or local variable to store the value of a filter. When you open the query editor, the local variables and rule inputs are evaluated immediately.

Guided and expression modes

The Filter and Sort and Limit sections have a Guided mode and an Expression mode.

• Guided mode: Use a simple configuration tool to specify the query's filtering and sorting behavior.
• Expression mode: Use an expression to specify the query's filtering or sorting behavior.

Switching from Guided to Expression mode will not clear the current configurations. This allows you to use Guided mode to set up your initial configurations, then switch to Expression mode to add more advanced configurations.

If you make changes in Expression mode that cannot be made in Guided mode, you must continue in Expression mode since Guided mode will be disabled. See Supported Logic and Parameters for more information on what is supported in Guided mode.

Filter records

The Filter section allows you to determine which records to return based on conditions you specify. Filters can be applied to the entire query or to a measure when building an aggregation.

Guided mode: filters

Selecting Guided allows you to easily set up filters using four criteria: Field, Condition, Value, and Apply Filter. Multiple filters are combined using the AND operator.

Ignore filters with empty values is selected by default. This means a filter will not be applied if its value parameter returns an empty value. This is useful when you have multiple filters that are all optional (for example, a grid that can be filtered by multiple selections).

If you don't select Ignore filters with empty values and the value for a filter is not supplied, the query will return an error.

Field

The Field picker contains all the fields in the record type, as well as related record fields.

The Condition and Value options will change depending on the data type of the selected Field. Only the conditions and values that are compatible with the data type of the selected field will display.

Condition

The Condition list displays all of the operators that can be applied to the selected field. Only operators that are compatible with the data type of the chosen field display.

The following tables explains the behavior of each available operator:

See Query Recipes for an example of how to use a!queryFilter() and a!queryLogicalExpression to query for both empty and null values.

The following table shows which operators can be applied to each data type.

Value

You can select how you want to pass in the filter value using the Value context menu.

The options in the context menu change based on the data type of the selected field. In most cases filter values can be passed in using the following:

• Static value, such as text or integer
• Rule input or local variable
• Expression
• Date presets (for Date and Date and Time data types)

If you select Input/Variable, existing rule inputs and local variables appear in the Value list.

Only rule inputs and local variables that are compatible with the data type of the selected field will display. This includes:

• Rule inputs and local variables that have the same data type as the selected field.
• Rule inputs and local variables that have Appian data types that can be cast to the same data type as the selected field.

For example, if you select a field that has a data type of Text, you can select rule inputs or variables of the same type or those that can be cast to the Text data type. See the list below for some examples of Appian data types that can be cast to primitive data types.

• User can be cast to Text
• List of User can be cast to List of Text
• Document can be cast to Integer
• Folder can be cast to Integer
• Document or Folder can be cast to Integer
• Group can be cast to Integer
• List of Document can be cast to List of Integer
• List of Folder can be cast to List of Integer
• List of Document or Folder can be cast to List of Integer
• List of Group can be cast to List of Integer

Apply filter

The Apply Filter decides when the filter in that row is applied to the query. Use this option only when you want to apply the filter when a particular condition is met, such as if the user belongs to a specific group.

The Apply Filter can be configured in an expression editor by clicking on the link in the Apply Filter column, shown as either Always or When. Always means the filter is always applied, and When means a condition expression exists. Enter an expression that returns a boolean value.

On the left side of each filter row, there is an icon that indicates whether or not the filter is applied based on the Apply Filter column. Each icon represents a different status determined by whether the filter is applied or not applied.

Visual Icon	Filter Status
	Applied
	Not Applied

Ordering filters

On the right side of each filter row, you can change the filter order using the up or down arrows, or delete the filter using the x icon.

Because filters are evaluated with the AND operator, the filter order can affect the speed of the query.

Expression mode: filters

If you need to create more complex filters, you can start in Guided mode, then switch to Expression mode to configure them manually.

Select Expression and an expression editor box will display where you can modify the a!queryLogicalExpression() function.

For example, if you want to use OR to combine filters instead of AND, create all of your filters in Guided mode, then switch to Expression mode and change AND to OR for the a!queryLogicalExpression() operator parameter.

Sort and limit

The Sort and Limit section allows you to determine the order of the records displayed for your query, and constrain the number of records returned.

Guided mode: sort and limit

In Guided mode, you can Sort by one or more fields in either ascending or descending order.

You can also set a Data Limit to restrict the number of records returned by the query.

Expression mode: sort and limit

If you need more advanced sorting or limiting conditions, you can configure them manually without closing the query editor.

Select Expression and an expression editor box prepopulated with the a!pagingInfo() function displays. This can be useful in certain situations, like when you want to use a rule input for your sorting and limiting.

Query results preview

The Query Results Preview section allows you to test and troubleshoot your query directly from the query editor.

From the preview, you can use the context menu to move fields left or right or to remove them from the query. Then, click TEST QUERY to run the current configuration and view the results in the preview grid.

Total row count

The total count displays the number of records returned by your query. If your selected record type has data sync enabled, the total count is returned by default and displayed at the bottom of the preview grid. There is no additional performance cost to get the total count in this case.

If you selected a record type that does not have data sync enabled, the preview grid shows the number of records that will be returned. Once you generate the query, you can set the fetchTotalCount parameter to true.

If you do not need the total count for the query, it is recommended that you do not set this parameter in order to maximize performance.

Query entities

Queries for data store entities are easy to set up with the query editor.

You can use the editor to configure all aspects of your query:

• Select a data store entity to query.
• Select the fields with data you need to retrieve.
• Apply filters for targeted results.
• Set up paging and sorting so the data is organized for your needs.

As you edit a query, you can test it at any time and preview the results to verify your configuration.

Select a data store entity

Once you open the query editor, choose the data source you want to get data from.

To use a data store entity, select Data Store Entity as the data source, then use the search to select a constant referencing your DSE. The Data Preview shows a snippet of the available data to confirm you've selected the correct constant.

When you choose a constant, make sure it meets the following requirements:

• All fields referenced in the query exist in the data store entity and are named the same in the query and the data store entity.
• The data store entity does not contain one or more fields of type encrypted text. All other primitive data types are supported.
• You have permission to view the constant and the data store entity.

Furthermore, you will not be able to edit a query in the query editor if there are no valid fields to configure. This will happen if there are no fields in your data store entity, or if all of the field names in the data store entity contain special characters.

After selecting the constant referencing your data store entity, the query editor configuration screen appears. This screen provides the tools to select, group, filter, and sort the data, as well as a preview of the data returned by the query.

Each section of the query editor corresponds to a different part of the generated query. For example, the information you input into the Filters section will generate the logicalExpression parameter in the query. See the following screenshot and table for a comparison of the query editor sections to the function parameters.

Call Out Letter	Section Name	Function Parameter	Function	Description
A	Paging & Sorting	pagingInfo	a!pagingInfo()	Determines how many rows to return, as well as how to sort those rows.
B	Filters	logicalExpression	a!queryLogicalExpression()	Determines which rows of data to return based on conditions you specify.
C	Fields	selection	a!querySelection()	Determines which fields of the data to return.
D	Query Results Preview	fetchTotalCount	a!queryEntity()	Previews the results of the query and contains field configuration options in the context menu . Fetch total row count in this section determines whether or not to retrieve the total number of rows.

Clicking CHANGE DATA STORE ENTITY will take you back to the data source selection screen. Changing the data store entity will start your query over from scratch.

Rule inputs and variables

The query editor parses rule inputs and local variables that are present in your expression rule or interface so you can use them as part of your query. For example, you might use a rule input or local variable to store the value of a filter. When you open the query editor, the local variables and rule inputs are evaluated immediately so make sure that you initialize them correctly.

In the expression example shown, the ri!user rule input is empty. This will cause a runtime error when the query editor opens.

1
2
3
4
5
6
7
8
9
10
 a!localVariables(
   local!user: user(ri!user),
   a!queryEntity(
     entity: cons!SUPPORT_CASE_DSE,
     query: a!query(
       pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 50)
     ),
     fetchTotalCount: false
   )
 )

To resolve this issue, make sure you create and save a test value for ri!user before saving and closing the expression.

The following sections describe how to create and save test values for rule inputs and local variables.

Creating test values for rule inputs

The RULE INPUTS button at the top of the query editor allows you to create rule inputs and change their test values without having to close the query editor. In order to save the rule input test values for future use, you must save the rule or interface. The query editor can change the test values, however it cannot save the test values for use after closing the rule or interface.

Evaluating test values for local variables

If your expression contains a local or function variable, a VARIABLES button will display next to RULE INPUTS. VARIABLES allows you to edit the test values of local variables and function variables. These test values are not saved to the rule or interface; they are there as a convenience so you can test your queries with different values.

See Using the Query Editor for more information on configuring rule inputs and variables.

Basic and expression modes

The Paging & Sorting and Filter sections have a Basic mode and an Expression mode.

Switching from Basic to Expression mode will not clear the current configurations. This allows you to use Basic mode to set up your initial configurations, then switch to Expression mode to add more advanced configurations.

If you make changes in Expression mode that cannot be translated to Basic mode, you will not be able to use Basic mode to make further changes. When you open an existing query in the query editor that has information that cannot be translated in Basic mode, Expression mode will open by default. See Supported Logic and Parameters for more information on what is supported in Basic mode.

Paging and sorting

Paging and sorting sets limits on the amount of rows returned by your query and determines the order of the rows.

Basic mode: paging and sorting

In Basic mode, you can either retrieve all rows, or you can limit the amount of rows returned per page to a certain number. You can also sort on multiple fields in either ascending or descending order.

Expression mode: paging and sorting

If you need more advanced paging and sorting settings, you can configure them manually without closing the query editor. Simply select Expression and an expression editor box prepopulated with the a!pagingInfo() function displays. This can be useful in certain situations, such as if you want to use a rule input for your paging and sorting.

Filters

Filters allow you to determine which rows of data to return based on conditions you specify.

Basic mode: filters

Selecting Basic allows you to easily set up filters using four criteria: Field, Condition, Value, and Apply Filter. Multiple filters are combined using the AND operator.

Ignore filters with empty values is selected by default. When selected, a filter will not be applied if its value parameter returns an empty value. This is useful when you have multiple filters that are all optional (for example, a grid that can be filtered by multiple selections). If you don't select Ignore filters with empty values and the value for a filter is not supplied, the query will return an error.

Field

The Field picker contains all the fields in the data store entity, including nested data types. The Condition and Value options will change depending on the data type of the selected Field. Only the conditions and values that are compatible with the data type of the selected field will display.

Field names that include special characters in your data store entity cannot be configured using the query editor and will not display. Likewise, if your data contains nested data types, only fields with one-to-one or many-to-one relationships will display. Fields with one-to-many relationships are not displayed in order to avoid run time errors.

Condition

The Condition list displays all of the operators that can be applied to the selected field. Only operators that are compatible with the data type of the chosen field display.

The following tables explains the behavior of each available operator:

See Query Recipes for an example of how to use a!queryFilter() and a!queryLogicalExpression to query for both empty and null values.

The following table shows which operators can be applied to each data type.

Note that for Date and Date and Time data types, between is substituted with Date Range in the query editor dropdown list. This is just to indicate that you can select from a list of date presets for the filter's value. The parameter in the expression will still display between.

Value

You can select how you want to pass in the filter value using the Value context menu. The options in the context menu change based on the data type of the selected field.

In most cases filter values can be passed in using the following:

• Static value, such as text or integer
• Rule input or local variable
• Expression
• Date presets (for Date and Date and Time data types)

If you select Input/Variable, existing rule inputs and local variables appear in the Value list.

Only rule inputs and local variables that are compatible with the data type of the selected field will display. This includes:

• Rule inputs and local variables that have the same data type as the selected field
• Rule inputs and local variables that have Appian data types that can be cast to the same data type as the selected field

For example, if you select a field that has a data type of Text, you can select rule inputs or variables of the same type or those that can be cast to the Text data type. See the table below for some examples of Appian data types that can be cast to primitive data types.

• User can be cast to Text
• List of User can be cast to List of Text
• Document can be cast to Integer
• Folder can be cast to Integer
• Document or Folder can be cast to Integer
• Group can be cast to Integer
• List of Document can be cast to List of Integer
• List of Folder can be cast to List of Integer
• List of Document or Folder can be cast to List of Integer
• List of Group can be cast to List of Integer

Apply filter

The Apply Filter decides when the filter in that row is applied to the query. Use this option only when you want to apply the filter when a particular condition is met, such as if the user belongs to a specific group.

The Apply Filter can be configured in an expression editor by clicking on the link in the Apply Filter column, shown as either Always or When. Always means the filter is always applied, and When means a condition expression exists. Enter an expression that returns a boolean value.

On the left side of each filter row, there is an icon that indicates whether or not the filter is applied based on the Apply Filter column. Each icon represents a different status determined by whether the filter is applied or not applied.

Visual Icon	Filter Status
	Applied
	Not Applied

Ordering filters

On the right side of each filter row, you can change the filter order using the up or down arrows, or delete the filter using the x icon.

Because filters are evaluated with the AND operator, the filter order can affect the speed of the query. Generally, it is better to order them so that filters that remove the most options come first.

Expression mode: filters

If you need to create more complex filters, you can start in Basic mode, then switch to Expression mode to configure them manually. Simply select Expression and an expression editor box will display where you can modify the a!queryLogicalExpression function.

For example, if you want to use OR to combine filters instead of AND, create all of your filters in Basic mode, then switch to Expression mode and change AND to OR for the a!queryLogicalExpression() operator parameter.

Fields

The Fields section contains a field picker that shows all of the fields in the data store entity. If there is a nested data type, you can expand it by clicking >.

Note that some fields cannot be configured in the query editor. The following fields will not display:

• For nested data types, fields with one-to-many relationships.
• Fields with names that include special characters in the data store entity.

You can add or remove the fields that you want your query to return. See Using the Query Editor for more information on how to add or remove fields from your query. The order of the columns in the preview pane changes as you select and remove fields from the list. If you clear a column and re-add it, it will be added as the last column.

Query results preview

The Query Results Preview section allows you to test and troubleshoot your query directly from the query editor. The TEST QUERY button populates the Query Results Preview section with the results of your query.

Fetch total row count

If Fetch total row count is selected, a parameter will be applied to the query that will retrieve the total number of rows that are returned. Note that Fetch total row count is applied to the query, not just the preview.

If you have a lot of data, it is recommended to turn off Fetch total row count to improve performance. In some cases, the total row count will be retrieved even if it is not selected. For more information on this, see a!queryEntity().

Normally, Fetch total row count is deselected by default. However, when launching the query editor from a read-only grid in design mode, it is automatically selected and cannot be changed.

When this setting is turned off, the total number of rows at the bottom of the Query Results Preview grid displays 1,000,000 to indicate the total is unknown.

Organize fields

You can use the context menu to move filters left or right, edit aliases, and set the visibility for fields. See Using the Query Editor for more information on how to configure fields.

Group data

You can group your data and perform calculations with it by clicking GROUP DATA. When your data is grouped, the context menu provides additional options for calculating and grouping it. See Using the Query Editor for more information on data grouping.

Supported logic and parameters

The query editor can be opened from any a!queryEntity()function that is a part of a rule or interface that does not have any syntax errors. It can also be opened from an empty expression rule or interface, or an a!queryRecordType() function.

Query editor cannot open scenarios

The following are some scenarios that will prevent the query editor from opening:

• There is an error within the a!queryRecordType() function. For example, an invalid parameter is present or a parameter is missing a required keyword.
• There is an error within the a!queryEntity() function. For example, the query parameter is missing or there is no cons! prefix for the data store entity constant.
• You are trying to open the query editor using the keyboard shortcut or toolbar icon, but the cursor is not within an a!queryEntity() or a!queryRecordType() function.
• The a!query(), a!querySelection(), or a!queryColumn() function is nested inside another function, such as if() or a!forEach().
• The query, selection, or columns parameter is defined by an external rule, such as rule!myExampleQuery().
• The value of the a!queryRecordType() function's fields parameter is an expression.
• The field parameter for a!queryColumn() is not a text literal. For example, it is a rule input or local variable.
• All of the field names contain a special character.
• The entity contains one or more encrypted text fields, which are not supported by the query editor.
• An expression used for a parameter in a!queryColumn() or a!queryAggregationColumn() does not evaluate correctly.
• You don't have permission to view the record type.

pagingInfo parameter

If your expression contains a pagingInfo parameter that cannot be translated to Basic mode, the Expression mode will open by default.

To open in Basic mode, the pagingInfo parameter must meet the following requirements:

• It cannot contain any rule inputs.
• It cannot contain any additional non-query functions such as if(), where(), etc.

logicalExpression parameter

If your expression contains a logicalExpression parameter that cannot be translated to Basic mode, the Expression mode will open by default.

To open in Basic mode, the logicalExpression parameter must meet the following requirements:

• The filters cannot be combined using the OR operator.
• It cannot contain nested logical expressions.
• It cannot contain any additional non-query functions such as if(), where(), etc.

If the a!queryEntity() function uses the filter parameter instead of the logicalExpression parameter, the Filter section will automatically open in Expression Mode.

If you switch to Basic mode, the query editor will wrap your a!queryFilter() in an a!queryLogicalExpression().

Feedback