Each Data Query can return either the extracted Entities directly or a file. If you require both variants, two different Data Queries need to be used.

• A Data Query consists of at least one SQL SELECT query, but can have multiple SQL SELECT query in it. Each SQL SELECT query in a Data Query produces a so called Result Set. Hence, a Data Query returns as may Result Set as it has SQL SELECT queries. You can think of a Result Set as a table.

• The SQL SELECT queries are executed one after another in the same transaction and are allowed to run a combined maximum of 120 seconds. After that, the transaction and therefore the execution of the SQL SELECT queries is canceled and the Data Query fails.

• The SQL SELECT query are executed with the User triggering the Data Query

  • If the Data Query is executed by an Activity then the Data Query is executed by same User as the Activity is executed. Hence, the Data Query might be executed as the Novunex Process Engine user as described here.

  You need to keep this fact in mind, since the Novunex Process Engine user can access all Entities without any restrictions. Hence, your Data Query might leak information.

Data Query Overview

Menu

Data Queries List

Pagination

Data Query Editor

The Data Query Editor allows you to create, modify and test Data Queries.

Menu

Configuration

Editor

SQL for Data Queries

Data Query only support SQL SELECT queries. Here, only the Novunex specifics of SQL SELECT queries are given. Further information about SQL SELECT queries can be found in the Microsoft SQL Server documentation. The SELECT queries used in the Novunex Platform have several extensions of the Microsoft SQL Server queries:

• Entity Type can be used as table names when putting them in square brackets like [MyEntityType].
  • Attributes of an Entity Type can be used like column names
  • If the Track Changes setting of an Entity Type is set to Yes, the Attribute modification history of each Entity is stored in a table with the name of the Entity Type and the suffix _History. For example the history of the Attributes of Entities with the Entity Type [MyEntityType] can be accessed with [MyEntityType_History].
• Core Tables can also be used like table names when putting them in square brackets like [File]
• Vales passed to the SQL SELECT queries as parameters start with a leading @ sign followed by an alphanumeric string., i.g., @MyParameter, @LowerBound, @SerialNumber. The Novunex Platform does not check the parameters. This means if you want to handle special cases like empty parameters, you have to write the SQL SELECT query accordingly.

Here is an example SQL SELECT query that retrieves the two Attributes FirstAttribute and SecondAttribute of the Entity Type [MyEnity] for all Entities where the Attributes ThirdAttribute equals the value of MyParameter

SELECT FirstAttribute, SecondAttribute FROM [MyEnity] WHERE ThirdAttribute = @MyParameter

The Novunex Platform provides you will the following SQL functions in addition to the functions the Microsoft SQL Server already has.

• ConvertDateTime( userId, DateTime) - Converts a UTC DateTime into the local DateTime for the user specified by userId.

Global Variables in Data Queries

Data Queries can access Global Variables in a similar fashion as Process Context Expressions.

SQL Global Variables

Process Context Expression Global Variables

Templates and Reports

The template files define how the results of Data Queries should be formatted inside a file. When the data form a Data Query is filled into a template, a Report is created. Hence, a Report is nothing more that the result of a Data Query formatted by a template and stored in a file.

Different file types of templates supported, but each Data Query can supports one template. I.e., if you require different templates for the same SQL SELECT query, you have to create multiple Data Queries with the same SQL SELECT queries and different templates.

All values are inputted as strings by the Novunex Platform into the template. This means that the strings have to be properly formatted in the Data Query itself, e.g., data and time strings.

Microsoft Word

Microsoft Word templates are DOCX-files that use so called MergeFields to determine where the results of Data Queries are inserted. Layouting has to be done in the Microsoft Word, like usage of different fonts or colors.

For each Result Set in the Data Query a block marked with MergeField must be defined in the Microsoft Word templates. The block corresponding to the first Result Set starts with the MergeField named TableStart:Entity and ends with the MergeField named TableEnd:Entity for independent Result Sets. The block corresponding to the second Result Set starts with the MergeField named TableStart:Entity1 and ends with the MergeField named TableEnd:Entity1. The block corresponding to the third Result Set starts with the MergeField named TableStart:Entity2 and ends with the MergeField named TableEnd:Entity2. Further blocks and Result Sets are handled by continuing the numbering scheme. While the numbering scheme is the same for independent an Result Sets with relations, Results Sets with relations use a different prefix. Each line in the Result Set becomes one line in the block. The columns in the Result Set are addressed by their name. A MergeField with the name of the column is used to access the value that column.

Alphanumerical Results Set

This examples shows how a Result Set is handled by MergeFields in the Microsoft Word templates. The Result Set is described in this table

To corresponding Microsoft Word templates uses the values from the tree columns and rows. To make this example more readable, all MergeField are enclosed in « and » marks. These are not part of the actual MergeField name.

List of delivered parts

«TableStart:Entity»
The «Name» with the part code «PartCode» was delivered «Quantity» times.
«TableEnd:Entity»

