Issue

Stored Procedure SmartObjects do not work with For Each loop; the single item reference is returning all of the items in the collection as a single concatenated string instead of just the single item.

Image

There are three rows in this table.

Image

For Each is implemented, and the "Single" item reference is used; this should return a single "fruit" in each email sent.

Image

It will send three emails.  However, instead of just a single item in each email it returns all items of the collection concatenated for all three emails/iterations. This behavior is incorrect.

Symptoms

This works with SQL Tables and Views SmartObjects and SmartBox SmartObjects.

The behavior of the values appending to eachother is due to how the for each loop was architected; there will need to be special design considerations when creating the stored procedure and SmartObject.

Please see Understanding the Loop Step in a Workflow regarding a possible workaround: 

http://help.k2.com/kb002121

Image

However, there are two limitations when implementing the loop design as per the KB above:

1.  The referenced record ID must begin with 1.

2.  The referenced record ID must be in running sequence number (1, 2, 3).  I.e. (1, 3) will not work.

Resolution

Another workaround exists (when ID does not begin with 1 or is sequential), but will also have the requirements below:

1.  There must be a unique field to identify the item within a delimited list, this can be an ID field or another unique field.

2.  If using a unique string/text field instead, this must not contain the semicolon (;) character; as the semicolon is used to split each item.

Overview of workaround:

We will store the unique IDs/unique field as a semicolon delimited list within a process data field. Use the For Each loop to iterate through this data field as it can handle a semicolon delimited text and use corresponding SmartObject Load/List method or another Create Reference event to interact with each item within the loop.

Image

Steps:

1.  Create a Data Field to store a semi-colon delimited list:

Image

2.  You can use a Set Data Field event with the Text > Join function to build out this delimited list.  I.e.

a.  1;3;4

Or

b.  UniqueString1;UniqueString3;UniqueString4

* Be sure to 'Return all items that match filter' (4.7 and previous version) or 'All Items as a collection' (K2 Five).

Image

3.  Use the For Each loop to target the semicolon delimited list data field and create the Single Item reference to be used in the loop.

Image

4.  Within the loop, this Single Item Reference field will return a single item of the delimited list (NOT all properties of the SmartObject).  I.e. 1, THEN 3, THEN 4 OR alternatively UniqueString1, THEN UniqueString3, THEN UniqueString4.

5.  Within this loop, you can call the Load OR Get List method of the SmartObject in question to get and use the data as needed or use another Create Reference event within the loop if you want to access that row as an item reference instead.

Image