6.2 - Aggregate Transform

The aggregate transform allows you to:

  • Create records which are derivatives of other records in a particular node.
  • Create record(s) which are not part of the original dataset.

Examples of use

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

Creating the Aggregate Transform

  1. Add an Aggregate transform; connect it to the Map transform and; open its setup.

Priority Field

The Priority field is displayed for transforms where there are 2 or more children connected to a parent and is used to control the ordering of processing.

Aggregate > Field Mapping

  1. Open the field mapping tab.
  2. Change the current transaction Id to OrderDetails.

  1. Press the Add button.
  2. Assign a friendly name to identify the record (this will appear in the drop down above).
    • For training, enter: Assembly

  1. Set the fields to following values, by double clicking the relevant field in the gird:
  1. Field Name
    • LineType
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • 2
  1. Field Name
    • Description
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • Assembly
  1. Field Name
    • StoredCharge
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • Carriage
  1. Field Name
    • StoredValue
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • GetParent("AssemblyCharge", "Orders")
  1. Press Apply.
  2. Press Refresh. Expand an order and you will see an additional detail line per order.

  1. Now create another Calc Record, by pressing the Add button.

  1. Assign a friendly name to identify the record.
    • For training: TwoManSupplement

  1. Set the fields to following values, by double clicking the relevant field in the gird:
  1. Field Name
    • LineType
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • 2
  1. Field Name
    • Description
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • TwoManSupplement
  1. Field Name
    • StoredCharge
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • Carriage
  1. Field Name
    • StoredValue
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • GetParent("TwoManDeliverySupplement", "Orders")
  1. Press save.
  2. Finally create the last ‘Calc Record’ for the Delivery charge, by pressing the add button once more and entering a friendly name:
    • For training: Delivery.

  1. Set the fields to following values, by double clicking the relevant field in the gird:
  1. Field Name
    • LineType
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • 2
  1. Field Name
    • Description
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • Delivery
  1. Field Name
    • StoredCharge
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • Carriage
  1. Field Name
    • StoredValue
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • GetParent("Delivery", "Orders")
  1. Press save.
  2. Refresh and expand the LAST order.
    • Each additional charge now appears on its own line with the corresponding value.


  1. We now need to create a final ‘Calc’ record to move the Order Comment from the header onto its own detail line.
  2. Press the Add button, and set the Description field to a recognisable name.
    • For training: Comment.
  1. Set the fields to following values, by double clicking the relevant field in the gird:
  1. Field Name
    • LineType
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • 3
  1. Field Name
    • Description
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • GetParent("CustomerComments", "Orders")
  1. Press save.
  2. Press Refresh and there will be another detail line.
    • This time the description field equals the value of the OrderComments field in the header.

  1. Save and close the Transform Setup screen.
  2. Save the integration configuration by pressing the Save button on the bottom-right of the designer.
Step 6.3: Filter Transform >