Importing customer orders into Business Central from six formats, and handling the failures

A Quebec distributor gets orders from six large customers, each in its own format: CSV and Excel files from retailer portals, distributors and marketplaces, up to about 300 orders in a single file. They were being keyed in by hand. They now land in Business Central as sales orders on their own.

The proof of concept answered "can this work?" in a few days. The rest of the work answered the harder question: what happens when something goes wrong, and who finds out?

The pipeline

1. A file watcher (a small Windows service) picks up new files in the folder where they already arrive, and pushes them to Azure Blob Storage. Nothing changes for the customers.
2. An Azure Function (C#) reads each file with a normalizer per source and turns it into one common order schema. Adding a source means adding a normalizer; nothing downstream changes.
3. An AL extension exposes a custom API. The Function posts the normalized orders into staging tables, and a processing codeunit creates the Sales Header and Sales Lines.

Keeping the parsing outside Business Central keeps the AL side about business rules only, and the format-specific mess (Excel quirks, column names, date formats) in code that's easy to unit test.

Business rules in AL

• Customer resolution from a setup table keyed by source and country.
• Tax area from the ship-to province, taken from the file's address or from the store.
• Freight from a grid kept in Business Central.
• Price from the file or from Business Central depending on the source, with a warning on the order when it came from the item card.

The failure cases

This is where the time went.

• Duplicates. Each source has its own duplicate key, stored in External Document No.. An order whose key was already created is rejected, so a file dropped twice, or retried, never creates orders twice.
• Half-created orders. Creating an order takes several steps (header, lines, processing). If an order is rejected after its header exists, the half-created order is deleted. Nothing incomplete is left behind.
• Bad lines. For a missing or unknown item, each source has a rule: reject the whole order, or skip the line with a warning. An order with no line left is rejected. Nothing is ever created with an empty item.
• Retries. If a call to Business Central fails mid-file (network, timeout), the inbox picks the file up again on its next sweep, up to three times, then moves it to a failed container. Orders already created on the earlier attempt are rejected as duplicates, which is exactly why the duplicate key matters.
• A file that crashes the Function goes to failed too, and the error message goes to the log instead of a console nobody reads.

A page for customer service

A Business Central page lists every imported order with its state: created, rejected (and why), archived. It opens on the last seven days, with views by period and by state. After fixing the cause (adding a missing item, correcting a price), customer service uses Retry on the rejected order. Archive and Unarchive keep the list short, and "Show archived" works inside the saved views.

That page is what makes the integration usable: the people who own the orders see the problems, not the developer.

Tests

• 33 AL tests in a separate test app cover the import rules: customer resolution, taxes, prices, duplicates, rejection and cleanup. They run in a sandbox, never against production.
• The normalizers have their own tests, on small files built inside the tests (no customer data), and they run in CI.

When a rule changes, the tests say right away whether something else broke. On an integration with six sources, that's the difference between changing a rule in an afternoon and being afraid to touch it.

Worth knowing if you build this

• Decide the duplicate key per source first. Retries, re-dropped files and recovery all depend on it.
• Make every failure land somewhere a person looks, inside Business Central if possible.
• Keep customer data out of the test files. Fabricate small files that hit each rule.

I wrote about a different Azure Functions sync before, for a scheduler working both ways.

Building order imports into Business Central? Let's talk.