Number sequences in D365 FO
Dynamics 365 Finance & Operation requires number sequences to generate unique Ids for master data, documents, transactions . Each functional area has its own parameters where number sequences are set up for unique keys.
Below is example of number sequences specified on Account Receivable parameters.
The system uses number sequence setup to assign new alpha-numeric IDs to different types of records. For example, Customers account ID can be “C000123, C000124, C000125”, invoices “INV-000234, INV -000235”.
Why we should follow number sequence requirements?
During configuration stage business users decide how they want their IDs to be generated. Having consistent and logically structured IDs on master data and transactions helps business users to understand better information. Some organizations have specific formats for IDs in each legal entity.
So when it comes to data migration we need to make sure that those requirements are met.
Especially making sure that master data has IDs in correct formats is important. These IDs will be used across the system in related information and transactions. As soon as any references are created it is not possible to change IDs on master records.
System generated IDs
When importing master data it is possible to allow Data Management to generate IDs. D365FO Data entities have build in logic which can generate Ids using number sequence parameters.
Below is example how to instruct the system to auto-generate Customer Account number (CustomerAccount) during import of data.
How to load related data?
What if we need to load related information with reference to the master data?
For example, a customer has multiple addresses. Addresses details should be imported using Customer Id as a reference. To get generated Customer IDs we should extract full list of customers from the system and perform cross reference to link addresses.
So for importing customers with multiple addresses there are 4 steps:
Question: Is it possible to reduce number of steps to 2: load customers, load addresses?
Answer: Yes, if Customer Ids are assigned during data preparation (not during upload).
How to generate correct IDs outside of the system?
Small number of records can be prepared in Excel with a simple formulas for making Ids.
But when we have a complex data model with a big number of master data objects and related information the automated solution is required.
Generating IDs can be done in Target Map in migFx. But before implementing the automated solution we need to understand how D365FO stores setup of number sequences.
How to know which format should be used?
D365FO stores number sequences and references for data types in several tables.
Below is example of Number sequence codes for Customer Account ID for 3 legal entities.
To know which ID fields use which number sequence codes we need to get Number sequence references.
For example, Customer Account number is CustAccount data type. Below is example of Number sequence references for CustAccount in each legal entity.
So by matching data type to a number sequence format per legal entity we should get following Customer account numbers for the first 3 records:
There are two options to assign unique IDs using number sequences:
1. Allow the system to auto-generate IDs during data import.
2. Prepare data with IDs before import.
Details of implementation of Number sequence generation in migFx will be described in the next post.