The killer reconciliation card. Shows commerce-vs-Business Central gap broken down by reason, not just the dollar number.
At a glance
Per-order audit trail of every commerce order vs its corresponding BC Sales Order or Posted Sales Invoice. Each row carries the commerce platform Order ID, the BC SO Number (or absent flag), the gap amount, and the reason code. This is the BC Functional Consultant’s daily worklist.
| What it counts | For every commerce order in the window: cross-references the BC salesOrders and salesInvoices OData collections by External Document No. (or workspace-mapped commerce order ID). Each unmatched, partially-matched, or refund-mismatched pair becomes a row with reason code: Released-Not-Invoiced, Voided, Sales Credit Memo, Unmapped Customer, Period Boundary, FX Translation, Tax Reclassification, Unknown. |
| VAT / tax treatment | Per-row, both sides normalised to net-of-tax. |
| Shipping | Reconciled per row. Mismatch flagged as a sub-reason. |
| Currency | Per-row, both sides translated to Reporting Currency at the same period-average rate. |
| Multi-Company | Each row carries the BC Company Name. Rows can be filtered by Company. |
| Output format | CSV-exportable table with: commerce order ID, customer name, order date, commerce gross, BC SO Number, BC posted amount, gap amount, reason, suggested action, age. |
| Time window | 30D (the same window as Revenue Gap vs Commerce, the headline) |
| Alert trigger | >5% gap unreconciled (same as the headline card) |
| Roles | owner, finance |
Calculation
Calculated automatically from your Microsoft Dynamics 365 data. See the At a glance summary above for what the metric tracks and the worked example below for a typical reading.Worked example
A US wholesale distributor on BC, BigCommerce B2B + Shopify Plus DTC. 30-day window 14 Mar 26 to 12 Apr 26. Headline gap: $680,000. The breakdown table:| Commerce Order ID | Customer | Commerce Gross | BC SO Number | BC Amount | Gap | Reason | Suggested Action | Age |
|---|---|---|---|---|---|---|---|---|
| BC-PO-2284912 | Acme Tech Distribution | $84,200 | SO-208441 | $0 | $84,200 | Released-Not-Invoiced | Post Sales Invoice (shipped 2 Apr 26) | 12 days |
| BC-PO-2284866 | NorthEast Components | $42,400 | SO-208533 | $0 | $42,400 | Released-Not-Invoiced | Post Invoice (partial ship) | 9 days |
| BC-PO-2284733 | RegionalTechCo | $28,800 | SO-208612 | $0 | $28,800 | Pending Approval >72hrs | Escalate to Finance Director | 7 days |
| SH-#10044128 | DTC Customer 0044128 | $284 | (none) | $0 | $284 | Unmapped Customer | Create BC Customer record | 2 days |
| BC-PO-2284601 | DeltaCorp Industries | $14,800 | SO-208702 | $0 | $14,800 | Customer.Blocked = Ship | Release credit hold | 14 days |
| ADC-PO-44821 | Wholesale-X | $18,400 | (none) | $0 | $18,400 | Unmapped Customer | AL Connector mapping issue | 21 days |
| SH-#10044211 | Returned-Order | $-440 | SCM-2241 | $-440 | $0 | Sales Credit Memo (matched) | None | n/a |
| … 247 more rows … |
- The top 6 rows account for 680K gap. The Pareto principle holds: a small number of high-value stuck orders concentrate the financial pain. The Controller’s daily routine starts at the top of this list.
- Three orders are stuck > 12 days. The DeltaCorp credit hold is 14 days old, the Wholesale-X unmapped customer is 21 days old. These are red-flag cases needing escalation to senior management.
- Sales Credit Memo SCM-2241 is correctly matched. The card filters this row out of the gap calculation; the original order shows the offsetting refund. Showing matched rows in the table at all is a deliberate audit feature: the Controller can search for any commerce order ID and see the BC counterpart.
- CSV export is the primary use mode. The Controller exports the full table at 8am Monday, sorts by gap descending, and assigns rows to operators by reason: stuck-Invoice queue → AR clerk; unmapped-customer queue → BC Functional Consultant; credit-hold queue → CFO conversation.
- Trend over weeks shows process health. When the merchant first onboarded Vortex IQ, this table had 712 rows and 680K, with the residual being structural (Net-30 ship-to-bill lag) not preventable. Healthy steady-state.
Sibling cards merchants should reference together
| Card | Why pair it with Revenue Gap, Detailed Breakdown |
|---|---|
| Revenue Gap vs Commerce | The headline; this card is the drill-down. |
| Commerce Orders Without BC Sales Order | Specifically the Unmapped Customer subset. |
| Sales Orders Blocked | Released-Not-Invoiced + Credit Hold rows. |
| Revenue Booked into GL | The BC denominator. |
| Sales Credit Memo Value | Matched-CM rows lineage. |
| Revenue Gap Spike Alert | Fires when this table grows. |
| SO to Invoice Lead Time | Diagnoses the structural Released-Not-Invoiced lag. |
Reconciling against the vendor’s own dashboard
Where to look in Business Central: There is no equivalent in BC because the join (commerce → BC) cannot be made inside BC alone. The closest BC-side worklist is:Sales > Sales Orders filtered toThe card does in 15 minutes what a BC Functional Consultant + Excel takes 4 hours per week to compile. Why our list may legitimately differ from a manual export:External Document No.IS NOT BLANK +Status = ReleasedAL Extension connector logs (Search > “Connection Setup” or your custom AL connector log page) Customer List > Filter to Customer.Privacy Blocked = TRUE for unmapped attempts
| Reason | Direction | Why |
|---|---|---|
| External Document No. mapping | Material | Card uses workspace-configured commerce ID field. If the AL Extension stores commerce ID in a custom field, the field map needs the override. |
| Period boundary | Card more inclusive | Orders straddling the period appear with their commerce-side date; manual exports may be inconsistent. |
| Sales Credit Memo matching | Card matches automatically | Manual exports often miss the Credit Memo offset. |
| OData freshness | Card 15 min behind | Live BC view is real-time; card is up to 15 minutes stale. |
Known limitations / merchant FAQs
Can I export this to Excel for the weekly close? Yes, CSV export is the primary use mode. Some merchants schedule a Power Automate flow to email the export to the AR team every Monday at 7am. How does the join work between commerce order and BC SO? By default viaExternal Document No. on the BC Sales Header. The AL Extension connector typically populates this with the commerce platform’s order ID at SO creation. If your connector uses a custom field, the field map needs the override.
What happens when a commerce order generates multiple BC SOs (split shipment)?
The card aggregates BC-side rows by External Document No. and shows the sum vs the commerce gross. Sub-rows can be expanded.
Does this work with multiple commerce platforms simultaneously?
Yes. Each row carries the commerce platform code (Shopify, BigCommerce, Adobe Commerce, marketplace). A single BC SO can match a single commerce order; cross-platform consolidation is on the headline card.
How are returns matched?
A commerce return generates a Sales Credit Memo in BC. The card matches by commerce return ID → BC SCM External Document No.. Matched returns drop out of the gap; unmatched ones (return processed in commerce but no SCM in BC) appear with reason Sales Credit Memo Pending.
Privacy and data retention?
Customer names are pulled from BC. The card respects Customer.Privacy Blocked = TRUE; flagged customers show as [Privacy Blocked] in exports.
Multi-Company: same commerce order in two Companies?
Possible if a customer’s order is split across Companies (e.g. UK ships partial, US ships partial). Each Company-side row appears separately with a shared commerce order ID; the gap is computed against the prorated commerce amount.
AL Extension connector errors: do they show here?
Yes if the connector logs the error against an attempted SO creation. Reason AL Connector Error carries the error message.