Correlate Records Across Systems v2

Correlating records means identifying which records in different systems represent the same business object. Each system may assign its own ID, so a Workflow needs a reliable way to connect the source record with the result of a target operation.

For example, an e-commerce platform supplies order A, and an ERP creates the corresponding sales order as ERP-10. Your goal is to retain the relationship A → ERP-10. You could store A in a supported external-reference field on the ERP order, write ERP-10 back to the source order, or use the Key/Value Node to store A as the Key and {"targetId":"ERP-10"} as its Value.

That correlation often backs Workflow logic that checks whether an order has already been processed before creating it again. It can also support Apps and reports: a lookup Workflow can combine the source order number, ERP identifier, and processing status so a user can find the matching record or investigate an exception. The correlation identifies the relationship; the Workflow must still implement the required lookup, update, and recovery behavior.

Choose an approach

Prefer the request echoed in the connector response when the selected Method provides it. Use Merge for separate collections that share a matching key, or ForEach when each source record needs its own processing sequence.

Approach Advantages Tradeoffs
1. Use Flowgear.Request — preferred Keeps the request and result together; supports bulk operations and preserves lazy processing without rereading the source for correlation. The Method must echo the request, and that request must contain the source identifier you need.
2. Use Operations.Merge Correlates independently retrieved collections by a shared key and exposes missing or multiple matches. Requires a common key and buffers all of Array2 in memory before producing results.
3. Use ForEach Keeps the current source item in scope while you write, inspect the result, and store the correlation. Per-record calls can give up bulk throughput and add request overhead.

1. Use the request echoed in the response

Most product connectors echo each write request in the corresponding output record under Flowgear.Request. The connector adds this metadata alongside the target's response, so the source values used for that operation travel with the resulting target ID. Property names are case-sensitive: use Flowgear.Request, not flowgear.request.

Check the selected Node Method's Returns to confirm the echoed shape. For example, the FreshBooks Node includes Flowgear.Request in its Create, Update, and Delete results. A query Method or a different kind of Node may return a different contract.

Worked example: retain the source and target order IDs

Suppose your target's create Method accepts the source order ID in a supported externalReference field and returns the new target ID as orderId. The following illustrates one successful result from a Step named writeOrders; these business field names are examples and must be replaced with the selected Method's actual contract:

{
  "orderId": "ERP-10",
  "Flowgear": {
    "IsSuccess": true,
    "Message": "Operation completed successfully.",
    "Request": {
      "externalReference": "A",
      "total": 125.50
    }
  }
}

The response now contains both identifiers: Flowgear.Request.externalReference is A, and orderId is ERP-10. You do not need to match two arrays by position or look up the original order again.

To retain this pair in Key/Value storage:

  1. Map the source order ID into the target Method's supported reference field before the write. The echoed request contains the input supplied to that Method, so an ID omitted from the mapping will not be recovered from the original source automatically.
  2. Add Key/Value → Set after writeOrders and set Collection to orders-written.
  3. Map its Items from the successful response records, using FILTER({writeOrders.Response}, {writeOrders.Response.Flowgear.IsSuccess} == true) for this example.
  4. Map each Items.Key from {writeOrders.Response.Flowgear.Request.externalReference}.
  5. Define Items.Value as an Object with a targetId String mapped from {writeOrders.Response.orderId}. Optionally set Items.Status to Written.

The stored entry is Key: A, Value: {"targetId":"ERP-10"}. Another Workflow can use GetSingle with the same Collection and Key to retrieve the target ID. To store the correlation in the source system instead, map the same pair into its update Method: use the echoed source ID to select the source record and write the target ID to its supported reference field.

Keep the nested Flowgear.Request fields in the response schema so they are available to mapping Expressions. Route unsuccessful results to an investigation or retry path as described in Handle Partial Batch Failures.

Why this retains lazy evaluation

A streamed integration can follow this sequence:

Read source records → Write target records → Store correlations

As the final Step consumes the write results, the connector processes further source records or batches and emits each result with its associated request. The correlation mapping takes both IDs from that one result. It therefore avoids a second read of the original source collection, which could require replay retention, and avoids collecting a separate list of all requests just to match them to responses.

