Iterate Collection

SLIK provides a simple mechanism for iterating over a collection of values and calling a SLIK operation for each element.  Iteration consists of an SQL statement that returns one or more rows.  For each row fetched SLIK creates a variable for every column name in the query as well as $element1…n.  These variables are consequently available to be referenced in the nested operation.  If a user provided column name in the SQL statement matches the SLIK operation column name then this values replaces any existing values.  This allows substitutions to be performed on numeric and date type fields without needing to see $element in the string.

The SLIK control database includes two tables called Collection and Element. This can be populated by the user and used as base tables when defining an Iteration. Note, however that the SQL Statement specified in the Iteration operation can select data from any ODBC compliant database and not just these Collection and Element tables.

This feature makes it possible to have a specific operation in SLIK driven with data coming from an external datasource.

Iteration consists of a SQL statement that returns one or more rows.  For each row fetched SLIK creates a variable for every column name in the query as well as $element1…n.  These variables are consequently available to be referenced in the nested operation.  If a user provided column name in the SQL statement matches the SLIK operation column name then this value replaces any existing values.  This allows substitutions to be performed on numeric and date type fields without needing to see $element in the string.  

To add an Iteration record click on the Iterate button or select Edit ® Add New ® Operation ® Group Variables… from the main menu.

Iteration records are created using the following form:

Iterate Form Field Descriptions

Iteration ID

The Iteration ID field holds the system generated unique identifier.  The Iteration ID field automatically populates on creation of the Iteration record.  The Iteration record is created when you click on OK to save and close the new record.

SQL Statement*

The SQL Statement field allows you to either select or enter the SQL statement the iteration is associated with.

Operation Type*

The Operation Type field allows you to select the Operation Type appropriate to the Iteration.

Operation*

The Operation field allows you to either select or enter the Operation that will be undertaken by this Iteration.

Collection Elements

The Collection Elements button provides access to the Collection / Elements Properties grid work area where Collections within the SLIK Control Database can be defined.

This is mainly envisaged to be useful for lists that are perhaps static rather than dynamic lists obtained from remote data sources.

Create an Iteration Record

1.       From within a Job Properties record click on the Iterate button.  This will open the Iteration Properties form. 

The Iteration ID field will automatically populate with a system generated unique identifier when the record is first saved.

2.       Using the SQL Statement* drop-down menu select the required SQL Statement for the Iteration record. 
Note: if the required SQL Statement is not available create one by clicking on the  button to invoke the SQL Statement Properties form.  Refer to Create a SQL Statement Operation.

3.       Using the Operation Type* drop-down menu select the appropriate Operation Type for the Iteration record. 

4.       Using the Operation* drop-down menu select the appropriate Operation for the Iteration record.  The Operation values available are limited to those relating to the Operation Type selected in the previous step.  

Collection and Elements

SLIK includes two tables in the SLIK Control Database called Collection and Elements that may be useful when performing Iterations. If you have a list of elements that are fairly static or cannot be looked up using an SQL Statement in a remote data source then you can add records to these two tables to hold a list of elements to process. For example, add the elements required to the SLIK Collection/Element tables and then create an SQL Statement like:

            SLIKDB: SELECT Element1 AS MyVariableName
FROM CollectionElements
WHERE CollectionName = ‘My Collection Name’

1.       Click on the Collection Elements button to invoke the Collection / Element Properties work grid.

6.       Amend the existing records by typing directly over the existing entry or to create a new entry:

a.        click on the Collection ID button to invoke a new entry space.  The Collection ID field will automatically populate with the next available unique identifier number.  Input a name for the Collection directly into the CollectionName field. 

b.       click on the Element button to invoke the Sequence / Element fields. 

c.       enter the required sequence value and then enter the elements to be acted upon using the Sequence and Element# column fields. 

7.       Click on the Close button to accept your new or amended Collection record and return to the Iteration Properties form.

8.       Click on the OK button to accept the Iteration record and close the Iteration Properties form.