3 – Hierarchy Transform

When importing data, Sage200 and many other applications require data to be structured in the form the application expects; for transactional data this often means a header/detail or Hierarchical Data structure.

IMan provides three data-shaping transformations (Hierarchy, Flatten & Aggregate) to provide you with the ability to manipulate data into the desired form.

In this step we will use the Hierarchy transform to shape our ‘flat’ dataset into one which has a header/detail structure.

Add Hierarchy Transform

  1. Drag a Hierarchy transform onto the palette.
  2. Drag a connector onto the palette and connect it to the two transforms as shown above.
    1. The connection points will turn red when connected.
  3. Double click the Hierarchy transform to open its setup and assign it a recognisable name, then Save.

Transform > Field Mapping

  1. Click on the Field Mapping tab.
  2. The following steps will define the fields in the order header.
    • For an order import, these are fields such as the customer name, address and order number.
  1. Click the Edit button, to show the field list.

  1. By default all fields are checked, so you only need to deselect the detail line fields which are listed on the final page.
  2. Set the OrderId 'Key' field to 1.
    • This will begin to construct the header/detail relationship.

If multiple fields join to form a unique key, simply set their key fields as 1, 2, 3, and so on.
  1. Click to page 3 and deselect the detail fields.

  1. Press the Green tick to save the grid.
  2. When asked if you want to delete the fields, press ‘Save’.

  1. Enter ‘OrderDetails’ into the New Transaction Id text box > press the Add button [>] to create a new transaction type.
    • This new transaction type will be our details record.

  1. Press the edit button to show the field list.
  2. Select the fields for the details (those that were deselected from the 'Orders' transaction).
  3. On the first page, select ‘OrderId’ and set its Key to ‘1’.

  1. Move to page 3, select the detail fields, set the 'LineNo' key to ‘2’.
    • This will create the second relationship for the detail records' organisation.

IMan enforces that every level must have at least 1 more field defined as key, hence why there are two fields marked as keys on the details record.
  1. Press the green tick and Save.
  2. Press Refresh.
    • The header detail structure will appear in preview.
  3. Press the [+] button at the start of the row to see the detail records:

  1. Press 'Apply' and close the setup window.
Step 4: Map Transform >