Build a FileMaker import batch report: applied, skipped and unresolved rows

Report what happened to each source row

A useful import report answers two questions: what was saved, and what still needs attention? An overall “Import complete” message cannot explain a row rejected by validation, an intentional omission or a write that failed halfway through.

Give the batch an identity and give each staged row an identity. Record one current outcome per row, then keep processing attempts in a separate history. Retrying a row should add an attempt, not another source row or another success count.

Separate the batch, the row and the attempt

Use Pending while a row awaits processing. Applied means the intended business change and its durable row receipt committed together. Skipped means an operator or explicit rule intentionally omitted it. Unresolved means it could not be applied and still needs a decision or correction.

Initialize OutcomeRevision to 0. Every supported writer must lock the row and increment this stored number whenever it changes the row’s input or current outcome, including corrections and operator exclusions. Do not reset it. A later failure report can then detect that its view of the row is stale.

Give each attempt a UUID before starting work and enforce its uniqueness in ImportAttempt. BasedOnRevision records the row revision read under lock. If the row could not be locked, leave that revision empty and record only the attempt failure. A retry of the same diagnostic save must reuse its AttemptUUID; a new processing attempt gets a new one.

Store short reason codes such as missing_parent, invalid_date, duplicate_source_id and validation_failed. Show readable explanations in the interface. Do not put credentials or unnecessary personal information into diagnostic logs.

Reconcile ten rows

The report should read: 10 source rows = 6 applied + 2 skipped + 2 unresolved + 0 pending. During processing, Pending belongs in the equation too. A completed review can still have unresolved rows; use a label such as “Finished with issues” instead of “All imported.”

Calculate counts from saved ImportRow outcomes within this BatchUUID. Do not calculate them from how many times the loop ran. Keep a separate attempt count if it helps diagnose repeated failures.

Make the receipt agree with the write

Before changing business data, lock the staged row and inspect its current outcome. An already-applied row is not eligible for the same change again. Within one worker window, group the target change, the Applied receipt and the successful attempt in one transaction.

Process one RowUUID with a new AttemptUUID:
    Begin transaction; lock and re-read that ImportRow.
    If locking fails: revert; record only the failed attempt; return.
    If outcome is Applied or Skipped:
        Revert this empty transaction; return without processing.
    Capture OutcomeRevision as this attempt's BasedOnRevision.
    Validate the proposed change and resolve target identity.
    Write the business change using its stable identity.
    Set ImportRow outcome = Applied, TargetUUID = target.
    Increment OutcomeRevision.
    Create the successful ImportAttempt.
    Check every step; commit only if all succeeded.
    If commit succeeds: return Applied.
    On failure, preserve the error and confirm rollback.

Record that failed attempt in a separate short transaction:
    Reacquire the ImportRow lock and re-read its saved state.
    If this AttemptUUID already has a receipt: return that receipt.
    Create the immutable failed ImportAttempt.
    Only if OutcomeRevision still equals BasedOnRevision
    AND the current outcome is Pending or Unresolved:
        Set outcome = Unresolved with this failure's reason.
        Increment OutcomeRevision.
    Otherwise preserve the row's newer state unchanged.
    Commit the attempt and any allowed row update together.
    On lock/write/commit failure: revert; show reporting failure.
    Retrying this diagnostic save uses the same AttemptUUID.

The failed-attempt record cannot survive if it is only written inside the transaction that is reverted. That is why the failure path writes its report separately. Rollback also releases the earlier row lock, so the later report must acquire a new lock and check the revision. A historical failure may be recorded without changing the row’s current outcome.

Treat an existing attempt receipt as immutable: after acquiring the row lock, verify its RowUUID and result, end the diagnostic transaction without changes, and return it. If the initial processing attempt never acquired a row revision, its diagnostic write may record the failure but must not change ImportRow. If any diagnostic save fails, retain its AttemptUUID and error for a controlled retry; do not claim the report was saved.

If the app crashes before a report can be saved, the row may remain Pending. On an uncertain connection or commit result, inspect durable target identity and receipts before deciding what to do; do not write a failure outcome until rollback is known. The revision rule must apply to every writer, otherwise the protection is incomplete.

Claris’s Commit Transaction reference describes the commit result. Capture it immediately; a later successful step must not overwrite the error you need to report.

Capture the commit result

Place this result-handling fragment immediately after the Commit Transaction step in the processing script. The successful target write and Applied row receipt belong inside the preceding transaction; this fragment does not implement them.

Set Variable [ $commitError ; Value: Get ( LastError ) ]
If [ $commitError ≠ 0 ]
    Exit Script [ Text Result: JSONSetElement ( "{}" ;
        [ "outcome" ; "unresolved" ; JSONString ] ;
        [ "error" ; $commitError ; JSONNumber ] ) ]
End If
Exit Script [ Text Result: JSONSetElement ( "{}" ;
    [ "outcome" ; "applied" ; JSONString ] ;
    [ "error" ; 0 ; JSONNumber ] ) ]

The caller must check the result shape and record a failure if it is absent or invalid. A successful script return is not a replacement for the durable row receipt. On an uncertain reconnect, re-read that receipt before offering Retry.

Correct one row without recounting the batch

Supply the missing parent for row 9 and retry it. The batch now has 7 applied, 2 skipped and 1 unresolved. Its source total is still 10. Its attempt history includes the original unresolved attempt and the successful retry.

If row 10 fails twice, it remains one unresolved row with two failed attempts. If the operator intentionally excludes it, require a reason and move that row to Skipped; do not erase its earlier failure history.

Importing the same file as a brand-new batch is a different question. If the business must prevent duplicate changes across batches, enforce source identity or a business operation key on the target as well. A unique RowUUID only protects the same staged row.

Review the report as an operator

Show the four outcome counts, the reconciliation equation, and a filtered list for unresolved rows with source row numbers and next actions. Offer a CSV report containing the saved outcomes, not an export of transient script variables.

In a working copy, test a validation failure, a locked target, a diagnostic-save failure and a repeat click after success. No failed write should increase Applied. No repeat action should create a second target for the same operation. Confirm the counts again after reopening the file.

Test this interleaving explicitly: A fails and rolls back; B retries the row and commits Applied at a newer revision; A then records its old failure. A’s failed attempt should appear in history, but the row must remain Applied with B’s TargetUUID and revision. Repeat with an operator marking the row Skipped, and with a newer correction that leaves it Pending. None may be overwritten by A’s late report. Retry A’s diagnostic save twice and confirm only one receipt exists for its AttemptUUID.