Skip to main content

Enrich and filter the rows of a list

A document brings its lines along, but each line names only the item code — not the properties of the item. When the target system is to receive only some of the lines, the very value the decision would rest on is missing. An additional-data query at level Rows of a list closes that gap: it loads a value for every row, and the element filter of the mapping then evaluates against it.

The worked example is the route Lieferung-Export Logistik-Partner: the logistics partner ships only the power tools item group for Cambira, while the delivery holds mixed lines. The item group sits on the item, not on the line — a row-level query loads it for every line, and the mapping then filters the list on it.

What you need​

  • A route whose records contain a list — in the example, the delivery lines under OriginalPayload.DocumentLines.
  • Reference data: use Load by key to fetch a real delivery. Without it the result cannot be fetched and checked later, and the field picker of the mapping stays empty.
  • Plain enrichment should be familiar — it is described in Enrich data with additional data. This guide builds on it.

You create the query on the Source data tab and the filter on the Mapping tab. Changes are saved automatically — there is no separate save button. They land in the draft and take effect only with its activation.

Point the query at the rows​

On the Source data tab, use Add additional data to create a query and set Level to Rows of a list. Three additional fields appear; others are dropped: Row limit, because the number of rows follows from the list, Sub-object and Link field (sub-object), because at row level Xchange reads in one bundled query from the main object alone, and Result type with Column name, because a row belongs to one record and not to a list.

  1. Name — Warengruppe in the example. This is the name under which you reach the value at the row later.
  2. Row list — the list whose rows are queried: OriginalPayload.DocumentLines[*]. The picker offers the lists of the source schema, in the same notation as the mapping's source list; with no schema loaded yet, enter the path yourself.
  3. Main object — the object type read from: the items (Items).
  4. Fields — the fields to read, here the item group (ItemsGroupCode).

Specify the link​

Two fields tell Xchange which item belongs to which line:

  • Link field (row) — the field of the line that names the item: ItemCode.
  • Link field (main object) — the field of the item the line references: ItemCode as well.

Once the row list and the main object are set, both are prefilled: the main object's key on the right, the row's field of the same name on the left where there is one. If the two fields are named differently, change the left side.

The link field in the main object must be its key. The reason lies in how Xchange reads: instead of fetching line by line, it collects the item codes of all lines and loads them in one bundled query — a delivery with forty lines therefore does not cost forty fetches. A field other than the key cannot be bundled that way, and the validation list reports it.

Conditions work here as they do at record level: they narrow the items retrieved further. The link itself does not belong in a condition — the two fields above are what express it.

Fetch and check the result​

Fetch additional data runs the query against the loaded reference data. Check the result in the data preview: the item group now sits on every line, not on the document — each row carries its own value. If a line has no value, there is no item for its item code; check the Link field (row) in that case.

Filter the list on it​

Switch to the Mapping tab and open the list mapping that fills the lines. Its source list is the same list the query addresses (OriginalPayload.DocumentLines[*]).

Open the list settings and its Filter tab:

  1. Add condition.
  2. Field — in the field picker, choose the loaded item group under Element fields. It sits there next to the fields the line brings along itself; that it was loaded afterwards makes no difference to the filter.
  3. Operator — equals.
  4. Value — the item group of the power tools, 100 in the example.

Only lines the filter matches appear in the target list. Combine several conditions with the AND / OR button.

When the list sits inside a list​

The row list may itself sit inside a list — the batches of a line under OriginalPayload.DocumentLines[*].BatchNumbers[*], for example. Three levels are possible: main record, list, list within that list.

In the mapping of such a list, the field picker then carries three groups: Element fields (the batch itself), Parent row (the line it sits in) and Main record (the delivery). A batch row thereby reaches the item code of its line without that code sitting on the batch.

Activate​

Query and filter take effect in operation only once you activate the draft. The full procedure is described in Activate configuration changes.

What comes next​

Before activating, check in the test area whether the route transfers the expected lines — the filter applies there exactly as it does in operation.

See also​