Use Case: Tracking Adjusted Sales Order Line Items for External System Sync
We have a situation where customers need to view their sales orders on our website. Unfortunately, direct access to the API isn't feasible due to high traffic, so we need to sync the data. The challenge is that sales orders (SOs) are frequently adjusted — items are added or removed up until shipping. When items are "removed" from the SO, I need to reconcile these deletions in an external system.
Goal: Capture deleted line items on Sales Orders to track and reconcile them with our external system. Initially, I considered using push notifications, but they can sometimes be unreliable (e.g., missed messages). Instead, I'm looking for a way to export all Sales Order Line items to an external table (Azure SQL Server) on a nightly or weekly basis. I’d then run a reconciliation job to manage the deletes.
Current Approach & Challenges
I attempted an export scenario that includes NoteID, InventoryID, Branch Name, CreatedOnDate, LastModifiedOn, and Order Number, which would export the data into a table. However, the dataset is massive. Even with a rolling 12-month export, I still get around 1.5 million rows of data (6 columns).
Questions:
- Is there a better way to achieve this?
- I tried using OData with Azure Data Factory (as part of my other sync jobs), but it's slow to page through and extract all the records.
- When using export scenarios, does Acumatica export the entire dataset each time, or is there an option to "sync" with the destination table (like an upsert)? Ideally, I'd like a replica of all active SO line items that’s automatically updated. If I could get that, it would solve a lot of challenges.
Any insights on improving this workflow or optimizing the data transfer would be much appreciated! Everyone on here has been awesome...I really appreciate the help!
