Overview
It is a very common requirement that certain types of records within a system require a unique identifier with some sort of sequential numbering which can be used as a reference point for customers, suppliers etc. but also might be used as an alternate key when integrating between systems. Typically, this might be a ticket number or invoice number for example.
In Dynamics 365
Some of the core tables in Dynamics 365 have had some basic autonumbering capability for a long time such as Case, Invoice, Orders etc. (as shown below) however these are quite limited in their configurability and there also wasn’t the ability to add these to other tables within Dynamics 365
As a result, Microsoft introduced the ability to create your own column of type “autonumber” with a configurable format which is outlined below.
- Data type – special “# Autonumber” data type stored as a string field
- Autonumber type – there are 3 options available
- String prefixed number – supports a simple combination of a pre-defined prefix with a sequential number e.g. TST-1000
- Date prefixed number – supports a simple combination of using the current date record is created (with a number of pre-set format options such as yyyy-MM-dd or MM-dd-yyyy or dd-MMM etc) along with the sequential number e.g. 2022-01-11-1000 or 01-Nov-1000
- Custom – create your own custom sequence using format identifiers e.g. {DATETIMEUTC:yyyy-dd-MM}-{SEQNUM:4}-{RANDSTRING:4} resulting in something like 2022-01-11-1000-ODST
- Prefix/Date format – depending on whether string prefix or date prefix is chosen, this field will change with the option selected but is used to set the respective prefix e.g. TST or 2022-01-11
- Minimum number of digits – what the minimum number of digits should be in your auto generated number but can exceed this length over time as the number of records on the table with the autonumber grows.
- Seed value – what the initial starting value should be for the sequential component of the autonumber. NOTE: this seed can be reset programmatically if required.
- Preview – as you define the components of the autonumber a preview will be shown based on the configuration
Some examples of custom autonumbers using a seed value of 1000 might be:
| Format | First Two Autonumber Examples |
|---|---|
| TEST-{DATETIMEUTC:yyyy-dd-MM}-{SEQNUM:6} | TEST-2022-01-11-001000;TEST-2022-01-11-001001 |
| {DATETIMEUTC:yy-dd-MM}-{SEQNUM:6}:{RANDSTRING:4} | 22-01-11-001000:JVES;22-01-11-001001:ZJFG |
| {DATETIMEUTC:yy-dd-MM}-{SEQNUM:6}:{RANDSTRING:4}:{SEQNUM:8} | 22-01-11-001000:GMCD:00001001;22-02-11-001002:PBC8:00001003 |
IMPORTANT THINGS TO NOTE REGARDING OOTB LIMITATIONS:
- You cannot automatically rollover sequential numbering at the start of a date period e.g. year or month, so that the sequencing begins at the original seed for a given month or year. This can be achieved via configuration/customisation. As an example, you cannot (out of the box) have a sequence number that resets to 1 at the start of January each year, e.g. 2022-12-31-009843, then becomes 2023-01-01-000001
- You cannot use a field on the target table or a related table as part of the autonumber definition. As an example, lets say you want to generated a case ticket number based on the case type of either Request (RQ) or Problem (PB) e.g. RQ-00001 or PB-00001 for the same autonumber field. Again, this can be achieved via configuration/customisation.