Skip to content

6.2 – Aggregate Transform

The Aggregate transform can:

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

Examples of use

  • Create a balancing detail line for a POS sales journal, as 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 of these. The workbook holds the assembly, two-man delivery and carriage charges, and the customer's comment, as columns on the order header. Sage 200 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 200 customer connector and a new Aggregate transform

    The Map now feeds two transforms: the Sage 200 customer import you built in Step 5, and this Aggregate.

  3. Save the integration before opening the 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 sets the order in which they are processed.

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 and comment lines are order details, so you build them on the OrderDetails transaction, using the fields you 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. + adds one, and − deletes the selected one after asking you to confirm.
    3. Description
      • The name of the selected calc record. It identifies the record in the list. Make it recognisable.
    4. Record Inclusion Condition
      • A formula that decides whether the record is derived. It defaults to True. Leave it alone on the three charge records: every order gets all three, and 6.3 removes the ones with no value. The comment record in step 7 below uses it.
      • The chevron beside the heading opens the formula editor. When the section is collapsed, it shows the condition as read-only text.

  4. Set the fields by double clicking the relevant row in the grid. For the Assembly record:

    Field Name Evaluate Evaluate String
    LineType ✘ 2
    Description ✘ Assembly
    StoredCharge ✘ Carriage
    StoredValue ✔ GetParent("AssemblyCharge", "Orders")
    • LineType is 2, the Sage 200 SOP line type for a charge. The item lines from the workbook carry 0.
    • StoredCharge names one of the additional charges set up in Sage 200. The demo company has three: Carriage, Air Freight and Express Delivery. All three charge lines use Carriage, the demo company's delivery charge. Each line's Description says which charge it is.
    • StoredValue 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 StoredValue 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, StoredCharge and StoredValue set, with Evaluate ticked only on StoredValue

    Leave every other field blank. A calc record is derived from the records already in the node, so any field not set here takes its value from the order's first detail line. A charge line therefore arrives holding that line's SkuCode, Qty and UnitPrice as well as its own OrderId.

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

    Description LineType Description field StoredCharge StoredValue (evaluated)
    TwoManSupplement 2 Two Man Supplement Carriage GetParent("TwoManDeliverySupplement", "Orders")
    Delivery 2 Delivery Carriage GetParent("Delivery", "Orders")

  7. The last record moves the customer's comment from the order header onto its own line. Press +, name it Comment, and set only two fields:

    Field Name Evaluate Evaluate String
    LineType ✘ 3
    Description ✔ GetParent("CustomerComments", "Orders")
    • 3 is the Sage 200 SOP line type for a comment. A comment line carries no charge and no value, so leave StoredCharge and StoredValue alone.

  8. This record needs a Record Inclusion Condition. Open it with the chevron and replace True with:

    GetParent("CustomerComments", "Orders") <> ""
    • Not every order has a comment: FBRN-309244 in the workbook has none.
    • Without the condition, that order still gets a comment line, and the line is not blank. A derived field whose formula returns an empty string keeps the value it inherited. The comment line therefore arrives reading Gold Mixer, the description of the order's first item.
    • With the condition, the record is not derived and the line arrives empty. It still arrives, and 6.3 removes it.

  9. When you finish, the Calc Records drop-down has four entries.

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

  10. Press Refresh, then expand an order in the preview.

    • Beneath its two item lines the order now has three charge lines: Assembly, Two Man Supplement and Delivery. Each has LineType 2 and the charge from the order header. The order also has a comment line with LineType 3 and the text from CustomerComments.
    • On the first order only Delivery has a value. The other two charges are 0, and 6.3 filters them out.

    The expanded OrderDetails grid for the first order, showing two item lines with LineType 0, three charge lines with LineType 2 and Carriage, and a comment line with LineType 3

Tip

An expanded child grid is much wider than the pane, so at first it shows a single column down the rows. Drag the columns narrower to see them all, as in the screenshot above.

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

Step 6.3: Filter Transform >