Create a transformation iterator dynamically in Jitterbit Design Studio

Use case

A common integration scenario is where the source data is flat and the target is hierarchical. If mapped one-to-one, the transformation will use as its generator the number of incoming records and the one-to-many node will not be populated correctly. The transformation must be instructed to generate multiple instances from the same record. This can be accomplished by using transformation conditions and the functions SetInstances() and GetInstances().

Examples

CSV to flat file

This operation creates test data, reads the output, passes it through one transformation and writes to a temporary file. The script 'Read Output' can be used to view the file.

While the transformation uses the NetSuite WSDL, it does not perform an Upsert action and no NetSuite endpoint (or credentials) is required to run the operation.

The following sample data has primary and billing contact information (names and emails). Each one has its own fields, but the target uses repeating rows for contact information. These screenshots show stepping through the records:

Note that when the operation is tested, the target has two contactRoles records from the single source.

A dynamic iterator is created by using the SetInstances() and GetInstances() functions and passing an array of values.

There are several prerequisites:

• Conditions (not scripts) are added to the transformation target to use SetInstances() and GetInstances().
• The SetInstance() function must be inside the condition of the immediate parent of a child node.
• The child must have a 0-to-many or 1-to-many (E*) cardinality.

Example script for parent node (contactRolesList)

primaryContactArray = Array(); // Instantiate the array. The array will be re-instantiated for each record
Set(primaryContactArray, Primary_Contact, -1); // Append the primary contact and primary email to array
Set(primaryContactArray, Primary_Email, -1);

billingContactArray = Array(); // Similar to the above
Set(billingContactArray,Bill_To_Name,-1);
Set(billingContactArray,Billing_Email,-1);

// Since the array contains two rows, the transformation generator will build two output children nodes
rolesArray = Array();
Set(rolesArray, primaryContactArray,-1);
Set(rolesArray, billingContactArray,-1);

SetInstances('contactRoles', rolesArray); // Must include the child node name.
// This stores the instance array to be used with GetInstances

/*
Example data at this point (Primary Contact, Billing Contact):
{{Judith Hall,sbishop0@pinterest.com},{Scott Bishop,sbishop0@mediafire.com,}}
Since the array contains two rows, the transformation generator will build two output nodes
*/
true // Since this is a condition, returned value must be true or else the record is skipped.

Example script for child node condition (contactRoles)

// Assign a global variable to the contents of GetInstance
$instanceReference = GetInstance();
// As this is a condition node, it must return true or else the record is skipped.
true

Example script for child node field (email)

// Simply get the array value. This will get the email column in the array records
$instanceReference[1]

Example script for child node field (name)

// Simply get the array value. This will get the name column in the array records
$instanceReference[0]

Flat file to database

The second example is similar to the first, except the target is now a database:

This uses a local PostgreSQL database. The tables are dropped and re-created in the first script for ease of deployment. The transformation creates multiple rows for each row in the source data:

The scripts are very similar to the text (first) example. SetInstances() is in the condition of the parent node, and GetInstances() is in the condition of the child node. The parent node has to use the name of the child node: SetInstances('ContactAddress', rolesArray)

Displaying the table in PostgreSQL, we see that the ContactAddress table shows 400 rows: the source has 200 rows, and 2 entries are created for each row.