Skip to main content

Load missing details

A delivery from SAP Business One names the customer only by the customer number; the phone number is missing. Its lines name the item, and whether it is a stock item or a freight charge is stored on the item itself. Both are in SAP Business One, outside the delivery.

Additional data fetches such details before the mapping runs. After that, they are available for selection in the mapping like any other source field.

In the example: The route "Lieferaufträge Kessler" sends deliveries to the logistics partner. Kessler requires the recipient's phone number in the field recipient_phone and, in the list lines, only the items that are actually packed. Delivery 80234 for the customer K-10317, Hollmann Bau GmbH, has two lines: ARM-4711 with quantity 2 and a freight charge. At the end, the test result carries the phone number and exactly one entry in lines.

What you need​

  • The Routes permission at the Full level. See Permissions.
  • A route whose source data is already loaded — the data preview shows a real record. You try out the queries on it.
  • Starting point: Configuration → Routes, select the route, Source data tab, Additional data section.

Xchange saves every entry automatically. In operation, it takes effect only with the activated configuration snapshot — that is step 6.

1. Create a query for the record​

The phone number belongs to the customer, and the customer belongs to the whole delivery. So the query runs once per delivery.

  1. Select Add additional data. The new query appears on the left, its settings on the right.
  2. In the Name field, enter Kunde. Do this first: the mapping later offers the results under this name, and a query appears there only once it has a name.
  3. The Level field says Main record. Leave it that way.
  4. In the Main object block, choose BusinessPartners in the Object field.
  5. In the Fields field, select Phone1.
  6. In the Link to main record row, choose CardCode as Field of the main record and CardCode as Field of the main object. All fields of the row are described in Routes — Source data.
  7. In the Row limit field, enter 1.

The link fetches, for each delivery, its own customer number. The Field of the main record CardCode is a reference to the delivery, not a fixed value. Without a link or condition the query reads all customers.

If you need the customer's default contact person instead, additionally choose the Sub-object ContactEmployees in the Main object block. The Link to object CardCode = CardCode returns every contact person of the customer. With the Link button you add a second row ContactPerson = Name; now only the contact person entered as the default at the customer remains.

A query reads the customer's phone number for the delivery's customer number.

2. Create a query for each line​

Whether an item is a stock item is stored on the item, and each line has its own. So this query runs per line.

  1. Select Add additional data once more and enter Artikel as the Name.
  2. In the Level field, choose Rows of a list. The fields below change: Result type and Row limit disappear, and the row list and the Link to row appear instead.
  3. In the Row list field, enter OriginalPayload.DocumentLines[*] — the lines of the delivery.
  4. In the Main object block, choose Items in the Object field.
  5. In the Link to row row, choose ItemCode as Field of the row and ItemCode as Field of the main object too. This way each line finds its item.
  6. In the Fields field, select InventoryItem.

A per-row query contributes exactly one record for each line. That is why Result type and Row limit are dropped here: one item belongs to one line.

3. Fetch and look​

The queries run only when you fetch them in the data preview.

  1. In the data preview, select Fetch additional data.
  2. Under Display, choose Reference data and look at what arrived: under Enrichments the customer with the phone number, and on each line under Enrichments the item with InventoryItem. For ARM-4711 it says tYES, for the freight charge tNO.

If you change a query afterwards, the button reads Outdated — fetch again. The preview then still shows the old result.

If a query fails, the message says why — e.g. a field that is missing in the chosen object, or a query on the main record without a link or condition on the main object. The full list is under Routes — Source data.

The data preview shows the loaded phone number and the item detail per line.

4. Map the results​

Switch to the Mapping tab.

The phone number: Add row, Target field recipient_phone, and in the Source column select Enrichments.Kunde[0].Phone1. The [0] is there because a query on the main record always returns a list, even if it has only one match.

The lines: In the list lines with the source list DocumentLines, the Element fields now offer Enrichments.Artikel.InventoryItem next to ItemCode and Quantity.

  1. Open the list and select List settings.
  2. On the Filter tab, create a condition: Field Enrichments.Artikel.InventoryItem, Operator equals, Value tYES.
  3. Select Done.

The freight charge thus drops out of the list, ARM-4711 stays. How lists and their filters are built is shown in Map header and lines.

5. Check in the test panel​

  1. On the Mapping tab, select the Test panel button.
  2. Use the delivery 80234 as the sample message.
  3. Select Run test.

The test runs the additional data too and reads from SAP Business One for it. The result shows recipient_phone with the phone number of the customer K-10317, and lines has one entry. The target system stays untouched.

If a query on the main record returns more records than Row limit allows, the test aborts with a message that names the query. In the example, this would mean that the link hits more than one customer.

6. Activate​

Your change is in the draft. Check it and activate it; only then does the route load the details. The way there is described in Validate and activate.

When additional data is missing in operation​

If a query in operation does not find what the mapping needs, the job aborts and lands in Clarification with the reason Additional data missing. In the example, that would be a customer without a phone number. How to resolve the case is described in Resolve a clarification.

What comes next​

See also​