ERP Integration Best Practices That Keep the Books Right
Nine ERP integration best practices with templates: master data profiling, duplicate-proof postings, posting vs document dates, period locks and reconciliation.
Luka Abramovic16 min read
ERP integration best practices come down to three promises: no posting lands twice, every posting lands on the right date in an open period, and a daily check proves both systems agree. The first promise is the easiest to break, because storing the source system's invoice number in the ERP doesn't stop a duplicate by itself. Microsoft's documentation says Business Central doesn't check whether external document numbers are unique. An integration that retries a timed-out invoice will post it again, with the same number, unless it searches for that number first.
The nine practices below use Microsoft Dynamics 365 Business Central as the worked example, because its documentation is public and specific, and note NetSuite where we could confirm the behavior. Details are as of September 2026. Deciding which system owns each record, and keeping sync one-way where you can, comes first and is covered in our guide to connecting business systems. This guide starts after those decisions.
1. Profile the master data before you scope the build
Every invoice, order and payment the integration moves refers to a customer, an item or an account. If the two systems disagree about those, each transaction either fails or quietly lands on the wrong record. The size of that disagreement is the real scope of the project, and you can measure it before anyone quotes.
Export customers from both systems with their IDs and names, plus the CRM's ID if the ERP already stores it. Someone comfortable with SQL loads them as two tables and runs the queries below. They are written for PostgreSQL. The first part defines a matching key that ignores case, punctuation and legal suffixes, so "Acme, Inc." and "ACME INC" match.
CREATE FUNCTION norm_name(t text) RETURNS text LANGUAGE sql IMMUTABLE AS $$
SELECT trim(regexp_replace(regexp_replace(regexp_replace(
upper(replace(replace(t, '''', ''), '&', ' AND ')),
'[^A-Z0-9]+', ' ', 'g'),
'\m(INC|INCORPORATED|LLC|LTD|LIMITED|CORP|CORPORATION|CO|COMPANY|THE)\M', ' ', 'g'),
' +', ' ', 'g'))
$$;
-- 1. In the CRM, with no match in the ERP by stored ID or by name
SELECT count(*) AS crm_only FROM crm_customers c
WHERE NOT EXISTS (SELECT 1 FROM erp_customers e
WHERE e.crm_id = c.id OR norm_name(e.name) = norm_name(c.name));
-- 2. In the ERP, with no match in the CRM
SELECT count(*) AS erp_only FROM erp_customers e
WHERE NOT EXISTS (SELECT 1 FROM crm_customers c
WHERE e.crm_id = c.id OR norm_name(e.name) = norm_name(c.name));
-- 3. Probable duplicates inside one system (run it for each system)
SELECT norm_name(name) AS match_key, count(*) AS records, string_agg(id, ', ') AS ids
FROM erp_customers GROUP BY 1 HAVING count(*) > 1;
-- 4. Same name in both systems but no stored link: pairs a person must confirm
SELECT c.id AS crm_id, c.name AS crm_name, e.id AS erp_id, e.name AS erp_name
FROM crm_customers c JOIN erp_customers e ON norm_name(e.name) = norm_name(c.name)
WHERE e.crm_id IS NULL;
-- 5. Linked records whose names have drifted apart
SELECT c.id, c.name AS crm_name, e.name AS erp_name
FROM crm_customers c JOIN erp_customers e ON e.crm_id = c.id
WHERE norm_name(c.name) <> norm_name(e.name);No database? The same key works in Excel or Google Sheets. Put names in column B, this in C2, fill down, and do the same on the other system's sheet. Then =COUNTIF(ERP!C:C, C2) returns 0 for customers the ERP doesn't have and more than 1 for ambiguous matches.
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(B2),"."," "),","," "),"'",""),"&"," AND "))&" ",
" INC "," ")," LLC "," ")," LTD "," ")," CORP "," ")," CO "," ")," THE "," "))| Count | What it means for scope |
|---|---|
| 1. CRM only | Customers the ERP will be asked to invoice but doesn't know. Each needs creating with tax status and payment terms that only finance can supply. |
| 2. ERP only | Usually old, cash or one-off accounts. Decide whether they belong in the CRM before the first sync decides for you. |
| 3. Duplicates in one system | Merge these before go-live. Afterwards, each one is a posting that can land on the wrong customer. |
| 4. Name matches with no link | Your matching workload. Time someone confirming ten pairs, multiply by the count, and put that number in the plan. |
| 5. Linked, names differ | Drift. Decide whose name wins and record it in the ownership table. |
Run the same five counts for items and for any account codes the integration maps. A plan that assumes clean master data is a plan without its largest task.
2. Make every posting impossible to duplicate
The classic duplicate invoice happens when the ERP posts the invoice but the reply never reaches the integration, because of a network drop or a client that stopped waiting. The integration records a failure, retries, and the customer is billed twice. The full sequence is in our guide to connecting business systems. In an ERP it costs more than elsewhere, because a posted invoice has already created ledger entries. Undoing it takes a cancellation or a corrective credit memo, and both documents stay in the books.
Business Central's API gives you two places to lose a reply. Creating a sales invoice makes a draft, and a separate Microsoft.NAV.post action posts it. The invoice's status field tells you which state it reached: Draft before posting, and Open, Paid, Canceled or Corrective after (salesInvoice API reference). Business Central also lets you require an external document number on every sales invoice, with the Ext. Doc. No. Mandatory setting, but requiring one doesn't make it unique, and the product doesn't check (Microsoft Learn). So the integration has to search before it creates, and again after any timeout:
GET {businesscentralPrefix}/companies({companyId})/salesInvoices?$filter=externalDocumentNumber eq 'SHOP-10442'
No result create the draft with externalDocumentNumber = 'SHOP-10442', then post it
One, status Draft post it
One, Open, Paid, Canceled or Corrective
already posted: record its number and stop
Anything else stop, and send it to a personA search alone still leaves a race. Two copies of the integration, or a retry that overlaps the original, can both search, both find nothing and both create. Close that gap in the integration's own database. Before calling the ERP, a worker claims the source reference, and the primary key makes the second claim fail:
CREATE TABLE posting_ledger (
source_ref text PRIMARY KEY, -- the source system's invoice number, never reused
erp_invoice_id text, -- filled in when the ERP confirms the draft
state text NOT NULL, -- claimed, drafted, posted, needs_review
attempts int NOT NULL DEFAULT 0,
updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO posting_ledger (source_ref, state) VALUES ('SHOP-10442', 'claimed')
ON CONFLICT (source_ref) DO NOTHING; -- "0 rows inserted" means another worker has itWhatever your ERP, ask your implementation partner two questions and get the answers in writing: which field carries the source reference, and does the ERP itself reject a second document with the same value? If the answer to the second is no, the integration is the only protection you have.
3. Carry the posting date and the document date separately
An invoice has two dates that usually match and sometimes mustn't. The document date is the date printed on the invoice. The posting date decides which accounting period it lands in. In Business Central, payment due dates are calculated from the document date, not the posting date (Microsoft Learn), and the sales invoice API has separate invoiceDate and postingDate fields. NetSuite keeps the same split, with a transaction date and a separate posting period on each transaction.
Integrations that collapse the two into one value break in one of two ways. Set both to the day the integration ran, and a batch that runs after midnight moves June 30 invoices into July, and every customer's due date moves with them. Set both to the original date, and anything that arrives after June is locked fails outright. Set them separately, and the document date can stay true while the posting date moves.
| Source value | ERP field | Rule |
|---|---|---|
| Invoice date on the source document | Document date | Always the source date, even when posting is late. |
| Accounting date, if the source has one, otherwise the invoice date | Posting date | The source date if its period is open. Otherwise, practice 4 applies. |
| Source timestamp | None. Keep it in the integration's log | Convert to the company's time zone before taking the date. An order at 9 p.m. Pacific on June 30 is already July 1 in UTC. |
4. Treat a locked period as a decision for a person
Business Central has a detail that surprises people who assume "closed period" means "can't post". Accounting periods don't control posting dates. Even a closed fiscal year still accepts entries, which are flagged as prior-year entries (Microsoft Learn). What actually blocks a posting is the Allow Posting From and Allow Posting To range on the General Ledger Setup page. A row for a specific user on the User Setup page overrides that range for that user (Microsoft Learn).
That override is where integrations go wrong. The integration posts as a user. If someone once gave that user a wide User Setup range to fix a failed sync, it can still post into a month finance believes is locked. If its range is narrower than the company's, it fails on dates people can post to by hand. Ask your controller to show you the integration user's row, or confirm it has none. The ranges can also be date formulas, such as -CM to CM for the current month only, which Business Central evaluates against the work date rather than the calendar.
When a posting date falls outside the range, Business Central refuses it with "Posting Date is not within your range of allowed posting dates". The integration should neither change the date by itself nor fail silently. Our default is to hold the transaction with its original document and posting dates intact, and send it to a named person in finance with the amount, the customer and the allowed range at that moment. They choose: post on the first open day with the document date unchanged, open the period briefly, or hold it. If finance agrees a standing rule, such as "post late items on the first open day", the integration can apply it, as long as it keeps the document date and logs every move.
NetSuite and Sage Intacct lock periods in their own ways. Ask the same two questions of any ERP: which date does the lock check, and does the integration's user have an exception?
5. Let the ERP calculate tax, and reconcile tax separately
In Business Central's sales invoice line API, the tax amounts are read-only. You send the tax code, and Business Central calculates the tax from its own setup (salesInvoiceLine API reference). US sales tax there is built from jurisdictions such as state, county and city, and tax can even be charged on an amount that already includes another jurisdiction's tax (Microsoft Learn). So the source system and the ERP each calculate tax, and they don't have to round the same way.
An illustrative example. A web store sells three items at $3.33 each with 8.25% tax and rounds tax on each line. The ERP rounds once on the invoice total.
| Method | Calculation | Tax |
|---|---|---|
| Round each line | $3.33 × 8.25% = $0.274725, rounds to $0.27, times 3 | $0.81 |
| Round the total | $9.99 × 8.25% = $0.824175, rounds to $0.82 | $0.82 |
One cent, and neither system is wrong. Four hundred such orders in a day put the daily totals $4.00 apart, all in the same direction, because identical prices round the same way every time. A reconciliation that expects the totals to match exactly raises a false alarm every day, and people learn to ignore it.
Each rounding step can move the result by at most half a cent. An invoice with n taxed lines has n line roundings on one side and one total rounding on the other, so the two can differ by at most (n + 1) × $0.005. That is the per-invoice tolerance, derived rather than guessed. If your ERP rounds per jurisdiction as well, count one rounding per jurisdiction per line. Then reconcile net amounts exactly and tax within that tolerance. A missing line shows up in the net, where no tolerance can hide it.
6. Agree which date's exchange rate applies
When Business Central posts a foreign-currency transaction, it converts it to your local currency at the exchange rate for the posting date (Microsoft Learn). Combine that with practice 4, and a late invoice that moves to the next open day also moves to a different rate.
An illustrative example. A €10,000 invoice dated June 30 is posted on July 1 because June is locked. At illustrative rates of 1.0850 on June 30 and 1.0790 on July 1, the source system reports $10,850 and the ERP books $10,790. The $60 is a rate difference, not an error, but a reconciliation in dollars will flag it.
Decide three things with finance. Which date's rate applies: document date, posting date, or the rate your payment processor actually settled at. Where the rates come from, so both systems load the same table. And how foreign-currency invoices are reconciled: our default is in the invoice currency, with dollar differences reported separately as rate differences.
7. Send the unit of measure on every line
In Business Central, each item has a base unit of measure, alternate units defined by how many base units they contain, and optional default units for sales and purchasing (Microsoft Learn). The sales invoice line API has its own unitOfMeasureCode field. If the integration leaves it empty, the line takes the item's default. A web store that sells "each" sends a quantity of 12, the item's default sales unit is a case of 12, and the invoice bills 12 cases, which is 144 units.
Conversions also create rounding. Microsoft's own example: receive five pieces from a box of six, recorded as a fraction of a box, and inventory shows 4.99998 pieces. The Quantity Rounding Precision setting on the item's units of measure fixes that, but only if someone sets it.
Before the build, export every item the integration will sell or buy, with its base unit, its default sales and purchase units, and the unit the source system uses. Every row where they differ needs an explicit conversion in the integration, and at least one of those items belongs in the test list.
8. Reconcile with control totals every day
An integration that reports success hasn't proved that the ledgers agree. Control totals do: for each day, the number of documents and the sum of their net and tax amounts, counted independently on both sides. The trick is to group both sides by the same date. Because practice 3 carries the source's document date into the ERP, both systems can group by document date, and a posting moved to July still lands in the June 30 row.
Export yesterday's invoices from the source and posted invoices from the ERP. In Business Central, the Share icon on the posted sales invoices list includes Open in Excel (Microsoft Learn). Load both into two tables, and a finance analyst or developer runs:
-- Daily control totals, grouped by the document date both sides carry
SELECT coalesce(s.document_date, e.document_date) AS document_date,
s.invoices AS source_count, e.invoices AS erp_count,
s.net - e.net AS net_difference, s.tax - e.tax AS tax_difference,
s.tax_tolerance
FROM (SELECT document_date, count(*) AS invoices, sum(net_amount) AS net,
sum(tax_amount) AS tax, sum((line_count + 1) * 0.005) AS tax_tolerance
FROM source_invoices GROUP BY document_date) s
FULL JOIN (SELECT document_date, count(*) AS invoices, sum(net_amount) AS net,
sum(tax_amount) AS tax
FROM erp_invoices GROUP BY document_date) e
ON e.document_date = s.document_date
ORDER BY 1;
-- Invoice by invoice: only the last five statuses need a person
SELECT coalesce(s.source_ref, e.external_document_no) AS reference,
CASE
WHEN e.external_document_no IS NULL
AND s.sent_at > now() - interval '26 hours' THEN 'in flight'
WHEN e.external_document_no IS NULL THEN 'missing in ERP'
WHEN s.source_ref IS NULL THEN 'in ERP only'
WHEN count(*) OVER (PARTITION BY e.external_document_no) > 1 THEN 'duplicate in ERP'
WHEN s.net_amount <> e.net_amount THEN 'net differs'
WHEN abs(s.tax_amount - e.tax_amount) > (s.line_count + 1) * 0.005
THEN 'tax beyond rounding'
ELSE 'matched'
END AS status
FROM source_invoices s
FULL JOIN erp_invoices e ON e.external_document_no = s.source_ref
ORDER BY status, reference;A day is clean when the counts match, the net difference is zero and the tax difference is within the tolerance. The "in flight" status is what keeps the report from crying wolf: a document sent less than one integration cycle ago, plus a margin, isn't missing yet. Our default is 26 hours for a nightly batch. Set yours to your schedule, and read the daily totals only for dates older than that window.
In a spreadsheet, the same check is two COUNTIFS and two SUMIFS per date, one pair on each system's export. What matters is that someone named looks at it every morning, and that each break is explained or sent to the exception queue.
9. Test the cases that break the books
The happy path will work. Run each of these in a sandbox copy of your ERP, with finance watching, before go-live.
| Test | How to run it | Passes when |
|---|---|---|
| Lost reply on create | Block the response after the ERP creates the draft, then let the integration retry. | One invoice exists. The retry found it by external document number. |
| Lost reply on post | Block the response to the post action, then retry. | One posted invoice. No second draft. |
| Two workers at once | Send the same source invoice to two workers at the same moment. | One claim in the posting ledger and one invoice. |
| Late invoice | Send an invoice dated in a period outside the allowed posting range. | Held with both dates intact and routed to the named person. Nothing posted. |
| Integration user's posting range | Compare the integration user's User Setup row with General Ledger Setup. | Finance has approved the range, or no row exists. |
| Midnight batch | Create a source invoice at 11:55 p.m. local time and run the batch after midnight. | Document date and posting date are the source date, in the company's time zone. |
| Tax rounding | Send the three-line $3.33 invoice from practice 5. | Net matches exactly. Tax differs by no more than $0.02 and the reconciliation shows it as matched. |
| Foreign currency, late posting | Post a foreign-currency invoice on a later date than its document date. | Reconciles in the invoice currency. The dollar difference is reported as a rate difference. |
| Unit of measure | Sell an item whose default sales unit differs from the source system's unit. | Quantity in base units matches the source exactly. |
| Credit memo, refund, partial payment | Send one of each against an invoice posted by the integration. | Each applies to the right invoice, and control totals still balance. |
| Unknown or merged customer | Send an invoice for a customer that isn't in the ERP, then one for a customer merged in the CRM. Our CRM integration guide covers what merges do to IDs. | Both held for a person. Neither creates a new customer silently. |
| Bulk volume | Send a day's volume times ten, such as a backlog after an outage. | Every document is counted in the reconciliation. No duplicates, no gaps. |
If you're planning an ERP connection, bring the five master data counts and the date mapping table to a first conversation. We'd look first at how postings are made safe to retry and who decides what happens in a locked period. Our systems integration work starts from there.