CRM-to-OFS Duplicate Work Orders at 15-Minute Intervals
A medical equipment company integrates Salesforce with Oracle Field Service. Every 15 minutes, an integration job pulls changed cases and posts them to /activities. Daily volume is 5,000 activities, with peaks of 1,200 in a single batch. The CRM Case ID is written into a descriptive text property, not into a unique external identifier. The integration always uses POST, so each run creates new activities. Technicians see duplicate jobs; measured duplication is 6-9%, and it rises after timeouts. The batch sometimes exceeds the 120-second API timeout; retries then create another full set of activities. Priority mapping is also inconsistent: CRM sends P1, P2, High, and Medium, while OFS expects 1, 2, 3, and 4. Unknown values currently default to 3, which hides data errors. Dispatchers correct duplicates manually, about 30 minutes per day. The company wants near-real-time updates but cannot increase the timeout.
1. Design an idempotent inbound integration. What key, HTTP operation, and deduplication controls would you use?
Read the worked answer
Use the CRM Case ID as the stable idempotency key. Store it in the OFS activity external ID field, or in a dedicated unique property if external ID is not available in your API version. On each run, do not POST unconditionally. Look up the activity by that key; if it exists, PATCH only the changed fields; if it does not, POST once. This converts the integration from create-only to upsert. Add a transaction ID per CRM change set and keep a short-lived deduplication table in the middleware so a retry after a timeout does not submit the same change twice. If the middleware has no state, derive the transaction ID from the Case ID plus the CRM last-modified timestamp. Ensure the external ID is immutable; if the Case ID can change, use the CRM's immutable record ID instead. Return and log the OFS activity ID after create so later updates can target it directly. Finally, run a reconciliation report that counts activities per Case ID and flags any case with more than one active activity.
2. The batch sometimes exceeds the 120-second timeout. What changes would you make to the integration pattern, and why is increasing the timeout not the first answer?
Read the worked answer
Move large batches off the synchronous request path. Use the asynchronous inbound message scenario or queue so the middleware submits a message and does not hold a 120-second HTTP connection. Chunk the 1,200-activity peak into smaller batches, for example 200 to 300 activities per message, and send only fields that changed. If the API supports bulk operations in your version, use them for initial loads. Keep the idempotency key on every item so a retry of a chunk is safe. Increasing the timeout only hides the symptom: a larger timeout still leaves the integration vulnerable to network retries, duplicate submissions, and long recovery times. It also does not address the create-only POST that turns a retry into duplicates. After moving to asynchronous processing, monitor queue depth, message processing time, and failures by error type. Set retry with backoff and a dead-letter queue. Important: idempotency must be in place before retries are enabled, otherwise the retry policy will amplify the duplicate problem.
3. How would you fix the priority mapping and prevent unknown values from being silently defaulted?
Read the worked answer
Build an explicit translation table between CRM and OFS values: P1 to 1, P2 to 2, High to 2, Medium to 3, and any other approved value to its corresponding code. Put the translation in the message scenario XSLT or in the middleware mapping layer, not in a technician-facing field. Validate the incoming value against the allowed set before sending. Unknown values should be rejected or quarantined with the Case ID and the original value, not defaulted to 3, because a silent default hides data errors and can delay high-priority work. Add a property validation rule on the OFS side as a second line of defense. Report mapping exceptions daily and route them to the integration owner. Also document which CRM values are deprecated. Test the mapping with a fixture set that includes every allowed value, an empty value, a lowercase value, and an unknown value. The test should prove that High is not accidentally mapped to 4 and that an unknown value creates an exception rather than a medium-priority activity.