How to Change Magento 2 Increment ID for Orders, Invoices, Credit Memos and Shipments
How to Change Magento 2 Increment ID for Orders, Invoices, Credit Memos and Shipments

How to Change Magento 2 Increment ID for Orders, Invoices, Credit Memos and Shipments

Published February 7, 2017 in Development
Google announces Mobile-First Indexing
Google Announces Mobile-First Indexing
December 8, 2016
Magento 2: Dynamic Store Configuration Fields
Dynamic Store Configuration Fields
April 3, 2017

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 Magento\SalesSequence\Model\Sequence.
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:

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_valuestart_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 = '' + ((11) * 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_valuestart_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_valuestart_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_valuestart_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_valuestart_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:

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:

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 \Magento\SalesSequence\Model\Sequence, the getCurrentValue() method uses a formatted-string-print function to set the pattern of the increment-ID number:

“$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_valuestart_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:

sales_sequence_profile: Default state

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:
Suffix:
Step:
Start-value:
Minimum increment-ID number:
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:

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_valuestart_value) * step + start_value) {padded to X digits} + suffix

Happy incrementing!

27 Comments

  1. peinture says:

    This is a very helpful and complete description of ways to modify the increment ID. Thank you for that!

    I have just one remark. The file that has to be modified (Sequence.php) can be found in magento 2 in this folder: .. /vendor/magento/module-sales-sequence/Model/Sequence.php

    • Brendan Tull says:

      @peinture – Thanks for the comment. You may notice that the file path you mentioned is already listed near the end of the post, in the “Make it Happen” section (“Pad-Length” sub-section). Would it have been more helpful if I’d listed the file path up in the main “Pad-Length” section?

  2. Thomas says:

    Thank you! very detailed and useful!

  3. Michelangelo Turillo says:

    Hello,

    can the suffix be the year of the creation of the order?

    thanks

    • Brendan Tull says:

      The suffix is a fixed string from the DB that’s added to the increment ID in Magento\SalesSequence\Model\Sequence::getCurrentValue(), as the 4th parameter passed to sprintf(). If you create a preference to redefine getCurrentValue(), you could set the date as a variable and pass it to sprintf() instead of the suffix.

  4. Felix says:

    About the padding: The clean solution seems to be a di.xml injection, as described here: https://github.com/magento/magento2/issues/5541#issuecomment-249501943. I don’t know yet what to do with it, but it sounds like it makes sense, to me. Happy if you update your article if that is indeed correct.

    • Felix says:

      Confirm that e.g.

      %s%’.8d%s

      in app/etc/di.xml will then pad the numbers to 8 digits.

      • Felix says:

        oups. above comment had xml markup in it but the comment function ate it all for breakfast. So see an example of the conf-snippet in the link in my first comment

    • Brendan Tull says:

      @Felix – Thanks for the suggestion. It looks like the XML solution worked, so I updated the article accordingly.

  5. Chris says:

    This is a really dumb question but my Magento 2.2.3 has order numbers like 2000023232
    How do I get rid of the leading 20000 ?

    • Brendan Tull says:

      @Chris – I can’t say for sure without seeing how the increment ID is configured in your database, but with the length of the number you gave, it seems likely that there’s a prefix of “2”. (See the the “Prefix/Suffix” section for an explanation of how it works.) In the “Make It Happen” section, under the “Prefix” sub-header, there’s a sample SQL query for changing the prefix. To remove it, just set it to null in the database. For example: UPDATE `sales_sequence_profile` SET `prefix` = NULL WHERE `meta_id` = 5; The meta_id may vary, so reference the sales_sequence_meta table and get the meta_id from whichever row has sequence_order_N, where N is the store view you’re configuring (or 0 for the default scope).
      To get rid of those zeros, you’ll need to change the pad-length. (See the the “Pad-length” section for an explanation of how it works.) If you don’t want any zeros, you could simply remove the padding character entirely using a custom module in the code base. In the “Make It Happen” section, under the “Pad-length” sub-header, there’s a sample di.xml file for changing the padding. To remove the padding, we should be able to pass the following value in the “pattern” argument: %s%d%s (on line 5 of the sample file). This should set the main part of the number as simply a [signed] decimal, without any padding.
      I hope this helps!

    • Uchiha D.P(.)rm(.)r says:

      @Chris: I guess you must be using 2 stores in the your project, For store one it will be coming as “10000” and for store two it will be coming as “20000”.
      🙂

  6. Samuel says:

    This is a fantastic article; your readers will really be able to take bits and pieces from your post to set up their store for their needs.
    I figured I would post my setup, both in order to get a confirmation that our set up makes sense, and also in case anyone is looking to do something similar.

    We have 2 store views: English (1) and French (2).
    We wanted every order/invoice/shipment/credit to increment sequentially regardless of store view. In order to accomplish this, in ‘sales_sequence_meta’, we set every record to ‘sequence_XXX_0’, 0 being the default.

    We set the same prefix and suffix for all storeviews in ‘sales_sequence_profile’, and everything seems to be working as expected.

    • Brendan Tull says:

      @Samuel – Thanks for the feedback! I’m glad the article was helpful.
      Based on what you described in `sales_sequence_meta`, it sounds like you’re essentially setting each increment ID to use the respective sequence table for the default store view. (e.g. `entity_type` of ‘order’ for `store_id` 1 and 2 both use the `sequence_order_0` table.) I think this does make sense in order to make everything increment sequentially across all store views.

      • Samuel says:

        Thanks Brendan,
        another question: I notice that there is the field ‘increment_last_id’ in table ‘eav_entity_store’.

        Does this need to be manually modified when updating the other tables while setting up increment ID per your article?

        • Brendan Tull says:

          I’m not exactly sure what that table is for, but after comparing the values to the sequence table and searching the code for usage, it appears to be related to data migration. It seems to be noting the “Last Incremented Id” from a corresponding Magento 1 site and setting it as the first respective `sequence_value` for a new Magento 2 site. I don’t think it’s directly relevant in this case, as I believe the sequence tables (e.g. ` sequence_order_0`) are authoritative when generating new increment IDs.

          • Samuel says:

            That makes sense, as we did indeed do an M1->M2 migration.

            One last question: does it not work to simply set the pad length manually in table ‘eav_entity_type’, field ‘increment_pad_length’ ?

          • Brendan Tull says:

            No, I’m pretty sure that’s for data migration too. As explained in the “Pad-length” section, the increment ID is actually calculated in \Magento\SalesSequence\Model\Sequence::getCurrentValue() with a formatted-string function that uses a pattern set in the constant DEFAULT_PATTERN. This is where the padding is specified. It can be changed as outlined in the “Make it happen” section, under the “Pad-length” sub-heading.

      • Samuel says:

        Hi Brendan,
        I am back to share some more insight on setting sequential increments regardless of store ID.

        In ‘sales_sequence_meta’, I set the sequence_table for each entity type to the default store one (ie. “_0”). So to clarify, we have the default store view (ID=0) plus 2 languages (ID 1 and 2). Every entity of type “order” (for store ID 1, 2 & 3) has the sequence table ‘sequence_order_0’ associated to it.

        This has been working well and the order numbers are incrementing sequentially across all store views.

        However, when performing
        bin/magento setup:upgrade

        We get the error “Unique contraint violation found”.

        Turns out there is a check to make sure there are no duplicates in the field ‘sequence_table’.

        The workaround is to go back to the default setup to run setup:upgrade.

        What do you think would be the best workaround in order to not have to manually edit the ‘sales_sequence_meta’ table every time we need to setup:upgrade?

        • Brendan Tull says:

          @Samuel – I’m honestly not sure at this point. Perhaps you could create an UpgradeSchema script to remove the unique constraint from the table, but it may be there for some other reason. Maybe instead it would be best to create a custom module to update the sequence tables of a certain type across all store views, so for example, if you place an order in store view 1, sequence_order_0 and sequence_order_2 would get updated at the same time as sequence_order_1, and I would think that would keep the increment IDs in sync, without having to violate the unique constraint in sales_sequence_meta. (just a couple ideas)

  7. Pitabas says:

    Hello Brendan,

    Thank your for the Awesome post.

    I am facing one issue, It will really great, if you could help me.

    Here is my issue:

    I have override the “pattern” inside di.xml (%s%’.05d%s), but order number is generating one extra number(1) e.g: Your order number is: 165898.

    It should be “65898”. Why the extra “1” is generating before the order number.

    So first I thought it was a prefix, then I check my DB >> sales_sequence_profile table, If found both prefix and suffix fields are showing NULL.

    Can you please help on this?

    • Brendan Tull says:

      I think that by default, only the prefixes for the default config scope are NULL, while prefixes for store-view 1 are “1”, and prefixes for store-view 2 are “2”, etc. You might check to ensure that ALL the prefix fields in sales_sequence_profile are NULL. If they are, then I don’t know what else would be causing this.

  8. Cesar Palacio says:

    Hello Brendan, thanks for your excellent article. I could create an ID with a combination of the year, month, day, hour, minute, and second. (Sorry for my English – Google Translator)

  9. 1st Main says:

    How would I go about getting my order number in this format: YYYY-MM-XXXXX
    (Y = Year, M = Month, XXXXX = 5 digit order number that resets every month)

  10. Brendan Tull says:

    @Cesar Palacio, @1st Main – To modify the format of the increment ID, you’ll need to plug in to Magento\SalesSequence\Model\Sequence (as described in the “Formula” section above), probably starting with the getNextValue() method, which has access to the sequence_value in the database.

  11. Alejandro Marroni says:

    Brendan excellent articule (BTW Magento support team used for explain some situation with incrementId). Let me share a issue (from Client point of view) we are expecting.
    We are running Magent Commerce Cloud and the architecture cloud has create for each order created a sequence of 3 jumping to 3. XXXXX3 to XXXXX6.
    Here is the official explication https://mariadb.org/auto-increments-in-galera/
    For the project this is cousing huge problem bc that number is used internally for the rest of the extra systems (ERP and warehouse) we are lousing 2 numbers for each order.
    Support team tell us we need to create a custom code to handle this situation.

    Here is the quesiton, with your experience should be possible to get back or get correctly jumpint 1 to 1 working with this configuration?

    Best,
    Alejandro.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Recent PostsView all
October 15, 2019

Getting Started with Magento Page Builder

Not Your Grandfather’s WYSIWYG Editor For those of you coming to this article with zero experience in Page Builder in Magento 2, here is a brief […]
October 3, 2019

Avoid Abandoned Revenue with Abandoned Cart Emails

If a shopper stood in line at a retail store, ready to make a purchase, and walked away once they reached the cashier—leaving the merchandise on […]
September 17, 2019

Five Essential Components of Successful eCommerce Search

Having an effective on-site search strategy is more important than ever for today’s online retailers. According to a blog by Econsultancy, 30% of users on eCommerce […]