We thank you for your purchase!

The values from the Result Set are filled into this template thus creating the final Microsoft Word file.

List of delivered parts

The Nail with the code n123 was delivered 50 times.
The Nut with the code c456 was delivered 35 times.
The Bolt with the code t789 was delivered 15 times.

We thank you for your purchase!

Images in Result Sets

Images stored on the Novunex Platform can also be inserted into the Microsoft Word template. In your SQL SELECT query you have to select the Url column of the File table as in this example

SELECT Url AS FileUrl FROM [File] WHERE Id=1234

Obviously, also other columns can be included in SQL SELECT query, but are omitted here to keep the example simple.

The image is referenced in the Microsoft Word templates by a MergeField field named Image:FileUrl. Be aware, that FileUrl must be replaced with the name of the Url column, if the column is named differently. However, in this example the corresponding Microsoft Word templates in this example could look like this.

Image from the Novunex Platform:

«TableStart:Entity»
«Image:FileUrl»
«TableEnd:Entity»

To make this example more readable, all MergeField are again enclosed in « and » marks.

Relations between Result Sets

When a Data Query has multiple Result Sets, there can be parent-child relation between the Result Sets. E.g., one Result Set describes an invoice with the billed products and the second Result Set holds detailed information about each item on the invoice. To work with the parent-child relation between the Result Sets in the template, each Result Set needs an extra column called CommandMapping. The CommandMapping column in the parent Result Sets needs to be an column of empty strings. The CommandMapping column in the child Result Sets needs to specify the relation linking the two Result Sets. Therefore, an example to retrieve two Result Sets can look like this

--Entity
SELECT Id, InvoiceNumber, InvoiceDate, CustomerId, '' as CommandMapping
    FROM Invoice
    WHERE Id = 1234

-- Entity1
SELECT Product, Description, Quantity, Price, InvoiceId, 'InvoiceId = %Entity.Id%' as CommandMapping
    FROM InvoiceItem
    WHERE InvoiceId = 1234

The first Result Set is always referred to as Entity, the second as Entity1, the third as Entity2, etc. The example above shows how the parent Result Set Enitiy required the empty string column CommandMapping. The child Result Sets Entity1 defines the parent-child relation as the link between the column InvoiceId in Entity1 and the column Id in Entity. Note the percentage signs around the fully qualified name of the column in the parent Result Set Entity.

The data of the Result Sets in inserted into MergFields in the Microsoft Word template. However, the blocks have to marked with the prefix BeginGroup and EndGroup in contrast to to the usual TableStart and TableEnd. Hence, the block corresponding to the first Result Set starts with the MergeField named BeginGroup:Entity and ends with the MergeField named EndGroup:Entity, block corresponding to the second Result Set starts with the MergeField named BeginGroup:Entity1 and ends with the MergeField named TableEnd:Entity1. Further blocks and Result Sets are handled by continuing the numbering scheme. The corresponding Microsoft Word template for the query given above could look like the example below. To make this example more readable, all MergeField are enclosed in « and » marks. These are not part of the actual MergeField name.

List of Invoices

«BeginGroup:Entity»
The invoice «InvoiceNumber» received on «InvoiceDate» and by customer «CustomerId» consisted of:
    «BeginGroup:Entity1»
        «Product» («Description») delivered «Quantity» times at «Price» $.
    «EndGroup:Entity1»
«EndGroup:Entity»

We thank you for your purchase!

Microsoft Excel

Microsoft Excel templates are empty XLSX-files where each Result Set in the Data Query requires one tab in the Excel template. The first Result Set is mapped onto the tab Entity, the second Result Set on Entity1, the third Result Set on Entity2, etc. Each column in the Result Set becomes one column in the corresponding tab. While any existing data in the columns is overwritten, the formatting is kept. Hence, the Excel file template defines the format of the resulting file.

Plain Text

Paint text templates are empty files with a file extension of .utf8. The file name can be chosen arbitrarily, so template.utf8 and plain.utf8 are both valid examples. When using a pain text template, the Result Set is exported into one singe text file with no column names. Each row becomes one line in the text file. The columns in each row are concatenated together without any delimiting character, i.e., if you need a delimiter between columns, you have to insert it in the SQL SELECT statement. Values in array are indicated by square brackets and the values inside the arrays are enclosed by double quotes " and separated by a comma ,. Empty arrays are exported as a opening and closing square bracket [].

This example query exports the three Attributes Count, Name and Dimensions of the Entity MyProduct and separates the columns with a semicolon.

SELECT Count, ';' AS Delimiter1, Name, ',' AS Delimiter2, Dimensions FROM [MyProduct] WHERE Id<=123 AND Id>=456

The Attribute Dimensions is an array of decimal values. Therefore, the first three lines of the resulting file can look like this:

2;test first;["1.2","2.3","3.4"]
6;test second;[]
1;another;["0.2","3.4","5.2"]

More information