D365-FinOps and D365-Business Central: Tip when importing a fresh chart of accounts from a legacy system

There are various approaches on how the chart of accounts is created or imported during a new ERP implementation. Due to business continuity this is mostly either copied 1 to 1 or inspired from the chart of accounts present in the legacy system. Most business decided to do some re-structuring as they incorporate the concept of Financial Dimensions present in Dynamics 365 ERPs. However, the (main) accounts that survive the transformation process usually tend to keep their own account code as the data is exported from the legacy system to an Excel file, transformed into another target Excel file and imported into Dynamics 365 (Finance and Operations or Business Central).

However, here comes the catch. Usually codes in legacy systems are numeric type fields while in both Finance and Operations and Business central the account code is actually a ‘string’ field. This means that in Dynamics 365 the way accounts are sorted are with an ‘alphanumeric sort’ (or natural sort order where, for example, ‘b44’ is sorted than ‘b5’) and not an integer sort. This has big consequences as account codes are used in ranges for calculation of ‘Total’ accounts. Finance and Operations also stops you from adding two account structures to your ledger where the main account number ranges overlap so you will encounter range overlapping errors which leave you scratching your head if you are aware of the ‘alphanumeric sort’ while building the Ledger for a specific legal entity.

There are two ways to handle this situation when designing your chart of accounts, to avoid falling in this trap:

(a)    Select specific number ranges for each section of your income statement (profit and loss accounts) and balance sheet accounts. Ensure you do not have ranges starting with the same digit else they will clash with each other. For example the numbers “1000” and “10000” start with the same digit (read: alphanumeric character) so you will have clashes if you have an accounting section in the 1K range and another section in the 10K range. Solution: have all sections start with unique digits

(b)    The other option is to use padding. Ensure that all main account codes have the same number of digits in order to ‘trick’ the alphanumeric sort to behave like an integer sort so if you really need accounts “1000” and “10000” as difference sections in your chart of accounts than use “01000” and “10000” which will then make these behave as an integer sort.

I hope this small tip helps you solve some headaches in import your chart of accounts in Microsoft Dynamics 365 Finance and Operations and Microsoft Dynamics 365 Business central.

Till next time.