Automate

Hi Community,

I need to query a "Reference Data List" with multiple criteria and insert the result aggregated into unique records into a "Repeating Table" element of my form.

To do this, I use a "Query Element" in my form with SQL code as a "Raw Query." The code contains the selection conditions and the SQL statement "GROUP BY."

After unsuccessful research in the Precisely documentation, the Knowledge Community, and the entire Internet, I found out through numerous attempts what the code for the "Query" parameter should look like.

Important: Following conditions must be met!

1. "Raw Query" must be selected.

2. The code in the "Query" property does not contain SELECT, FROM, or WHERE.

3. The syntax follows SQL standards ("=" instead of "==", 'AND' instead of "&&", etc.).

4. In the "Data Connection," all (!) columns of the "Reference Data List" addressed by the "Query" code and the "Set Fields" property must be selected.

5. The GROUP BY statement in the "Query" code must also contain all addressed columns, including those that are only used for selection!

6. The "Automatically retrieve data when form is opened" option can be selected in the "Data Connection", but does not have to be.

7. In the "Query Element," "Append to Repeating Group" does not have to be selected in order to fill the form element with data.

If all these conditions are met, the "Query Element" successfully fills the "Repeating Table" element in the form with grouped values.

However, this does not work reliably with unique records, as the columns with the selection criteria must also be grouped.

This leads to ambiguous records in the "Repeating Table" element.

So there is no real grouping as in proper SQL.

My questions are:

A. How to setup the "Query Element" correctly, so that the GROUP BY statement only outputs unique records via the output columns only?

B. What alternatives may applicable instead of a „Query Element" to get unique records into a „Repetaing Table" element?

I would welcome any suggestions for a solution to my problem.

Many thanks in advance.

Regards

Norbert