Skip to content

6.3 – Filter Transform

Clean Up the Dataset

6.2 creates all three charge lines on every order, whether or not that order was charged for them, and a comment line on every order whether or not the customer left one. Those empty lines must go before you import the orders into Sage 200. 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. Save the integration, then double click the Filter to open its setup.

  3. Make sure the Transform Id is a recognisable name.

    • A new Filter is already named Filter. For training, leave it as it is. A second Filter would be named Filter_1; rename that one.
    • Unlike the Aggregate, the Filter has no Priority field, because it is the only transform connected to the Aggregate.

    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 = 0 Or (%LineType = 2 And %StoredValue <> 0) Or (%LineType = 3 And %Description <> "")

    The Filter's Field Mapping tab, with OrderDetails selected, the record evaluation formula entered and the syntax check reporting No Errors Found

    Read it as: keep every item line; keep a charge line only if it has a value; keep a comment line only if there is a comment. You set the three line types in 6.1 and 6.2: 0 for an item, 2 for a charge and 3 for a comment.

    • The editor checks the formula as you type and reports No Errors Found beneath it.

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 and expand the first order.

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

    The expanded OrderDetails grid for the first order after filtering, showing two item lines, one Delivery charge and the comment line

    The other two orders show the rest of the behaviour. FBRN-309243 keeps Delivery and its comment. FBRN-309244, the only order charged for all three, keeps Assembly, Two Man Supplement and Delivery, and loses its empty comment line.

Refresh from the transform that changed, and work downwards

Refresh from the transform that changed and work downwards. A Refresh replays whatever its upstream transform last produced. If you change the Aggregate and refresh only the Filter, the Filter shows the old data and does not warn you.

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

Step 7: SOP Import >