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
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 sample data used in the Jitterpak 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
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)
Example script for child node condition (contactRoles)
Example script for child node field (email)
Example script for child node field (name)
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:
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.
Last updated: Oct 02, 2019