Overview

Queries are powerful tools that help you access your data. The query editor is an intuitive interface that makes creating queries a lot simpler. This article explains how to use the query editor to create an a!queryEntity().

• If you're new to queries, see Querying Data From an RDBMS
• For detailed information about the capabilities and limitations of the query editor, see Query Editor
• For examples of queries, see Query Recipes

There are six basic steps to creating a query:

1. Select the data store entity that you want to query.
2. Create or update rule inputs and test values to pass information into the query.
3. Set up the paging and sorting to limit the amount of rows returned and order them.
4. Create filters to limit the results to the conditions you specify.
5. Select the fields to include in the query or group the data.
6. Test the query to make sure it is returning the desired information and configure the fields if necessary.

Open the Query Editor

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.

To open the query editor, in the expression editor of a rule or interface:

• Ctrl+Click (Command+Click for Mac) the a!queryEntity() function.

OR

• Place your cursor within the a!queryEntity() function and click the query editor icon, or press Alt+V (Command+Option+V for Mac). Note: If the rule or interface is empty, you can just click the icon.

Select a Data Store Entity

In order to query a data store entity, you must first create a constant of type Data Store Entity that points to it. See Constants for additional information.

To select a data store entity:

• Start typing the name of the constant in the Data Store Entity picker and select the constant that points to the desired data store entity.

After you select a constant, a couple of rows of data display so you can view all of the fields in your data store entity.

Create Rule Inputs

If you want to use rule inputs for any of the settings in the query editor, you can create new rule inputs or edit existing ones directly from the query editor. You can use the query editor to change the test values of your rule inputs to test and troubleshoot your query.

To create a rule input:

1. At the top of the query editor, click RULE INPUTS.
2. Click Add New Rule Input.
3. Configure the Name, Type, and whether or not it is an Array.
4. Enter an Expression or Value to use for testing the query and click Save.

Modify Variable Test Values

Interfaces typically have many different variables and their state can change as you interact with them. With the query editor you can configure the test values for local variables and function variables. The gives you the flexibility to test your queries at any point in time.

These test values will only apply the values while in the query editor; after you click GENERATE QUERY, any changes you make will be reset.

To update a test value for a variable:

1. At the top of the query editor, click VARIABLES.
2. Edit the Test Value for the local or function variable and click OK.
3. You can also click USE DEFAULT TEST VALUES to reset the variables to their original values.

Configure Paging and Sorting

The query editor allows you to select the paging and sorting parameters in Basic mode using configurable fields. You can set limits on the amount of rows you retrieve and determine the order of the rows with paging and sorting.

To configure the page size:

1. To change the number of rows to retrieve, enter a different number in Rows Per Page.
2. To retrieve all data, select All Rows.

To configure how the data is sorted:

1. In the Sort By field, select a field from the picker.
2. Select either In Ascending Order or In Descending Order.

3. To add another field to sort by, click Add Sort.

   

If you need more advanced paging and sorting settings, select Expression to configure additional settings. For additional information on specifying paging and sorting as an expression, see a!pagingInfo().

Create Filters

Filters allow you to select which results to return from your query based on conditions you specify. The query editor allows you to easily set up filters using four criteria: Field, Condition, Value, and Apply Filter.

If you want skip filters that have an empty value, select the Ignore Filters with Empty Values check box. See a!queryLogicalExpression() for more information on ignoring filters with empty values.

To create a filter:

1. Click Add Filter to add a new filter row.
2. From the Field picker, select the field that you want to use to filter the data.
3. From the Condition list, select the operator to apply to the filter.
4. Use the context menu next to the Value field to select how you want to pass the value into the filter.
   • The options in this menu change based on the data type of the field selected. In most cases, you can enter a value directly, choose a rule input or variable, or enter the value using an expression. For date and date and time data types, you can choose from a list of date presets.
5. To apply conditional filters, in the Apply Filter column click the condition link (shown as either Always or When). Enter an expression that will result in either true or false and click OK.
   • Note: Use Apply Filters in situations where you want to apply the filter only if a particular condition is met. For example, if the user belongs to a specific group.
6. If you have more than one filter, use the up and down arrows to change the order in which the filters are applied. You can also delete a filter by clicking X.

See the following image for the location of each step in the user interface.

If you need more advanced filters, select Expression to create your filters manually. For additional information on specifying filters as an expression, see a!queryFilter().

Select Fields

The Fields section allows you to select which fields will be returned from your query. By default, the query editor fetches all fields so designers can see a query preview with all available fields. However, for performance reasons, you should only query the fields that you need. If you query all of the fields, it may take longer for your query to run.

To add or remove fields in your query:

• To remove a field from the query, in the Query Results Preview section, click the column's context menu , then select Remove.
• To add a field to the query, in the Fields section, click the name of the field.
  • Note: If your data is grouped, you can add a column to the query more than once.
• You can also click REMOVE ALL or ADD ALL to remove or add all fields.

Configure Fields

You can use the query editor to arrange the order of the fields, define aliases for fields, and to configure visibility.

When grouping data, aliases are automatically created for calculated columns. Edit Alias allows you to edit these aliases however you want. You can also change the alias for any field using this feature.

Visibility defines whether or not to show a column based on if a particular condition is met. For example, if the user belongs to a specific group.

To configure the fields:

• In the Query Results Preview section, click the column's context menu .
  • To change the order of the fields, select Move Right or Move Left.
  • To edit or add an alias to a field, select Edit Alias and enter a new name.
  • To configure whether or not a field appears, select Set Visibility and enter an expression that will result in either true or false.
    • Note: If false, the data for the field will not be retrieved from the database. An icon will display next to the column header and the data for the column will not display.

Preview the Query

You can preview the results of your query by clicking TEST QUERY. 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 by the query.

If the query filters out all data, Query returned no data displays.

Group the Data

The query editor allows you to group your data and perform calculations with it. You can perform sum, average, minimum, maximum, or count calculations, as well as group your data by month, year, or date.

Clicking GROUP DATA removes all fields from thequery but keeps the values for the Paging & Sorting and Filters sections.

To group your data:

1. Click GROUP DATA.
2. Select the field that you want to group by.
3. In the Fields section, select another field to perform a calculation with. Usually this is a field that contains numbers or dates.
4. Click the context menu to select a calculation to apply to the columns.

   • If you select a column with a Number (Integer) or Number (Decimal) data type, you can choose to calculate sum, average, min, max, or count.

     

   • If you select column with a Date or Date and Time data type, you can choose to group by month or year.

     

When your data is grouped, you can select a field more than once. For example, you can add a field with a Date data type twice to group by year then month.

The fields display an icon that indicates which fields are used for grouping, as well as which calculations are used in numeric fields.

After the data is grouped, REMOVE GROUPING displays next to the TEST QUERY. Clicking this button will remove any grouped or calculated fields. All filters will remain.