Query Editor

Overview

Queries are complex, yet 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 your queries, along with giving you the ability to easily test and troubleshoot your queries 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

You can open the query editor from the expression rule toolbar of any empty rule, as well as any rule that contains a single a!queryEntity() function by clicking the query editor icon Query Editor Icon. See Supported Logic and Parameters for more information on what types of queries are supported.

Query Editor Icon

The query editor is made up of five main sections. Configuring each section and then clicking GENERATE QUERY will create a query 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 expression parameters.

Call Out Letter Query Editor Section Name Associated Function Parameter Associated Function Description
A Source Constant entity a!queryEntity() Selects the data store entity you want to query.
B Paging & Sorting pagingInfo a!pagingInfo() Determines how many rows to return, as well as how to sort those rows.
C Filters logicalExpression a!queryLogicalExpression() Determines which rows of data to return based on conditions you specify.
D Select Fields selection a!querySelection() Determines which fields of the data to return.
E Query Results Preview fetchTotalCount a!queryEntity() Previews the results of the query. Also, Fetch total row count in this section determines whether or not to retrieve the total number of rows.

Source Constant

After launching the query editor, select the constant that points to the data store entity you want to query from the picker. This picker only shows constants that point to valid data store entities.

After a valid data store entity is selected, the main sections of the query editor display, including a preview of the data in the Query Results Preview section.

You can use the Source Constant picker along with the Select Fields or Query Results Preview sections to help you browse for the correct data store entity. After you select a data store entity, you can quickly check to see if it is returning the correct fields and data that you want to query.

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.
  • 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.

Paging & Sorting

Paging and sorting sets limits on the amount of rows returned by your query and determines the order of the rows. You can configure these settings using either Basic or Expression mode.

Switching between Basic and Expression mode will not clear the current configurations. This allows you to use Basic mode to set up your initial paging and sorting 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 paging and sorting 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.

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 one field 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 sort by more than one field.

Filters

Filters allow you to determine which rows of data to return based on conditions you specify. You can configure these settings in By Field mode or in Expression mode.

Switching between By Field and Expression mode will not clear the current configurations. This allows you to use By Field mode to set up your initial filters, then switch to Expression mode to add more advanced filters, such as using nested logical expressions and combining filters with OR instead of AND.

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

By Field Mode: Filters

Selecting By Field 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 list 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 rule inputs 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.

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 =, <>, >, >=, <, <=, between, in, not in, is null, not null
Date, Date and Time, Time =, <>, >, >=, <, <=, between, in, not in, is null, not null
Boolean =, <>, in, not in, is null, not null

Value

Configuring a filter’s value can be done with a rule input or in an expression editor. Existing rule inputs will appear in the Value list. To create a new rule input or edit an existing one, in the Query Results Preview section click CONFIGURE RULE INPUTS. See Using the Query Editor for more information on configuring rule inputs.

The between operator requires 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 that contains a list of two values, or create a list of two values in expression mode.

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

  • Rule inputs that have the same basic data type as the selected field
  • Rule inputs that have Appian data types that can cast to the same basic data type as the selected field
    • Note: If the Condition column is set to is null or not null, the value column is disabled.

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 configure more complex filters, you can configure them manually without closing the query editor. Simply select Expression and an expression editor box will display where you can input an a!queryLogicalExpression function. This can be useful in certain situations, such as if you want to use OR to combine filters instead of AND.

Select Fields

The Select Fields column contains a field browser that shows all of the fields in the data store entity. If there is a nested data type, the name of of the nested data type displays as a field name, but the fields within the nested data type cannot be selected.

Note: Field names that include special characters in your data store entity cannot be configured using the query editor and will not display.

You can use the check boxes to select or clear the fields that you want your query to return. Selecting a column will add it to the Query Result Preview section as a column header. 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.

There is a CLEAR ALL FIELDS button in the Query Results Preview section to remove all of the fields from the query. The ADD ALL FIELDS will add all fields back to the query.

If all of the fields are selected, the selection parameter will not be generated in the resulting query.

Query Results Preview

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

The CONFIGURE RULE INPUTS button allows you to create rule inputs and change their test values without having to close the query editor. See Using the Query Editor for more information on configuring rule inputs.

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.

You can also change the way the table is sorted by clicking on the column in the header row to sort by that field. This will apply the sort to the query, not just the preview.

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().

The query editor can be opened from any new expression rule, or any expression rule that contains a single a!queryEntity() function, if the a!queryEntity() function meets the following requirements:

  • The selection parameter contains a list of columns.
  • The query does not use an aggregation parameter.

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 parameters must meet the following requirements:

  • The sort parameter must not contain an array.
  • 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 By Field mode, the Expression mode will open by default. In order to open in By Field mode, the logicalExpression parameter must meet the following requirements:

  • The a!queryEntity() function must use the logicalExpression parameter, not the filter parameter.
  • The logical expressions must be combined using the AND operator.
  • It cannot contain nested logical expressions.
  • It cannot contain any additional non-query functions such as if(), where(), etc.
FEEDBACK