Large lookup tables and string maps in Workday Orchestrate

Where a large lookup table should live in Orchestrate

A lookup table with more than a handful of entries does not belong in an expression. Load the rows at run time from a CSV file, a RaaS report or a WQL query, fold them into a StringMap with a String Map aggregation on a Loop, and read each value with getOrElse(key, default). If an administrator should maintain the values in the tenant, put them in an integration map on the integration system and read them with intsys.mapLookupWithDefault. If the lookup returns a whole record rather than one code, use a Join Loop.

Why a hard-coded map stops working past a few entries

The map global functions build maps inline. stringMap takes at most three key/value pairs, and Workday's reference tells you to use put or putAll for anything larger. Written inline, a five-entry map looks like this (sample apps call the same Map.stringMap function):

Map.stringMap("US", "USA", "CA", "CAN", "GB", "GBR")
  .put("DE", "DEU")
  .put("FR", "FRA")

That is fine for a fixed list of five country codes. It is the wrong place for 400 cost center mappings. Each change means editing the orchestration, building the app and deploying it again, and nobody outside the developer can see or correct the values. Keep inline maps for values that are part of the logic, such as a status code to label table, and keep business data in a data source.

Build a StringMap from a report, query or file with a String Map aggregation

Aggregation strategies collect a value from each pass through a Loop, Batch Loop or Join Loop and output one result after the loop finishes. The String Map strategy takes a key and a String value from each iteration and outputs a StringMap.

1. Fetch the lookup rows: a Send Workday RaaS Request, a Send Workday API Request to the WQL endpoint, or a CSV you already have as data.
2. Add a Loop over the rows. For JSON, iterate with iterator("$.Report_Entry[*]") or whatever path holds the rows. For CSV, use rows() so the header is skipped.
3. On the loop's three-dot menu, select Add Aggregation, give it a reference name such as codeMaps, and click Add Output.
4. Pick the String Map strategy. In Select String Key and Select String Value, enter the expressions for the source code and the target code, for example data.LoadMap.item.stringAtJsonPath("$.legacyCode") and data.LoadMap.item.stringAtJsonPath("$.costCenterId").
5. Optionally set a Condition so that rows with an empty key are left out, and tick Fail When No Inputs if an empty lookup table should stop the run instead of silently mapping nothing.

For a CSV with a defined format, columnValue("Legacy Code") reads a column by name. Without a format, columnValueAtIndex(0) reads it by position.

You can add several String Map outputs to the same aggregation. Workday's AP e-Invoice sample app does this: one loop over the lookup rows produces two maps, one keyed to the app's own reference values and one to Workday IDs. It also filters the WQL query with IN (...) so that only the keys present in the current batch are loaded, which keeps the map small even when the table behind it is large.

Read the map with getOrElse, not get

The StringMap member functions give you three ways to read a value:

• get(key) returns the value if one exists.
• getOrElse(key, default) returns the value, or the default you supply when the key is missing.
• containsKey(key) returns true or false, which you can use in a Branch on Conditions step.

Inside the main loop, getOrElse keeps one missing code from turning into an unclear failure later on:

data.codeMaps.costCenterMap.getOrElse(
  data.Workers.item.stringAtJsonPath("$.legacyCostCenter"),
  "UNMAPPED")

The AP e-Invoice sample does the same thing when it enriches each invoice line, using emptyString() as the default. Pick a default you can search for later. A blank value is hard to tell apart from a blank source field.

When the same code can appear twice in the source, the docs don't say which row wins in the aggregation. Remove duplicates in the report or query that feeds the map, so the result does not depend on row order.

Use integration maps when an administrator should own the values

Integration maps are configured on the integration system in the tenant, not in the orchestration definition on the Developer Site. They are therefore available to orchestrations built on the Workday Integration System template. An administrator maintains them from the integration system's related actions with Integration System > Configure Integration Maps. Which maps appear there depends on the template and the integration services configured on the system. Each map can have a default value, and Workday's training recommends setting one so that unmapped values are easy to find.

The intsys functions read them:

mapLookup, mapLookupWithDefault and mapContains all throw an exception if the named map doesn't exist, so a typo in the map name fails the run rather than returning blanks. Workday's course manual recommends reading map and attribute values once, in a Create Values step near the start, and referring to that step downstream instead of calling intsys throughout the flow. The Orchestrate Sampler app's file-processing suborchestration uses intsys.mapLookupWithDefault(data.start.mapName, data.Line.Key, emptyString) in exactly this way.

Two practical points. First, an orchestration built on the Integration System template, or a suborchestration that calls intsys, has to be deployed and run once in the tenant before the step debugger can run it. Second, if you only need one setting per environment, such as a target folder or a company code, rather than a table, use an app attribute (String, Numeric or Boolean) and assign the value per environment in App Manager. If you are coming from Cloud Connect, the same idea is covered in integration field overrides and maps.

Switch to a Join Loop when the lookup returns a record

A StringMap holds one String per key. If each match needs several fields, such as a cost center's ID, its manager and its company, build a Join Loop instead of several parallel maps. The Join Loop iterates a primary dataset and, for each record, finds the matching record in a joining dataset by key. Its Missing Match Strategy setting (Alternate Processing, Complete with Error or Skip Record) defines what happens to unmatched rows, and isDataDefined tells you inside the loop whether a match was found. The maximum size of a joining dataset is 500 MB. The Join Loop guide covers its settings, and merging two RaaS reports is the most common case.

How large is too large for an in-memory map

Workday sets a 200 MB limit on data an orchestration holds in memory at once, which includes temporary values created while evaluating expressions. Large documents are kept on disk in file-backed structures. String values are held in memory, according to Workday's data types page, so a map of strings counts against the memory limit, not the 2 GB disk allowance.

A cost center or pay code table of a few thousand rows is small next to that limit. A lookup built from a full worker population, or one that copies long text values into the map, is where you should either filter the source down to the keys this run needs, as the AP e-Invoice sample does, or match the records with a Join Loop instead of copying the table into a map.

Prove the lookup matched what you expected

A lookup that quietly returns your default for every row still lets the run finish. Check it:

• Log the map size after the loading loop with a Log step: data.codeMaps.costCenterMap.size().toString(). Compare it with the row count of the source report.
• Add a Count output to the main loop's aggregation with a condition such as data.codeMaps.costCenterMap.containsKey(data.Workers.item.stringAtJsonPath("$.legacyCostCenter")).not(), and log the number of unmapped rows. For an integration map, count the rows where intsys.mapContains is false.
• Read a few mapped values back from the target system, not from the orchestration's own log.

If the count of unmapped rows is not zero, the source key format is usually different from the map's key: leading zeros, trailing spaces or different case. Normalize both sides the same way, with trim() or upperCase(), before you build the map and before you look a key up. For more on run-time evidence, see logging in Orchestrate.

kiweely can run the RaaS report or WQL query behind the lookup against the client's tenant before you build the map, so duplicate keys and formatting differences show up before the first run. After a run, it can read the target records back to confirm the mapped values landed.