Some merchants want to customize order numbers or invoice numbers to be different than what Magento 2 produces by default. They might want the numbers to contain more information, or they might have an existing format that shouldn’t be changed. Perhaps the numbers need to pick up where they left off from a previous website (or maybe they just don’t look pretty enough). These numbers each have an Increment ID, and the values used to create them are stored in the database and are not configurable from admin, so we’ll need a little SQL to make our customizations. Without further ado…
We can individually adjust the following properties of increment IDs for Orders, Invoices, Credit memos, & Shipments:
- Prefix
- Suffix
- Step
- Start-value
- Pad-length
The Prefix, Suffix, Start-value, and Step are stored in the database, while the Pad-length is set in the code. Before changing them, let’s see how they’re all used to generate increments IDs:
Formula
In Magento 2, the method for determining the increment ID is in MagentoSalesSequenceModelSequence.
The pattern is set as: a string, plus a 9-digit number (padded with 0s), plus another string.
The getCurrentValue() method returns a new increment ID according to the pattern as: The Prefix, plus the return value of the calculateCurrentValue() method, plus the Suffix. The calculateCurrentValue() returns the main number:
return ($this->lastIncrementId - $this->meta->getActiveProfile()->getStartValue()) * $this->meta->getActiveProfile()->getStep() + $this->meta->getActiveProfile()->getStartValue();
For the sake of demonstration, we’ll work with the increment ID for orders, so $this->lastIncrementId is the last value in the sequence_value column of the sequence_order_1 table in the database. (If we were working with the invoice increment ID, $this->lastIncrementId would come from the sequence_invoice_1 table.) The 1 in the table name is the store ID for the first store view. If you have another store view, you would use the sequence_order_n table (where n is your store ID).
So, calculateCurrentValue() subtracts start_value from the last sequence_value, multiplies by step, and then adds start_value. The result is added between the Prefix and Suffix.
We can express the whole method as a mathematical formula:
increment_id = prefix + ((sequence_value – start_value) * step + start_value) {padded to X digits} + suffix
sequence_value starts at 1 and always increases by 1 when a new order is created [or invoice, etc].
Initially, start_value, and step are each 1, and prefix and suffix are undefined.
If we plug these values into our formula, we can predict the initial order increment ID:
increment_id = '' + ((1 – 1) * 1 + 1) {padded to 9 digits} + ''
increment ID = ((1 – 1) * 1 + 1) {padded to 9 digits}
increment ID = 1 {padded to 9 digits}
increment ID = 000000001
(This is consistent with the first order increment ID shown in sales_order.increment_id in the database: 000000001.)
Prefix/Suffix
The Prefix and Suffix are simple. They prepend and append the increment-ID number with the values stored in sales_sequence_profile.prefix & sales_sequence_profile.suffix, respectively. Changing the Prefix can be an easy way to lengthen the increment ID or make it start with something besides 0. The Suffix could be used to add 0s, to make the number appear to increase by 10 or 100 each time (as an example). Alternatively, we could use these values to stylize the increment ID or make it proprietary, so in this case, let’s just add a Prefix of “CL-” and use “-M2” for our Suffix.
(When we create the next order, sequence_order_1.sequence_value increases from 1 to 2, but start_value and step are still each 1.)
Plugging it into our formula:
increment_id = prefix + ((sequence_value – start_value) * step + start_value) {padded to X digits} + suffix
increment ID = ‘CL-‘ + ((2 – 1) * 1 + 1) {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-‘ + 2 {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-000000002-M2’
(When we create this order, the last row of sales_order.increment_id should be consistent with our calculation.)
Step
The Step is stored in sales_sequence_profile.step. It is 1 by default and should not be less than 1.
We can use it to increase our order increment-ID number by a certain amount each time a new order is created (or “step” it up). For example, because step is 1 by default, our last increment-ID number “stepped” up by 1 from 000000001 to 000000002. However, when we change the step, the increment ID will “shuffle” one time before it follows the new pattern.
To demonstrate, let’s set the step to 100. (When we create the next order, sequence_order_1.sequence_value increases from 2 to 3, but start_value is still 1.)
Plugging it in:
increment_id = prefix + ((sequence_value – start_value) * step + start_value) {padded to X digits} + suffix
increment ID = ‘CL-‘ + ((3 – 1) * 100 + 1) {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-‘ + 201 {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-000000201-M2’
As you can see, the increment-ID number did not “step” up by 100 from the previous value (000000002). This is because it shuffles on the first change. However, the next increment ID should follow the new pattern. (The next order increases sequence_value from 3 to 4.)
increment ID = ‘CL-‘ + ((4 – 1) * 100 + 1) {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-‘ + 301 {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-000000301-M2’
This time, the increment-ID number “stepped” up by 100 from the previous value, as we hoped (000000201 to 000000301).
Start-value
The Start-value is stored in sales_sequence_profile.start_value. It is 1 by default and cannot be less than 0. (Also, it should not be both greater than the last sequence_value and less than step, because that would make the increment-ID number negative.)
The Start-value is somewhat unintuitively named, as it reduces the amount by which step is multiplied, and then is added back to the increment-ID number. For example, because start_value is 1 by default, our last two increment-ID numbers (000000201 and 000000301) effectively have a 1 added to a multiple of the step value (which is 100).
When we change the Start-value, the increment ID will “shift” one time before it follows the pattern again. To demonstrate, we’ll use a Start-value of 3. (When we create the next order, sequence_order_1.sequence_value increases from 4 to 5, and step is still 100.)
When we plug it in:
increment_id = prefix + ((sequence_value – start_value) * step + start_value) {padded to X digits} + suffix
increment ID = ‘CL-‘ + ((5 – 3) * 100 + 3) {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-‘ + 203 {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-000000203-M2’
As you can see, the amount by which step was being multiplied got reduced, and the amount added to the end of the increment-ID number increased (instead of “stepping” from 000000301 to 000000401, it “shifts” to 000000203).
The next increment ID should “step” up as usual. (The next order increases sequence_value from 5 to 6.)
increment ID = ‘CL-‘ + ((6 – 3) * 100 + 3) {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-‘ + 303 {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-000000303-M2’
This time, the increment-ID number “stepped” up as expected, and the Start-value of 3 was added to the end.
(If this change seems like an unusual way to set your increment IDs, don’t worry about it. The intention was just to demonstrate how start_value affects the pattern.)
Minimum increment-ID number
If we want to set a “starting” number to make it seem like the increment ID was originally higher than 1 and counted up from there (like numbering your first bank check 1000), we won’t actually use the start_value property. (However, because it has that name, it seemed helpful to address the matter under this section.)
To set a minimum number for our increment IDs, we need to add a value to sequence_order_1.sequence_value.
To demonstrate, let’s add a sequence_value of 1006 and put step and start_value back to 1. (When we create the next order, sequence_order_1.sequence_value increases from 1006 to 1007, and step and start_value are now 1 again.)
According to the formula:
increment_id = prefix + ((sequence_value – start_value) * step + start_value) {padded to X digits} + suffix
increment ID = ‘CL-‘ + ((1007 – 1) * 1 + 1) {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-‘ + 1007 {padded to 9 digits} + ‘-M2’
increment ID = ‘CL-000001007-M2’
Now it’s as if the first order increment ID was 1000 and we “stepped” up from there.
Note: The sequence_order tables have an AUTO_INCREMENT value, so the above method only works if the inserted sequence_value is higher than the previous. If you’ve tried to shorten the order ID this way unsuccessfully, you should check the AUTO_INCREMENT value for the related sequence table:
SHOW CREATE TABLE sequence_order_1;
If the AUTO_INCREMENT value in the returned query is higher than the sequence_value number you’re wanting to change to, you’ll need to run a query like this:
ALTER TABLE sequence_order_1 AUTO_INCREMENT = 1006;
Pad-length
The pad length of the increment-ID number is determined in the code base, and it’s not affected by any of the database properties.
In MagentoSalesSequenceModelSequence, the getCurrentValue() method uses a formatted-string-print function to set the pattern of the increment-ID number:
return sprintf(
$this->pattern,
$this->meta->getActiveProfile()->getPrefix(),
$this->calculateCurrentValue(),
$this->meta->getActiveProfile()->getSuffix()
);
“$this->pattern” comes from the constant DEFAULT_PATTERN, which is initially: %s%'.09d%s.
The increment-ID number is determined by the middle section: %'.09d. The '.0 sets “0” as the padding character and sets the number of digits to display as the value that follows, which in this case is 9. The d presents the number as a [signed] decimal. This means that by default, the increment-ID number will be a signed decimal with 9 digits, padded with 0s. To demonstrate, we’ll set the pad-length to 6. (When we create the next order, sequence_order_1.sequence_value increases from 1007 to 1008, while start_value and step are still 1.)
Using our formula:
increment_id = prefix + ((sequence_value – start_value) * step + start_value) {padded to X digits} + suffix
increment ID = ‘CL-‘ + ((1008 – 1) * 1 + 1) {padded to 6 digits} + ‘-M2’
increment ID = ‘CL-‘ + 1008 {padded to 6 digits} + ‘-M2’
increment ID = ‘CL-001008-M2’
As you can see, the increment-ID number is only 6 digits long now, instead of 9.
Make it happen
In the database, the sales_sequence_profile table sets the pattern for the increment ID on each entity type (order, invoice, creditmemo, and shipment) at each store view. We need to make our changes for store view 1, which is set on rows 5–8. (These rows set the 4 entity types respectively.) For the sake of demonstration, we’ll work with the order increment ID, so we’ll be changing row 5 of sales_sequence_profile (meta_id = 5).
Here’s what sales_sequence_profile looks like by default:
The following are the SQL queries (plus the line of code) to set each property to the value used in our examples. (Because we worked with the increment ID for Orders, the meta_id in each query below is set to 5, but you could also use 6, 7, or 8, to change the increment IDs for Invoices, Creditmemos, and Shipments, respectively.)
Prefix:
UPDATE `sales_sequence_profile` SET `prefix` = 'CL-' WHERE `meta_id` = 5;
Suffix:
UPDATE `sales_sequence_profile` SET `suffix` = '-M2' WHERE `meta_id` = 5;
Step:
UPDATE `sales_sequence_profile` SET `step` = 100 WHERE `meta_id` = 5;
Start-value:
UPDATE `sales_sequence_profile` SET `start_value` = 3 WHERE `meta_id` = 5;
Minimum increment-ID number:
INSERT INTO `sequence_order_1` (`sequence_value`) VALUES ('1000');
Pad-length:
The constant DEFAULT_PATTERN is set in: /vendor/magento/module-sales-sequence/Model/Sequence.php, on line 19.
We can change this in a custom module by creating etc/di.xml with the following contents:
%s%'.06d%s
Conclusion
You can now have full control over your increment IDs. Although they sometimes seem to change unexpectedly when you adjust certain values, the formula should help you predict the pattern:
increment_id = prefix + ((sequence_value – start_value) * step + start_value) {padded to X digits} + suffix
Happy incrementing!