The Wiser dataselector

With the data selector tool, it is possible to easily make selections on the data that can be managed in Wiser and its data model. Using the data selector reduces the need to write complex MySQL queries. In this manual we describe every part of the dataselector.

Entity choice

The first part of the data selector is the entity choice. Therefore, the first step in creating a data selector is to choose an entity that will serve as the basis for the retrieved data. For example, the entity 'customer'.

Scopes

Scopes are similar to the WHERE statement of an SQL query. The properties that are available are all properties of the selected entity. Multiple scopes can be created by clicking on "Scope toevoegen" or by clicking on the "OF/OR" button visible with each scope. So the "Scope toevoegen" button adds an "AND" scope, and the "OF" button adds an "OR" scope.

Conditions

The condition of a scope determines how the entered value must satisfy the value of the property. There are several conditions, and the names will speak for themselves. There are a few notes, however:

• The "is empty" and "is not empty" options also treat NULL values as empty.
• It is possible to place request variables by the values, for example {articleId}. This is desirable if the created data selector is used in combination with the DataselectorParser component in the GCL. Thus, dynamic values can be retrieved based on a request parameter (as in /getArticle/?articleId=1).
• There are a number of fixed substitutions that can be used:{NowYear} - Current year.{NowMonth} - Current month (1 through 12).{NowDay} - Current day (1 to 31).{NowHour} - Current hour (0 to 23).{NowMinute} - Current minute (0 to 59).{NowSecond} - Current second (0 to 59).{NowMysqlDate} - Current date, in the format YYYY-MM-DD.{NowMysqlTime} - Current date and time, in the format YYYY-MM-DD UU:MM:SS.Note that "current" means the time at the time of execution, not the time when the data selector is set. For "is equal to" and "is unequal to", the special values "Current date" and "Current date and time" are equal to {NowMysqlDate} and {NowMysqlTime}.For an explanation of the key button, see the "Field editor" section later in this wiki.

Data selection

In the "Selecteer benodigde gegevens" section, the properties of the entity to be retrieved can be chosen. This is similar to the SELECT section of an SQL query. A selected entity can also be clicked to open the "field editor. The "Field editor" section explains more about this editor.

Underlying and parent objects

To retrieve data from entities linked to the previously selected entity (for example, contacts to a relation), underlying and parent objects can be used. This is similar to JOIN statement in an SQL query. With parent and underlying objects, scopes can again be set, and even more parent and subordinate objects to join even deeper. For example, joins can be made on: relation " invoice " invoice line.

The option "Optioneel" turns the JOIN into an optional LEFT JOIN, in which case a lower or upper object does not have to exist.

Defining the underlying and parent entity types must be done in the wiser-link table. There it is predefined how certain types are linked together and with what type number.

Grouping

As in a SQL query, it is possible to group data by certain values. Grouping can be done only on the chosen fields of the main entity and of the chosen fields of lower and upper objects. The chosen options can be dragged to determine the order. If you have chosen grouping, it is also possible to aggregate (add up) the retrieved values based on the grouping key. For example; to retrieve the total outstanding amounts of all customer invoices

Filtering the resultset (HAVING)

The having section works the same as the scopes, but is placed in the HAVING section of the SQL query. Thus, this makes it delayed, but this may be necessary in certain cases to load the correct data.

Sorting

Setting sorting works the same as grouping. Clicking on a selected field sets the direction, ASC or DESC. These fields can also be dragged to set the sort order.

Limit

A limit can be set, and this is the same as MySQL's LIMIT statement. This can simply be a number to retrieve the first X number, or it can be a comma-separated value as in a MySQL query, for example, "60.30." In that example, the first 60 records are skipped, and 30 records are returned. So that would be equivalent to LIMIT 60,30.

Options for a saved dataselector

Load data unsecured

This checkbox ensures that the data returned does not need to be secured. This is intended only for data that is publicly available. Think of a data selector intended to retrieve a menu structure. This also only works if the data selector is loaded by ID, and thus will not work if the JSON is directly passed along, otherwise the security is too easy to bypass.

Make available in export module

This checkbox ensures that this data selector (provided it is saved) is also available as a choice in Wiser's export module. The data can then be exported as an Excel file upon request.

Make available in communication module

This checkbox ensures that this data selector (provided it is saved) is also available in the communication module to retrieve recipients or content.

Make available for rendering

This check mark ensures that this data selector (if saved) is also available as a basis for dynamic content in web pages. Thus, you can combine a data selector and a template to include dynamic information easily and with specific layout in a web page.

Show on dashboard

This checkbox ensures that the result of this data selector (provided it is saved) is also shown in the Wiser dasboard. This requires that this data selector returns only 1 value, for example the number of new customers of the past week.

Make available during branch creation

Allows you to create branches that contain only specific data. In this way you can, for example, check all customer data in a branch for the newest customers and edit them safely.

Linking roles to the data selector

A data selector can also be called via the Wiser API, however, this requires authentication (see the Wiser API documentation). Authentication also reveals the role of the user from Wiser. Here you specify whether and, if so, which roles can access the data from this data selector via the API. In this way, you create an API endpoint for JSON data within Wiser without code.

Field editor

The field editor can be used to give a field additional properties.

These include setting an alias, specifying the format in which you want to return something (think of the format of a monetary amount, or a date), and aggregation, such as the sum of a property, the average value, etc.

The "Is item ID" checkbox is intended to return properties that have an ID of another field stored as a different value. For example: A field contains the ID of a color, then with "Is item ID" it can be set that this ID equals a color entity, and the name of that entity can then be retrieved.