Orchestrate Join Loop explained: missing matches, skipped records, multiple keys and the current layout

How the Join Loop matches records

A Join Loop iterates over one dataset, the primary dataset, and for each record looks up the matching record in one or more joining datasets by a key you define on both sides. The loop body runs once per primary record with the matched data in scope, and two settings decide what happens when a record has no match or more than one: the Missing Match Strategy and the Multiple Match Strategy.

Because the primary dataset drives the loop, a joining record that no primary key points to is never visited. The keys on both sides are String expressions that must produce the same value, and the Missing Match Strategy decides whether a primary row without a match disappears without an error.

Primary Dataset and Joining Dataset are the old Join On and Join With

The current component reference groups the settings under Joined Datasets Configurations, where you choose Primary Dataset or Joining Dataset and configure each. Older material, including Workday's Orchestrate course manual and the logic-components overview, still calls them the Join On dataset and the Join With datasets. They are the same thing, and the expression functions kept the old names: noJoinOnRecords, unusedJoinWithKeys.

For the primary dataset you set an Output Name (the name of the current record inside the loop), an Iterator and a Data Iterator Type (AutoType lets Orchestrate work out the type). The match handling sits on the primary dataset under Error Handling.

For each joining dataset you set a Primary Dataset Key, an Iterator, a Data Iterator Type, a Joining Dataset Key and, optionally, Return Value expressions that expose individual values instead of the whole joining record.

A worker-to-cost-center join, as it looks in a flow's source, uses the loop's step name and the output names you chose:

Primary iterator:     data.GetWorkers.response.iterator("$.data[*]")
Primary dataset key:  data.JoinLoop.worker.stringAtJsonPath("$.costCenterID")
Joining iterator:     data.GetCostCenters.response.iterator("$.data[*]")
Joining dataset key:  data.JoinLoop.costCenter.stringAtJsonPath("$.costCenterID")

The joining key expression refers to the joining record by its own output name (costCenter), even though you write it inside the join's configuration. Workday's sample orchestrations write it this way, for example data.JoinLoop.xml.stringAtXPath("/*/EmailID") for a joining dataset whose output name is xml. Pointing the joining key at the primary record's name instead makes every lookup compare against the wrong value.

Why the Join Loop drops unmatched rows

When a primary record finds no match, the Missing Match Strategy decides:

• Skip Record: the record never enters the loop body.
• Complete with Error: processing stops and an error is logged.
• Alternate Processing: the record enters the loop body anyway, and you handle the gap yourself.

With Skip Record, a primary row with a key the joining dataset does not contain produces nothing downstream, so an output file is shorter than the input and no step fails. If every row is dropped, look first at the keys rather than the data. Build both keys as if the comparison were exact, because nothing in the component's settings normalises them. 00123 and 123, ab-01 and AB-01, or a trailing space all fail to match. Normalise both key expressions the same way with String functions such as trim(), upperCase() or leftPad() before comparing.

The joining side has no equivalent setting. A joining record whose key no primary record uses is not an error and does not run the loop body; it is left over. Those leftovers are what the residual functions report.

Keep unmatched records with Alternate Processing and isDataDefined

To output every primary record whether or not it matched (the equivalent of a left outer join), choose Alternate Processing and branch inside the loop with isDataDefined(producerID, outputName), which returns true when that step-and-output combination exists. In a joined record that did not match, the joining output is simply not defined:

isDataDefined("JoinLoop", "costCenter")

Put that in an If branch: the true branch reads data.JoinLoop.costCenter, the false branch writes a default or an exception row. One of Workday's App Catalog samples uses the same check against a Return Value name, isDataDefined("JoinLoop", "clientContractorId"), so it works for returned values as well as whole records.

Both match-handling sections also let you define a custom log message with Expression Builder, which is the simplest way to record which key went unmatched.

Duplicate keys in the joining dataset

When a primary record matches more than one joining record, the Multiple Match Strategy applies: Complete with Error, Skip Record, Use First, Use Last or Use All. Use First and Use Last pick a single record by position, which only gives a stable answer if the joining dataset arrives in a meaningful order, such as a report sorted by effective date. Use All makes every match available in the loop body, which is what you want for one-to-many data such as a worker and all their dependants.

If you choose Skip Record here, the keys that were dropped because they appeared more than once are listed by unusedMultipleMatchJoinWithKeys(outputName).

Reading skipped and residual records after the loop

Workday documents a JoinResiduals type for what the join did not use. Its functions, from the JoinResiduals reference:

The reference documents the functions but not where the JoinResiduals value lives. Workday's project forecasting sample reads it after a Join Loop named JoinLoop as data("JoinLoop", "joinResidual"), for example data("JoinLoop", "joinResidual").unusedJoinWithKeys("ProposedRFLRow"), and logs .summary() from the same value. Pick it from the step's outputs in Expression Builder to be sure of the name in your build. unusedJoinWithKeys plus dataJoinedWith is how you recover "right-only" records when you need a full outer join, and summary() is a ready-made line for an integration message.

Joining on more than one key

There are two different needs here.

Several lookups, each on its own key. A Join Loop can hold multiple joins, each with its own keys and joining dataset. Workday's AP e-invoicing sample app enriches each invoice in one loop with five joins: companies on company name, suppliers on supplier name, currencies on currency code, and so on. With Alternate Processing, check each joining output separately with isDataDefined, because one record can match the supplier but not the company.

One lookup on a composite key. The key fields are single String expressions, so build the same composite string on both sides:

Primary dataset key:
data.JoinLoop.worker.stringAtJsonPath("$.employeeID").append("|").append(data.JoinLoop.worker.stringAtJsonPath("$.positionID"))

Joining dataset key:
data.JoinLoop.assignment.stringAtJsonPath("$.employeeID").append("|").append(data.JoinLoop.assignment.stringAtJsonPath("$.positionID"))

The same e-invoicing sample builds its tax lookup key this way, by appending several line fields with append. Adding a separator is a recommended practice rather than a Workday requirement: without one, 12 + 345 and 123 + 45 produce the same key.

Size and which dataset goes where

The joining dataset can be at most 500 MB, and it is the one queried repeatedly for every primary record. The Join Loop reference recommends putting a dataset with unusually large records on the primary side. The 500 MB cap applies only to the joining side, so when one dataset is much bigger, making it the primary is the safer choice. If both sides are large, trim the joining side before the loop (fewer report columns, a narrower query). For loop sizing in general, see Orchestrate looping and pagination limits.

The Join Loop is available in the Synchronous, Asynchronous, Business Process, Home Card, Integration System and Custom Agent Action templates.

Collect the output and prove the join worked

Add an aggregation to the Join Loop (its step menu, Add Aggregation) to turn the iterations into one document: JSON Fold for a JSON array, CSV Fold for a file, or Count with a condition to count matched rows. The CSV walk-through is in processing a CSV file in Orchestrate, and a full report-to-report example is in merging two RaaS reports.

A join is proven by numbers, not by a successful run. Count the primary records going in, count the rows the aggregation produced, and account for the difference with the residual functions: the skipped records and the unused joining keys should explain every row that is missing. Writing summary() into an integration message or a log puts that explanation on the run itself. While building, Orchestration Builder's debugger runs the loop one step at a time and shows each step's outputs, so you can watch a known key match before running the full set. If the run fails rather than undercounts, the Orchestrate errors guide covers reading the failed steps.