Skip to content

6.2 – Aggregate Transform

The Aggregate transform can:

  • Create records derived from the other records in a node.
  • Create records that are not part of the original dataset.

Example of use

  • Create a balancing detail line for a POS sales journal that is the negative sum of the other records.
  • Create a VAT record that sums the VAT amount fields of the other records.
  • Consolidate many lines of an invoice or journal into a single record.
  • Create shipping charge or comment lines from values held, for example, in a header record.

This step does the last one. The workbook carries the assembly, two-man delivery and carriage charges as three columns on the order header, and Sage 300 needs them as order lines. The Aggregate moves them.

Design > Transform Setup

  1. Open the Transforms group in the palette and drag an Aggregate transform onto the design surface.

  2. Drag a Connector from the same group, then drag its ends onto the Map transform and the Aggregate to join them.

    The design surface: the Excel reader, Hierarchise and Map transforms in a chain, with the Map now feeding both the Sage 300 customer connector and a new Aggregate transform

    The Map now feeds two transforms: the Sage 300 customer import built in step 5 and this Aggregate.

Transform > Setup

  1. Double-click the Aggregate to open it.

    The Aggregate's Setup tab, showing the Transform Id, Description and Priority fields

Priority appears only once a transform has a sibling

Priority Field

IMan shows the Priority field only when two or more transforms are connected to the same parent transform. Priority controls the order in which they run.

It appears here because the Map transform now has two children. It was not on the Map's own Setup tab in step 4, when the Map was the only transform connected to the Hierarchise.

Transform > Field Mapping

  1. Press the Field Mapping tab and change the Current Transaction Id to OrderDetails.
    • The charge lines are order details, so you build them on the OrderDetails transaction with the fields added in 6.1.
  2. Press the + button beside Calc Records to create a record.

  3. Give it a recognisable name in Description.

    • For training, enter: Assembly

    The Aggregate's Field Mapping tab, showing Current Transaction Id set to OrderDetails, the Calc Records drop-down with its add and delete buttons, the Description, and the Record Inclusion Condition

    1. Delete Child Records
      • Removes the records the calc records are derived from, and keeps only the created ones. For training, leave unticked. You need the order lines as well as the charges.
    2. Calc Records
      • Every record this transform creates. The + adds one and the − deletes the selected one after you confirm.
    3. Description
      • The name of the selected calc record. It identifies the record in the list, so make it recognisable.
    4. Record Inclusion Condition
      • A formula that decides whether IMan creates the record. It defaults to True. For training, leave it. Every order gets all three charge records, and 6.3 removes the ones with no value.

  4. Set each field by double-clicking its row in the grid. For the Assembly record:

    Field Name Evaluate Evaluate String
    LineType ✘ 2
    Description ✘ Assembly
    ExtendedPrice ✔ GetParent("AssemblyCharge", "Orders")
    MiscChargeId ✘ PACK
    • LineType is 2, the Sage 300 line type for a miscellaneous charge. The order lines from the workbook carry 1.
    • ExtendedPrice is the only evaluated field. GetParent reads a field on the parent order of the line being created. The workbook holds the charge there.

    The Field Mapping dialog for ExtendedPrice on the Assembly calc record, with Evaluate ticked and the GetParent formula in the Evaluate String

  5. Press the tick to save each field. With all four set the record reads:

    The Assembly calc record's field grid, showing Description, LineType, ExtendedPrice and MiscChargeId set, with Evaluate ticked only on ExtendedPrice

    Leave every other field blank. A calc record is derived from the records already in the node. The fields you do not set here carry through from the order's first detail line. A charge line therefore arrives holding that line's SkuCode and Qty as well as its own OrderId.

  6. Press + again and build the other two records the same way:

    Description LineType Description field ExtendedPrice (evaluated) MiscChargeId
    TwoManSupplement 2 Two Man Supplement GetParent("TwoManDeliverySupplement", "Orders") PACK
    Delivery 2 Delivery GetParent("Delivery", "Orders") PACK

  7. When you finish, the Calc Records drop-down has three entries.

    The Calc Records drop-down open, listing Assembly, TwoManSupplement and Delivery

  8. Press Refresh.

    • Expand an order in the preview. It shows three extra detail lines beneath the item lines: Assembly, Two Man Supplement and Delivery. Each carries LineType 2 and the charge from the order header. On the first order only Delivery has a value. The other two charges are 0, and 6.3 filters them out.

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

Step 6.3: Filter >