Let me provide a comprehensive solution that addresses all aspects of this duplication issue - from Power Automate idempotency patterns through to proper error handling.
Root Cause Analysis:
Your issue stems from three compounding factors: Power Automate’s at-least-once delivery guarantee, concurrent flow execution under load, and the lack of transactional isolation in REST API calls. When 50+ orders hit simultaneously, multiple flow instances can pass your validation check before any commits occur.
Power Automate Idempotency Pattern Implementation:
First, restructure your flow with a proper idempotency layer. Add these actions at the very beginning:
- Generate Idempotency Key (Compose action):
{
"key": "@{concat(triggerBody()?['PortalOrderID'], '-', triggerBody()?['LineNumber'], '-', triggerBody()?['ProductID'])}",
"timestamp": "@{utcNow()}"
}
-
Check Idempotency Log (OData query to custom table):
Query your SalesLineIdempotencyLog table for this key. Use $filter with the exact key match.
-
Conditional Branch:
If key exists AND ProcessedDateTime is within last 24 hours, terminate flow with success status. This handles both duplicates and legitimate retries.
Trigger Deduplication Strategy:
The trigger itself needs configuration changes. In your Power Automate trigger settings:
- Enable ‘Split On’ if processing array inputs
- Set concurrency control to limit parallel runs (start with 10, tune based on load)
- Add a ‘Delay’ action of 2-3 seconds after trigger to allow database commits to complete
- Implement exponential backoff in retry policy
This doesn’t prevent duplicates entirely but reduces race condition windows significantly.
Line Item Uniqueness Validation with Locking:
Your current validation has a time-of-check-time-of-use (TOCTOU) vulnerability. Implement atomic check-and-set:
// Power Automate HTTP action to D365 custom API
POST /api/services/SalesLineService/CreateLineWithLock
{
"PortalLineID": "@{triggerBody()?['LineID']}",
"OrderID": "@{triggerBody()?['OrderID']}",
"ProductID": "@{triggerBody()?['ProductID']}",
"Quantity": @{triggerBody()?['Quantity']}
}
// API should: 1) Acquire row-level lock 2) Check existence 3) Insert if not exists 4) Release lock
// Return 409 Conflict if duplicate detected
Create a custom X++ service class that wraps the sales line creation in a transaction with explicit locking.
Error Handling in Workflows:
Your current error handling isn’t catching duplicates because they’re succeeding at the API level. Implement these error handling patterns:
- Configure Retry Policy: Set to exponential backoff with 3 retries, 5-second initial interval
- Add Scope Actions: Wrap your D365 create operations in a Scope action to catch and handle specific errors
- Duplicate Detection Handler: Add a parallel branch that queries for duplicates 30 seconds after creation and logs/alerts if found
- Idempotency Log Update: Always log the attempt, even on failure, with status field (Success/Failed/Duplicate)
Comprehensive Flow Structure:
Trigger: When item created
↓
Compose: Generate idempotency key
↓
Get Rows: Check idempotency log (OData)
↓
Condition: Key exists?
Yes → Terminate (already processed)
No → Continue
↓
Insert Row: Log idempotency key with 'Processing' status
↓
Scope: Sales Line Creation
↓
HTTP: Call D365 REST API with idempotency headers
↓
Condition: Success (200/201)?
Yes → Update idempotency log to 'Success'
No → Update idempotency log to 'Failed' + retry logic
D365 Custom Table Setup:
Create table ‘SalesLineIdempotencyLog’:
- IdempotencyKey (String, 100, Primary Index)
- PortalOrderID (String, 50)
- PortalLineID (String, 50)
- SalesLineRecId (Int64, nullable)
- ProcessedDateTime (DateTime)
- Status (Enum: Processing/Success/Failed/Duplicate)
- RetryCount (Integer)
Add an index on (IdempotencyKey, ProcessedDateTime) for fast lookups.
Inventory Allocation Protection:
Since duplicates cause over-allocation, add a validation step in your sales line creation that checks total allocated quantity against available inventory before confirming. Implement this in X++ as a pre-create validation.
Testing and Validation:
- Use Power Automate’s test feature with concurrent runs to simulate load
- Monitor the idempotency log for duplicate key attempts
- Verify inventory allocation matches actual order quantities
- Check that legitimate retries (after failures) work correctly
Performance Considerations:
The idempotency check adds ~200ms per flow run but prevents costly duplicate processing and inventory reconciliation. Under your 50+ orders/hour load, this is negligible compared to duplicate cleanup costs.
Monitoring Setup:
Create a Power BI report or D365 workspace that shows:
- Duplicate attempts prevented (idempotency log hits)
- Flow execution times and retry patterns
- Inventory allocation accuracy metrics
This complete solution addresses all four key focus areas: idempotency patterns through the key-based locking mechanism, trigger deduplication via concurrency controls, line item uniqueness through atomic check-and-set, and comprehensive error handling with status tracking.
This draft is based on general Microsoft Dynamics 365 knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.