---
alias: user-guide-queries
description: "This documentation outlines how to create and manage queries for retrieving data based on specific criteria within the system"
---

# Queries

:lock: 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.

![queries00](./images/Queries00.png)

The folder structure on the left side allows you to:

* Create New Folder
* Edit a Folder
* Delete a Folder

<!-- Video filename: query_create_new_folder.mp4 -->
{% set video_id = 'bbee23f8712033d1510775f2c57e93d7' %}
{% include-markdown 'includes/cloudflare_stream.md' %}

!!! info
	  Private queries are displayed in the list with a distinctive marker. They are visible only to the user who created the query or to users in the Administrators special group. Editing the query or changing its visibility to public is restricted to the creator and members of the Administrators group. Similarly, only the creator can change the visibility of a query from public to private.

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.

![queries02](./images/Queries02.png)

## Creating a Query

:lock: Query.**Create**

:lock: 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.

![queries03](./images/Queries03.png)

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

![queries04](./images/Queries04.png)

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

<!-- Video filename: query_add_filter.mp4 -->
{% set video_id = '8dd9f813e85b5a6429870611eff79462' %}
{% include-markdown 'includes/cloudflare_stream.md' %}

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.

    ![queries06](./images/Queries06.png)

    !!! 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 [[user-guide-queries#query-parameters|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).

![Screenshot showing a query with shortcuts, including "ec A 4" and "B dewevecenacutey".](./images/query_shortcuts.png)

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

![queries07](./images/Queries07.png)

!!! info
	  After saving, the Query becomes available in the Business Data for the entity Type it applies and on the specified path. The different Queries appear under the Query Selection.

<!-- Video filename: entity_query.mp4 -->
{% set video_id = '0443b0b71b5b424bf736c5d0be6e5861' %}
{% include-markdown 'includes/cloudflare_stream.md' %}

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.

<!-- Video filename: query_column_options.mp4 -->
{% set video_id = '9cddc635fcefe10f86a7a3e2c2fd9778' %}
{% include-markdown 'includes/cloudflare_stream.md' %}

!!! note
    When you select a column that is a relation to another object (for example, `LastProcessedResourceName` in Material is related to Resource), the GUI will let you select what columns you would like to see from the destination object.

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

![queries10](./images/Queries10.png)

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

![queries12](./images/Queries12.png)

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

![queries11](./images/Queries11.png)

!!! note
	  May also be useful to look at the [[tutorials-opendataprotocol]] tutorial.