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.

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

Priority Field

The Priority field is only displayed for transforms where there are 2 or more transforms connected to a parent transform. The priority field is used to control the order of processing.

Creating new records

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

  1. Change the current transaction Id to 'OrderDetails'.

  1. Press the Add button.

  1. Assign a recognisable name to identify the record.
    • For training, enter: Assembly

  1. Set the fields to following values, by double clicking the relevant field in the grid:
  1. Field Name
    • LineType
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • 2
  1. Press the green tick. Add the following fields:
  1. Field Name
    • Description
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • Assembly
  1. Field Name
    • ExtendedPrice
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • GetParent("AssemblyCharge", "Orders")
  1. Field Name
    • MiscChargeId
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • PACK
  1. Press the green tick.
  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 recognisable name to identify the record.
    • For training, enter: 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
    • Two Man Supplement
  1. Field Name
    • ExtendedPrice
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • GetParent("TwoManDeliverySupplement", "Orders")
  1. Field Name
    • MiscChargeId
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • PACK
  1. Save.
  1. Finally create the last ‘Calc Record’ for the Delivery charge.
  2. Press the Add button, and assign a recognisable name to the record.
  1. For training, enter:
    • Delivery
  1. Set the fields to the 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
    • ExtendedPrice
  2. Enable Script Evaluation
    • Checked
  3. Formula
    • GetParent("Delivery", "Orders")
  1. Field Name
    • MiscChargeId
  2. Enable Script Evaluation
    • Unchecked
  3. Formula
    • PACK
  1. Press Save
  2. Once complete there will be three items in the Calc Records drop down:

  1. Refresh, and expand the last order.

Each miscellaneous record now appears on its own line with the corresponding value.

  1. Close the setup window and save the integration.
Step 6.3: Filter >