Skip to content

Step 3 – Hierarchy Transform

Sage 200, like many applications, needs imported data in a particular structure. For transactional data this often means 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 turn the flat dataset into one with 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.

    The Transforms palette, and the design surface showing the Excel Reader connected to a Hierarchise transform

Transform > Field Mapping

The Hierarchy transform takes one flat transaction and splits it into a parent and a child, using key fields to say which rows belong together.

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

    • Transaction Id to Hierarchise already reads Orders, and the Hierarchy panel shows Orders as the root. IMan chooses it for you because the Excel Reader has only one transaction.

    The Field Mapping tab: Transaction Id to Hierarchise set to Orders, the Hierarchy panel showing Orders as root, and the field grid

    1. Transaction Id to Hierarchise
      • The incoming transaction to reshape, which you named 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 re-parent it.

  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 a single scrolling list with no pages.

    The header field list in edit mode, scrolled to the top, with every field ticked in the Import column

  3. Set the Key of OrderId to 1.

    • This begins the header/detail relationship: rows sharing an OrderId become one header.
    • Every key starts at 0, which means "not a key".

  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:

    The end of the header field list, with Import unticked for LineNo, Qty, SkuCode, Description and UnitPrice

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.

    The Remove Fields confirmation listing LineNo, Qty, SkuCode, Description and UnitPrice

Adding the detail level

  1. Enter OrderDetails into the New Transaction Id box.

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

    New Transaction Id containing OrderDetails, with the add button now enabled

  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 leave at 0
    SkuCode leave at 0
    Description leave at 0
    UnitPrice leave at 0

    The detail field list, scrolled to the top, with OrderId ticked and its Key set to 1

    The end of the detail field list, with the five line fields ticked and LineNo keyed 2

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. 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.

    The Hierarchy panel showing Orders as root with OrderDetails nested 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 reveal the OrderDetails records belonging to that order. The three rows that shared FBRN-309243 have become one header with three lines beneath it. This is the structure Sage 200 expects.
    • The detail grid's columns are much wider than the pane, so at first only one or two are in view. Scroll right, or drag the column edges in, to see all six at once as below.

    The preview showing an Orders row expanded to reveal its OrderDetails records

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

Step 4: Map Transform >