Queries#

Administration.Queries

Overview#

The Queries page provides access to the maintenance of queries. A Query is an inquiry to the system for a given entity type to return the records that match specific criteria. Queries can also be used as Widgets for Dashboards.

Browsing Queries#

Selecting the Queries page will display the Queries folder structure. Selecting a folder will show its contents in the table on the right.

The folder structure on the left side allows you to:

• Create New Folder
• Edit a Folder
• Delete a Folder

To view the details of a specific Query, simply select its name to display information similar to the example below. Each Query includes an information button that provides the ID, the creation date and creator, as well as the last modification date and the person who made the changes.

Creating a Query#

Query.Create

Query.ManagePrivate

To create a new Query, select the Create button on the top ribbon and then follow the steps below.

Step 1: Details

1. Enter the name and, optionally, a description. Note that the Query name must be unique within the folder.
2. Select the folder where the query should be created.
3. Select the entity type for the query.
4. Select whether this will be the default query for the chosen entity type.
5. Select the maximum number of results.
6. Select whether the distinct attribute is enabled or not. If enabled, the results will not contain duplicated values.
7. Select whether this will be a public or private query.
8. Optionally, enter a Comment.
9. Select Create to complete the operation.

Using a Query#

After the creation of a new Query or when opening an existing one, the Query page is opened. This page is mainly divided into two sections:

• Filters - containing the search properties for the query.
• Results - showing the results returned by the query.

In order to select the filters to apply to the Query, follow the steps below:

1. Expand the Filters section.
2. Select the Add Filter button. This will open the Search Entity Property window.
3. In the Search Entity Property, choose the desired property, and select Confirm.

After adding the desired properties to the filter section, you can:

1. Choose the logical operator to connect the expressions

2. Choose the filter type to apply. The currently supported filters are:

   • Is equal to - finds records with an exact match.
   • Is not equal to - returns records that do not exactly match.
   • Greater than - finds records where the value is greater than the specified value.
   • Greater than or equal to - finds records where the value is greater than or equal to the specified value.
   • Lesser than - finds records where the value is less than the specified value.
   • Lesser than or equal to - finds records where the value is less than or equal to the specified value.
   • Like - finds records matching the search string that can use wildcards (% for any sequence or characters and _ for a single character).
   • Not like - finds records not matching the search string that can use wildcards (% for any sequence or characters and _ for a single character).
   • Contains - finds records that contain a certain string.
   • Starts with - finds records that start with a certain string.
   • In (only for objects) - finds records that are included in a set of user defined objects.
   • Not in (only for objects) - finds records that are not included in a set of user defined objects.

   

   Info

   String comparisons are case-insensitive.

3. Choose to use parameters, by activating the Parameter property. By choosing to use parameters, it also possible to mark it as Optional. For more information, see Query Parameters.

It is possible to use special dynamic tokens in the queries, such as:

• @Today, which represents the current date.
• @Now, which represents the current date and time.

For the case of Today and Now it is possible to add and subtract times using a simple '+' or '-' arithmetic:

• @Today +1d represents the current date plus one day.
• @Today -1M represents the current date minus one month.
• @Today -1y represents the current date minus one year.
• @Now -1m represents the current date and time minus one minute.
• @Now +10s represents the current date and time plus ten seconds.

There are also special tokens available for certain property types:

• @Employee, which represents the current Employee.
• @User, which represents the current User.
• @UserRoles, which represents all the Roles of the current User (can only be used in properties of type Role).

Having done this, it is possible to execute the Query by selecting the Execute Button.

In the results section, it is also possible to choose which columns to show, by selecting the Column Options button. These options may include entity operation attributes if the Query.AdvancedMode security feature is enabled. In this section, it is possible to choose which properties are to be included in the output table, as well as their sort order, how they are to be presented in the Setup Tab, and the different type of joins available in the Joins Tab. These are:

• Inner Join - display only records which exist both on the source and target tables.
• Outer Join - display all records which exist at least in the source or the target.
• Right Join - display all records which exist on the target (right-side) even if they don't exist in the source.
• Left Join - display all records which exist on the source (left-side) even if they don't exist in the target.

Query Parameters#

It is possible to use parameters in a Query. In order to that, you need to flag the filter value as a parameter. When trying to execute a Query with parameters, the system will prompt you for the parameter values. If the parameter is marked as optional, you can choose to ignore it, in which case the filter expression is removed from the query execution.

Query Filters#

A Query can contain one or more filters to restrict the data that is selected. The system also supports nested filter groups. Each filter (or filter group) needs to be connected with an AND or OR logical operator.

Generating Query Code#

It is possible to generate the query code in C# or TypeScript. This option translates the query code in a useful C# or TypeScript code that can be used to programmatically implement the query to be used in the clients code.