migFx Support

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: 

1. load customers;
2. extract customers;
3. map customer Ids to address details; 
4. load customer addresses.

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.

• NumberSequenceCode - unique id of a number sequence
• Name - name of a number sequence. Note: usually this name does not tell much because it is generated by the system.
• Smallest - smallest number.
• Largest - largest number.
• Next - specifies which number is next. For example, after 1000 records were generated the next number will be 1001.
• Format - specifies combination of letters and symbols (both optional) and numbers (#).

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.

• ScopeType - shows if a number sequence is specific per DataArea (Legal entity).
• ScopeValue - shows which legal entity is using this code.
• DataTypeName - shows which type uses this code.
• NumberSequenceCode - shows a number sequence code.

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:

Conclusion

There are two options to assign unique IDs using number sequences:

1. Allow the system to auto-generate IDs during data import.

• Pros: The system will handle all requirements to assign unique IDs.
• Cons: If several related data entities are linked by IDs then extract and cross reference steps are required.

2. Prepare data with IDs before import.

• Pros: Several related data entities can be loaded together in one step.
• Cons: IDs should be generated using requirements outside of the system. Additional development and mapping is required.

Details of implementation of Number sequence generation in migFx will be described in the next post.