This preserves lazy evaluation and streaming where the participating Methods support them. A Method may still process its first item or batch before returning, and the request copy adds data to each response. Other mappings, multiple consumers, or a Method that materializes its input can still introduce buffering.

2. Merge collections by a shared key

Use Operations → Merge when you already have two collections to correlate, such as source orders and ERP records, or source orders and stored Key/Value markers. Both collections must expose the same matching key. If the source has only A and the target has only ERP-10, you first need an external reference or an existing correlation that connects those identifiers.

Map each input item to a Key and a Value. Merge returns one item for each Array1 record and adds an Array of matching Array2 values under MergedPropertyName. No match produces an empty Array; several matches produce several values. Use consistent key types and review unexpected multiple matches before treating the relationship as one-to-one.

Correlate a batch with its markers

For example, suppose the source contains orders A and B, and the orders-written collection contains a marker for A. Configure Operations Merge with these mapped inputs:

{
  "Array1": [{"Key":"A","Value":{"orderId":"A"}}, {"Key":"B","Value":{"orderId":"B"}}],
  "Array2": [{"Key":"A","Value":{"targetId":"ERP-10"}}],
  "MergedPropertyName":"Written"
}

Array1 is built from the source orders and Array2 from Get.Items, using each stored record's Key and Value. A Step named mergeOrders returns:

[
  {"orderId":"A","Written":[{"targetId":"ERP-10"}]},
  {"orderId":"B","Written":[]}
]

Map the next write's input Array from FILTER({mergeOrders.Items}, COUNT({mergeOrders.Items.Written}) == 0). Only order B is selected. Map its order fields into the provider's required input shape, then store a marker only for a confirmed successful result.

Choose the Get date range to include every marker relevant to the source batch. An excluded or deleted marker is indistinguishable from an unwritten record in this design. Merge buffers its second Array, so keep the marker lookup bounded appropriately.

The same arrangement works with records queried from another system. For example, map the source orderId to Array1.Key and the ERP's externalReference to Array2.Key, with the ERP record as Array2.Value. Each source order then carries the matching ERP records, including their target IDs, for storage or reporting.

Merge fully reads Array2 before it starts producing results from Array1. Use it when that lookup collection has a manageable size. Prefer the echoed request when you already have the matching input attached to each write result.

3. Correlate inside a ForEach

Use ForEach when the target Method does not echo the information you need, or when each record requires several decisions or operations before you can store the correlation.

For the order example:

  1. Add a ForEach Step named processOrders and map Items from the source order collection, retaining its orderId field.
  2. Add the target write Step inside the loop and map it from the current item. The source ID is available as {processOrders.Items.orderId} throughout that iteration.
  3. Inspect the target result and confirm success. If the Method accepts an Array, supply a one-item Array for this iteration and consume its result before recording completion.
  4. Add Key/Value → SetSingle inside the same loop. Map Key from {processOrders.Items.orderId} and map Value.targetId from the successful target result.

The current source item and the target result are both in scope when you save the pair. You can instead use that pair to update a reference field in either system. Keep the write and correlation Steps inside the loop; a later root Step cannot refer to the loop's active Items value. If later Steps need a collection of pairs, expose them through the loop's Result mapping. See Container Steps and Scope.

ForEach can consume the source incrementally, but performing the target operation once per iteration can turn an efficient bulk write into many individual requests. Use the echoed-request approach for a straightforward bulk write and reserve a loop for work that benefits from per-record control.

Store a useful, reliable correlation

Use stable source IDs and separate unrelated mappings into clearly named Key/Value Collections. If IDs can repeat across source systems or accounts, include that scope in the key or Collection. Store the target ID and only the additional status or summary fields needed by your Workflows, Apps, or reports. Key/Value records belong to the current Site Environment, and each serialized Value is limited to 32 KB.

A stored correlation does not make the target write and the marker write atomic. Only record a successful write as completed, and account for concurrent runs or a failure after the target succeeds but before the correlation is stored. Eventual Consistency explains those recovery windows; Idempotent Upsert covers stronger duplicate-prevention strategies.

See also