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().
There are six basic steps to creating a query:
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:
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.
You can also create a new query from a read-only grid in the Design Mode of an interface.
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:
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.
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:
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:
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:
To configure how the data is sorted:
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().
The default page size of the query is 50 rows per page and the default startIndex parameter is 1. You can change the startIndex parameter in Expression mode.
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:
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().
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:
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:
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.
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.
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.
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.
If the query filters out all data, Query returned no data displays.
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:
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.
On This Page