Step 3 – Hierarchy Transform

Sage 300 and many other applications need imported data in a particular structure. For transactional data this is often a header/detail or Hierarchical Data structure.

IMan has three transforms that reshape data: Hierarchy, Flatten and Aggregate.

In this step you use the Hierarchy transform to shape the ‘flat’ dataset into a header/detail (parent/child) structure.

Design > Transform Setup

1. Press the Transform Setup tab.
2. Open the Transforms group in the palette.
3. Drag a Hierarchise transform onto the design surface.
   • This is the Hierarchy transform. The palette tile is labelled Hierarchise.

4. Drag a Connector from the same group and join the Excel Reader to it.

   

Transform > Field Mapping

The Hierarchy transform splits one flat transaction into a parent and a child. Key fields say which rows belong together.

1. Double-click the Hierarchise transform to open it, then press the Field Mapping tab.

   

   1. Transaction Id to Hierarchise
      • The incoming transaction to reshape. You named it in Step 2.
      • For training, this is: Orders
   2. New Transaction Id
      • Names a new level to add beneath the current one. The button beside it stays disabled until the box has a value.
   3. Hierarchy
      • The structure you are building. It starts as a single root, and each level you add appears beneath its parent. Drag one level onto another to change its parent.

2. Press Edit above the grid to make the field list editable.

   • The toolbar also offers Select All Fields and Deselect All Fields, which act on every row at once.
   • The grid is one scrolling list, not a set of pages.

   

3. Set the Key of OrderId to 1.

   • This starts the header/detail relationship. Rows that share an OrderId become one header.

4. Scroll to the end of the list and untick Import for the detail line fields:

   Field Name
   LineNo
   Qty
   SkuCode
   Description
   UnitPrice

   The end of your list should now look like this:

   

A composite key is numbered 1, 2, 3

If several fields together form a unique key, set their keys to 1, 2, 3 and so on.

1. Press Save above the grid.

   • IMan lists the fields it is about to remove and asks you to confirm. Press the tick to continue.

   

Adding the detail level

1. Enter OrderDetails into the New Transaction Id box.

   • The button beside it becomes enabled when the box has a value.

   

2. Press the add button to create the level.

   • OrderDetails appears in the Hierarchy panel, nested beneath Orders.

3. Press Edit above the grid, then Deselect All Fields and confirm the prompt.

   • A new level starts with every field selected. It is quicker to clear them all and pick the few you want.

4. Tick Import for the detail fields, and set their keys:

   Field Name	Key
   OrderId	1
   LineNo	2
   Qty
   SkuCode
   Description
   UnitPrice

   

   

Every level needs one more key field than its parent

IMan requires every level to have at least one more key field than its parent level. That is why the detail has two key fields.

1. Press Save above the grid.

   • The Hierarchy panel now shows the finished structure: Orders as the root, with OrderDetails beneath it.

   

Checking the result

1. Press Refresh.

2. Press the arrow at the start of a row to expand it.

   • The header row opens to show the OrderDetails records for that order. Two rows that shared an OrderId are now one header with two detail lines. This is the structure Sage 300 expects.
   • The detail grid is much wider than the pane, so only its first column is in view. Scroll right to see the other detail fields.

   

3. Press Close at the bottom of the transform, then Save the integration.

Step 4: Map Transform >