Query Editor

Overview

Queries are powerful tools that help you access data from your processes and interfaces. The query editor simplifies creating queries by guiding you through the process of creating them, along with giving you the ability to easily test and troubleshoot them all in one easy-to-use interface.

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.

Query Editor Basics

Opening the Query Editor

You can open the query editor from the following locations:

Where How
Expression editor of any empty rule or interface Click the query editor Query Editor Icon icon.
Expression editor of any empty rule or interface Press Alt+V (Command+Option+V for Mac).
Expression editor of any rule or interface that contains an a!queryEntity() function* Ctrl+Click (Command+Click for Mac) the a!queryEntity() function.
Expression editor of any rule or interface that contains an a!queryEntity() function* Place your cursor anywhere in the a!queryEntity() function and click the query editor Query Editor Icon icon.
Expression editor of any rule or interface that contains an a!queryEntity() function* Place your cursor anywhere in the a!queryEntity() function and press Alt+V (Command+Option+V for Mac).
Read-only grid in the Design Mode of an interface Click CREATE QUERY or EDIT QUERY.
The component configuration for local variables in Design Mode of an interface Hover over the variable and click the query editor Query Editor Icon icon.

*If there are any errors in the expression, the query editor will not open.

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

Layout

The query editor is made up of four main sections. Configuring each section and then clicking GENERATE QUERY will create an a!queryEntity function in the expression rule.

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 Context Menu Icon. Fetch total row count in this section determines whether or not to retrieve the total number of rows.

Data Store Entity Selection

Before you create a query, you must first point it to the data store entity that you want to get data from. Simply select a constant that points to the desired data store entity. Note that the picker only shows constants that point to valid data store entities.

Make sure that the constant and the data store entity it points to meet the following requirements:

  • The constant points to a valid data store entity.
  • 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.
  • 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.

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

Variables and Rules 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 values, but not save them for use after the rule or interface is closed.

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 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 and variables.

Basic and Expression Mode

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 & 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 & 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 & 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 a run-time 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.

The Field picker allows you to browse all the fields in the selected entity and choose fields that are basic data types.

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 table shows what operators can be applied to each data type in the query editor.

Data Type Valid Operators
Text =, <>, in, not in, starts with, not starts with, ends with, not ends with, includes, not includes, is null, not null
Integer, Float, Time =, <>, >, >=, <, <=, between, in, not in, is null, not null
Date, Date and Time =, <>, >, >=, <, <=, Date Range (between), in, not in, is null, not null
Boolean =, <>, in, not in, is null, not null

Note that for Date and Date and Time data types, between is substituted with Date Range in the query editor drop down 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.

The between operator requires a list of two values, with the lower value as the first item and the upper value as the second. For example: {10,20}. You can set up one rule input or local variable that contains a list of two values, or create a list of two values in expression mode.

Value

If the Condition column is set to is null or not null, the value column is disabled.

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.

Values Selection

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)

Date Presets

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 basic data type of the selected field will display. This includes:

  • Rule inputs and local variables that have the same basic data type as the selected field
  • Rule inputs and local variables that have Appian data types that can cast to the same basic data type as the selected field
    • Note: Empty local variables will display because they don't have a data type.

For example, if you select a field that has a data type of Number(Integer), only rule inputs that are also of type Number(Integer) or rule inputs that can cast to the basic data type Number(Integer), such as Document or Folder, will display. See the table below for some examples of Appian data types that can cast to basic data types.

Basic Data Type Appian Data Type
Text User
List of Text List of User
Integer Document
Integer Folder
Integer Document or Folder
Integer Group
List of Integer List of Document
List of Integer List of Folder
List of Integer List of Document or Folder
List of Integer List of Group

Apply Filter

The Apply Filter decides when the filter in that row is applied to the query. Use the Apply Filter 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 icons represents a different status determined by whether the filter is applied or not applied.

Visual Icon Filter Status
Filter Applied Applied
Filter Not 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.

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.

Filters Section

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, simply 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 depends on the selection order; if you clear a column and re-add it, it will be added as the last column.

By default, the query editor fetches all fields so designers can see a query preview with all available fields. However, for performance reasons, the best practice is to only query the fields that you need.

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.

The query preview only displays 10 rows at a time. This may differ from the number of rows per page in the Paging & Sorting section as it is only intended to be a preview.

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. The query editor uses such a large number to indicate it doesn't know the total.

Configure Fields

You can use the context menu Context Menu Icon 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.

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 rule or interface.

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!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 the a!queryEntity() 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 field parameter for a!queryColumn() is a not a text literal. For example, it is a rule input or local variable.
  • All of the field names contain a special character.
  • An expression used for a parameter in a!queryColumn() or a!queryAggregationColumn() does not evaluate correctly.

Note that these scenarios may not cover every situation in which the query editor will not open.

pagingInfo Parameter

If your expression contains a pagingInfo parameter that cannot be translated to Basic mode, the Expression mode will open by default. In order 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. In order 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