Create the lists and enforce uniqueness

Create Request Intake with required single-line text columns SiteCode, RequestReference and JobType. Add optional text columns Outcome and TargetItemID. Keep Title and populate it with the reference.

Create Job Register with the columns below. Create their names without spaces so the expressions can use the same internal names.

Open the RequestKey column settings and enable unique values. SharePoint supports this on single-line text, uses case-insensitive uniqueness and requires an index. Resolve existing duplicates before enabling it on a populated list. Microsoft reference.

Capture a stable key

Create an automated cloud flow using SharePoint When an item is created on Request Intake. Use the created-only trigger so writing Outcome back does not start the flow again.

Add three Compose actions, named exactly as follows:

SiteCode
toUpper(trim(coalesce(triggerBody()?['SiteCode'], '')))

RequestReference
toUpper(trim(coalesce(triggerBody()?['RequestReference'], '')))

JobType
toUpper(trim(coalesce(triggerBody()?['JobType'], '')))

Add a Condition using this expression. Compare its result with the Boolean true. The pipe character is reserved as the key separator.

and(
  not(empty(outputs('SiteCode'))),
  not(empty(outputs('RequestReference'))),
  not(empty(outputs('JobType'))),
  not(contains(outputs('SiteCode'), '|')),
  not(contains(outputs('RequestReference'), '|'))
)

On No, update the intake Outcome to Invalid input and terminate with Failed. On Yes, add Compose RequestKey:

concat(
  'portal-v1|',
  outputs('SiteCode'),
  '|',
  outputs('RequestReference')
)

Validate lessOrEquals(length(outputs('RequestKey')), 255) in another Condition. Reject an oversized key; do not truncate it, because different requests could then share the same key. Continue only on Yes.

Let the destination decide

Add a Scope named WriteRecord containing only SharePoint Create item, named CreateJob, targeting Job Register. Map Title and RequestReference to the RequestReference Compose output; map RequestKey, SiteCode and JobType to their respective Compose outputs; map SourceItemID to the trigger’s ID.

After the scope, add two parallel scopes with different Run after settings:

• Recorded: run only when WriteRecord succeeds. Update the intake item to Outcome Recorded and TargetItemID from body('CreateJob')?['ID'].
• RecoverWrite: run only when WriteRecord fails or times out. Follow the recovery checks below.

In every intake Update item action, use the trigger ID and re-supply required intake fields from the trigger. Keep receipt updates outside WriteRecord: a failed receipt update must not be misclassified as a failed job creation. Run after reference.

Read back and compare the actual record

Inside RecoverWrite, add SharePoint Get items on Job Register, named FindJob. Set Top Count to 1. In Filter Query, enter the following expression; it doubles apostrophes inside the OData string literal.

concat(
  'RequestKey eq ',
  decodeUriComponent('%27'),
  replace(
    outputs('RequestKey'),
    decodeUriComponent('%27'),
    decodeUriComponent('%27%27')
  ),
  decodeUriComponent('%27')
)

Example filter produced: RequestKey eq 'portal-v1|LEI|REQ-1042' (illustrative example). Check the column’s internal name if adapting an existing list. For large lists, use the indexed key and test lookup beyond the first 5,000 items; Microsoft documents a filtered-query limitation and pagination guidance. Get items reference.

After FindJob succeeds, check equals(length(body('FindJob')?['value']), 1). Only in its Yes branch, compare the normalised job type:

equals(
  first(body('FindJob')?['value'])?['JobType'],
  outputs('JobType')
)

• Match: update intake Outcome to Existing record confirmed, with TargetItemID from first(body('FindJob')?['value'])?['ID']. End successfully. This covers a repeat or a write whose acknowledgement was lost; do not claim to know which.
• Different job type: record Conflict - review required, keep the original job unchanged and terminate with Failed.
• No match: record Unresolved write - review required and terminate with Failed. A missing match is not proof that another write is safe.

Add a failure branch after FindJob for failed or timed-out reads. Record Recovery lookup failed if the intake list remains available, then terminate with Failed. If logging also fails, preserve the failed run for the support owner. Never treat all create errors as duplicates.

This example’s immutable payload contains only site, reference and job type. If you add amount, currency, asset or other business fields, validate and compare all relevant values before accepting a repeat.

Test the paths that ordinary demos miss

To exercise uncertain-write recovery, use a test copy that successfully creates the item and then deliberately fails its write scope. Confirm that recovery finds the committed row. Remove this test-only action afterwards; production WriteRecord must contain only CreateJob.

Give exceptions an owner and a safe next step

The operations owner decides whether two references mean the same job. The flow owner maintains mappings, access, monitoring and recovery. Nominate deputies and document how to find the destination record from an intake receipt.

Review failed runs and intake items with missing or exception Outcomes. For a recovered item, confirm the business record before replaying anything. Fix permissions or mapping faults first. Preserve the same RequestKey on a retry.

Teach users to retain their original reference and use the correction route. Restrict who can edit or delete destination keys; deleting a protected record removes its duplicate barrier. Set retention around the period in which requests may be replayed.