Get Records

Field Name

Description/Use

Type/ Options 

Optional/ Mandatory 

Data-object

This is the collection name in the context into which the system will store the returned data.

Text

Mandatory

Collection

Check this box if the returned data contains multiple rows. If not checked, the system will only return the first record.

Checkbox

Optional

Distinct

If this checkbox is checked the part will eliminate duplicates from the returned data and return a distinct value.

Checkbox

Optional

Data source(s)

This is the name of the collection of data source(s) name in the database. It corresponds to the FROM clause in SQL query. For example, select * from tb1, tb2, tb3; It select records from three tables.

Collection

Mandatory

Selection

This is the filter query of the data, and it corresponds to the WHERE clause in the SQL query. For example, select * from temperature where temperature.value>5; It returns all records of which the value is larger than 5.

Selection operators vary by variable type as follows:

• numeric fields: equals, not equals, is larger (or equal) than, is smaller (or equal) than, in, not in
• text fields: equals, not equals, is not, like, equals or empty, in, not in

Use like with leading and/or trailing wildcard (%) if necessary

In, and not in comparison value must be a collection as text, quoted and comma separated.

• Use the format variable part to do the conversion so that apostrophes are escaped correctly for the SELECT statement.
• Note that if the collection is empty, this term only is dropped from the query to avoid an error from "where target in {null}."

Complex criteria can be built using the editor by using AND/OR (toggle to switch) and adding parentheses around specific terms to define the precise selection rules.

Advanced Note: while less commonly used, the greater than/smaller than rules for numeric fields can also be applied to text fields, as per SQL

Collection

Optional

Columns

These are the selected columns of the data table(s). It corresponds to the SELECT clause in SQL query. For example, select date, value from temperature;. Here id date and value is the 'column' name. 'Data object' name is the data path name you want the data to be stored into in the context.

In addition the following aggregate functions are supported:
MIN, MAX, COUNT, AVG, SUM and GROUP_CONCAT.

When a 'group_by' is specified, the aggregate functions: MIN, MAX can be used for selecting the record with the column with the lowest or highest value in the set of grouped records. The COUNT function can be used for counting the total numbers of records per group. The AVG and SUM function can be used for calculating the average or the sum of the values in a column of the records per group. The GROUP_CONCAT can be used for concatenating all the values of a column of the records per group. For the latter, you can alter the default separater ',', by setting the record_format.separator for the column.

Collection

Mandatory

Group by

This allows you to group the returned data by the given column, and this value corresponds to the GROUP BY clause in SQL query. For example, select * from temperature group by day; It returns all data which is grouped by the day value.

Collection

Optional

Having

This field allows you to add additional conditions. It corresponds to the HAVING clause in SQL query. For example, select * from temperature group by temperature.day having temperature.value>5; It returns all the data when value is larger than 5. Please be noted that a GROUP BY clause is required before the HAVING clause.

Collection

Optional

Order by

This field allows you to sort the returned data by the given column. It corresponds to the ORDER BY clause in SQL query .For example, select * from temperature order by date desc; It returns all data which is sorted by the date in the descending order.

Collection

Optional

Log data object

This is the name of the data object that stores the error messages from the execution of the database parts: add data record, get data record, update data record, delete data record, and import values. When the log data object is specified, the error message and stack trace of a database part will be stored to the object in case of errors.

When used by multiple database parts, the Log data object will store the cumulative list of error messages from the different parts.

Text

Optional

Reset log data object

When this field is checked the system will clear the error messages from the Log data object. Typically, this option should be checked in the first database part of a sequence of database parts.

Checkbox

Optional

Skip on error

When this field is checked the system will skip the execution of subsequent database parts linked to the same Log data object. 

Checkbox

Optional

Merge results

When this field is checked the get records part results will "write into" any stored values for the data-object (for instance from a previous cycle through the part). Any common items are overwritten as expected, but if there are no results from the get records, the prior results will be retained.

When not checked, the data-object is cleared of all results prior to running the get records query. In the event there are no results from the get records, the data-object will be empty. 

Checkbox

Optional

Database

This is the name of the database. It is only used when the data is not stored in the same project and when the location can't be retrieved automatically.

Text

Optional