Skip to content

6.3 – Filter Transform

Clean Up the Dataset

6.2 creates all three charge lines on every order, whether or not the order was charged for them. The charge lines with no value must go before the orders reach Sage 300. The Filter transform removes them.

Design > Transform Setup

  1. Drag a Filter transform onto the design surface and connect it to the Aggregate transform.

    The design surface, with a Filter transform connected to the end of the Aggregate

  2. Double-click the Filter to open its setup.

  3. Check that the Transform Id is a recognisable name.

    • A new Filter is already named Filter. For training, leave it. A second Filter would arrive as Filter_1, so rename that one.

    The Filter transform's Setup tab, showing the Transform Id

Transform > Field Mapping

  1. Press the Field Mapping tab and change the Current Transaction Id to OrderDetails.

  2. Enter the Record Evaluation formula:

    %LineType = 1 Or (%LineType = 2 And %ExtendedPrice <> 0)

    The Filter's Field Mapping tab, with OrderDetails selected and the record evaluation formula entered

    The formula keeps every item line, and keeps a charge line only if its amount is not zero. LineType 1 is an item line and 2 is a miscellaneous charge, as set in 6.1 and 6.2.

The formula must evaluate to True or False

The formula must return True or False. When it returns False, IMan removes (filters) the record from the dataset.

  1. Press Refresh.

    • The first order keeps its two item lines and its Delivery charge. It loses Assembly and Two Man Supplement. Those two columns were empty for that order, so GetParent returned 0 and the formula excluded them.

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

Step 7: Sage 300 Order Import >