Mendix Advanced Developer: Greenleaf Foods: a nightly import that loses the whole batch

Greenleaf Foods: a nightly import that loses the whole batch

Greenleaf Foods imports supplier price lists every night. A scheduled event calls SUB_ProcessFeed, which retrieves an ImportBatch and loops over its up to 1,200 PriceLine objects. For each line it calls SUB_UpsertPrice, configured with error handling 'Custom with rollback', and commits the ImportBatch once, after the loop.

When a single line contains an invalid unit code, the entire run stops: no prices are updated, and the operations team finds only a generic error in the log. They re-run the job, and this time roughly 300 duplicate Price records appear. The upsert checks for an existing price by product code plus description, but some incoming descriptions have trailing spaces, so the check misses records that already exist. Separately, the development team added a validation-feedback activity inside SUB_UpsertPrice to warn about the bad unit code; on the import screen nothing is ever displayed, and the run behaves as though the activity were not there. The job is run by a scheduler, not by a person.

1. Explain why one invalid line stops the entire run, and redesign the error handling so the other 1,199 lines are still processed. Be explicit about what a rollback reverts in a loop.

Read the worked answer

Why nothing is saved. 'Custom with rollback' on the sub-microflow call rolls back all uncommitted changes in the current transaction, then hands control to the custom handler. Any error the handler does not swallow propagates; since the only commit is after the loop, every processed line - all 1,199 good ones - is discarded with it. The transaction boundary is the problem as much as the error handling.

Redesign.

Change the call's error handling to 'Custom without rollback' and write an explicit handler: increment a failure counter, create an ImportError record (batch, line number, message, raw value), and let the loop continue. Reserve 'Continue' for cases where a silent skip is genuinely acceptable.
Move the commit inside the loop, but not per line - one commit per line is roughly one database round trip per record and will dominate runtime on 1,200 lines. Commit in chunks (for example every 100 lines, or after the loop-of-100 inner microflow). With chunked commits, an unexpected fatal error loses at most one chunk.
In the custom handler, delete any partially created Price object, because 'without rollback' keeps changes made before the error, including a half-initialised object. Decide this explicitly rather than hoping the object is valid.
Wrap the whole job so that an unhandled failure marks the ImportBatch as Failed with a message, instead of leaving it in 'Processing' forever.

Result: 1,199 prices committed, one ImportError row, and an operator-visible report. Re-running after a partial failure is now safe only if the duplicate problem is also fixed - which it is not yet.

2. The re-run created about 300 duplicates. Explain the failure mechanism and propose fixes that make the job idempotent, ranking them by robustness.

Read the worked answer

Mechanism. Two causes compound. First, the job restarts from line 1 after a failure instead of resuming, so previously processed lines are processed again. Second, the existence check is based on product code plus description, and trailing spaces make the incoming description unequal to the stored one at the database level, so the check returns empty and a new Price is created. 'Check then create' is a match on business data, not an identity.

Fixes, most robust first:

A natural key plus a database constraint. Derive a normalised key at import time - trim, collapse internal whitespace, upper-case - and store it in a dedicated attribute. Add a Unique validation rule on that attribute, which creates a real unique constraint in the database. This holds even when two task-queue workers process overlapping chunks, which no amount of microflow checking can guarantee.
An idempotency ledger. Persist (batchId, lineNumber) or the supplier's own line identifier in an ImportLog entity with a unique constraint, and skip lines already recorded. This makes the batch resumable and gives you an audit trail of what was imported when.
Resume, don't restart. Keep the last successfully committed chunk in the ImportBatch and start the next run from there.

Handling the constraint. A unique violation surfaces at commit, so it must be caught by error handling around the commit and treated as 'already imported' (log and continue), not as a fatal error. Also normalise before comparing, and treat the normalised key as the thing the business agrees is the identity of a price - if two genuinely different products normalise to the same key, that is a data-quality finding, not something to paper over.

3. Why does the validation-feedback activity never show anything, and what should replace it for a scheduled, unattended import?

Read the worked answer

Why it fails silently. Validation feedback attaches a message to an object in an open client page - it requires the object to be part of a page's context or a member of it, and it stores the message in that client's state, not in the database. A scheduled event has no client session and no page, so the call has no target. It does not raise an error; the message simply goes nowhere. The same applies to task-queue workers and to microflows reached from a published REST operation.

What to use instead. Model the outcome as data:

An ImportError entity with the batch, line number, severity, message and the offending raw value, written from the custom error handler.
A ImportBatch status enumeration (Queued / Running / Completed / Completed with errors / Failed) plus counters for processed, succeeded and failed, so an operator can see at a glance whether the run is acceptable.
A monitoring microflow or scheduled event that raises an alert when the batch is Failed or when the error count exceeds a threshold - a nightly job that quietly 'succeeds' with 1,200 failures is worse than one that fails loudly.
A retry entry point per line: a RETRY button on the error grid calls a microflow that reprocesses exactly that line, so fixing one bad unit code does not require re-running 1,200 lines.

Rule of thumb to state in review: validation feedback is a UI concern for interactive flows; anything that runs unattended must persist its errors and must have an observable success criterion.