23

Magento 2. After fresh install the new Order id will be 100000001, 100000002 and so on.

What is the correct way to change that increment id so it would be a higher number? For example 155555552, 155555553 and so on. When changed it will look much better.

In Magento 1.9 To edit default Id for Orders, Invoice or Shipment we simply had to change values in database in "eav_entity_store".

Magento 2 looks so different...

I know there are some plugins that offer to do this, but I want to know how to do it manually.

11 Answers 11

26
  1. Open your PHP Admin database (I tested on php 7)
  2. Find & click on the table called: sequence_order_1 (it is located in database itself not in the information_schema )
  3. Click on "Operations" tab & under "AUTO_INCREMENT" edit to desired order number.
  • I put the number 100500400 in the field AUTO_INCREMENT, click "Continue". Then I made a test purchases but the order id's are still 000000013, 00000014... – Luis Garcia Aug 19 '16 at 17:38
  • i found sequence_order_1 and in operation tab there is Auto_increment the value is 5, so what is the correct value? – Ahmad Darwish Mar 12 '17 at 17:53
  • 1) back up your database before making any changes. 2) instead of 5 you set the value you want for your order id, for example 1035327500. Your next order is should be 1035327501 – Mark Mar 13 '17 at 20:18
  • which magento version are we talking about? – Felix Dec 6 '17 at 16:30
  • @Felix It is for Magento 2 – Kishan Patadia Mar 20 '18 at 9:49
20

Go to your database from phpmyadmin,

Here _1 is used for store id after tablename.

Default Frontend store id is 1. if you have multi store then you have to set query for each store with table name like sequence_order_2 upto sequence_order_.*

Enter below query for table sequence_order_1 is used for default store. If you have multiple store you have to set tablename as per store id in below query.

This is only used for order placed from frontend.

sequence_order_1 is used for order id management in magento 2.

ALTER TABLE sequence_order_1 AUTO_INCREMENT=155555551;

Next order id is start from 155555551.

Below Query is defined for INVOICE, if you want to change invoice id

ALTER TABLE sequence_invoice_1 AUTO_INCREMENT=155555551;

For Shipment ALTER TABLE sequence_shipment_1 AUTO_INCREMENT=155555551;

  • This does not work form me. After doing some test buys, the order id's are still 000000013, 00000014... – Luis Garcia Aug 19 '16 at 16:47
  • excellent answer! – huzefam Jan 11 '17 at 13:29
  • it may help - magento.stackexchange.com/questions/156075/… - – sandip Jan 23 '17 at 18:50
  • could you state the magento version for which this is correct? – Felix Dec 6 '17 at 16:29
  • how to change first digit of the new order id – Tirth Patel Jun 15 at 11:38
16

What was previously in eav_entity_store is now covered in sales_sequence_profile and sales_sequence_meta. The table sequence_order_1 gets populated when orders are placed.

  • Could you roughly state the magento version numbers for which this is true and works? – Felix Dec 6 '17 at 16:30
  • I haven't come across any changes in behaviour on this I think this applies from 2.0.0 up to current release 2.2.1 – Kristof at Fooman Dec 6 '17 at 21:39
  • Still the same on 2.2.4, just tested it. – Frank Groot Aug 28 '18 at 7:47
7

Format for new order id is defined by default by constant in

Magento\SalesSequence\Model\Sequence :

const DEFAULT_PATTERN = "%s%'.09d%s";

It's pattern for sprintf() function that creates new id. To remove leading zeros you have to pass your pattern to constructor like this:

<type name="Magento\SalesSequence\Model\Sequence">
    <arguments>
        <argument name="pattern" xsi:type="string">%s%s%s</argument>
    </arguments>
</type>

or extend Sequence class and change as you need

6

Though all above answers are correct fully/partially, I thought to write down the answer as this would be helpful for future readers. This answer will address two questions:

1) How to change the order sequence number?

2) How to add Prefix or Suffix to an order number?

Before making any blind changes in the database, you should understand which sequence table is associated to what. You can check this from table sales_sequence_meta

Run the query select * from sales_sequence_meta; each store will have sequence table associated with that store. Note the table name viz: Order, Invoice, creditmemo or shipment.

Once that is done, go the table for example if you want to edit order number goto table sequence_order_1 and change the AUTO_INCREMENT value

alter table sequence_order_1 AUTO_INCREMENT=617;

This is all what is required to jump order sequence.

Now, moving further if you dig more and have a requirement where you want to add Prefix/Suffix to order follow below steps:

Goto table sales_sequence_profile and check the data

select * from sales_sequence_profile;

You will observe column prefix and 'suffix'. Initially it would be NULL. You can add the value in there of prefix and suffix for relevant meta_id which you can map from table sales_sequence_meta

This will change order prefix/suffix for all orders.

Hope this helps all !! Cheers !!

4

Fresh Install:
If your on Magento 2.2.0+ and your running a fresh install with multi-store enabled (this is enabled by default) you can simply run the following queries before you place any orders through the site:

ALTER TABLE `sequence_creditmemo_0` AUTO_INCREMENT=303;
ALTER TABLE `sequence_creditmemo_1` AUTO_INCREMENT=303;
ALTER TABLE `sequence_invoice_0` AUTO_INCREMENT=303;
ALTER TABLE `sequence_invoice_1` AUTO_INCREMENT=303;
ALTER TABLE `sequence_order_0` AUTO_INCREMENT=303;
ALTER TABLE `sequence_order_1` AUTO_INCREMENT=303;
ALTER TABLE `sequence_shipment_0` AUTO_INCREMENT=303;
ALTER TABLE `sequence_shipment_1` AUTO_INCREMENT=303;

Please note that 303 is just the number I choose for our site because our old platform left off at order number 300 and I wanted some kind of consistency. Also note that you do not need any of the queries that end with _1 if you enabled single store mode.

Existing Install:
If your already using Magento 2.2.0+ (or upgraded to it) and simply want to bump up the numbers, not alter the padding or add characters, simply check the current AUTO_INCREMENT value on the following tables (assuming multi-site):

  • sequence_creditmemo_0
  • sequence_creditmemo_1
  • sequence_invoice_0
  • sequence_invoice_1
  • sequence_order_0
  • sequence_order_1
  • sequence_shipment_0
  • sequence_shipment_1

And change them using the alter queries shown above but make sure the new value you use is greater than the value you get back from checking the current AUTO_INCREMENT value.

If you are going to mess around with padding or add a custom pattern you need to see the other answers here and review the following tables:

  • sales_sequence_meta
  • sales_sequence_profile

Update: Mult-Stores?
For those not familiar with multi-stores this is a feature that allows you to run completely separate stores from the same Magento install. A common reason to do this is to support other languages. In the back end this is why you can choose the store view your looking at (if multi-store is turned on) when updating the configuration and doing other things for example.

For this reason Magento will add an underscore and a number at the end of certain tables to keep track of the different store views. You'll notice how the queries I provided seem to be duplicate tables but they end with different numbers, 0 or 1 in this case. If you had several stores/ views these numbers would just keep incrementing, _2 or _3 for example. This allows you to manage the id's of each store differently. Your change to the id system in one view (store) does not have to be the same in another.

It is out of scope for this post but this same numbering system applies to other tables in the database allowing you to truly treat and affect each store view differently. You could have a completely different theme per store for example.

  • Why do you keep mentioning about multi stores? What would be different if you had single-store mode enabled? – Bram Apr 9 '18 at 9:16
  • 1
    I answered this is the post: "...note that you do not need any of the queries that end with _1 if you enabled single store mode." Notice that the queries I provided call what looks like duplicate tables except the end has a number, 0 or 1 in this case. If you disabled multi-store you would only see _0 nothing with _1 or _2 or _3 and so on. I added an update to clarify this. – Blizzardengle Apr 10 '18 at 14:45
2

Change The Sequence To change the order number padding edit Sequence.php (I believe there is a better way via a di.xml)

vendor/magento/module-sales-sequence/Model/Sequence.php

Edit this line

const DEFAULT_PATTERN  = "%s%'.09d%s";

Change the "9" to the desired padding

Example

const DEFAULT_PATTERN  = "%s%'.05d%s";

Change the Prefix

In admin > Stores > Sales > Order Number > 
Enter Desired prefix

To keep everything easy add Fooman same order invoice number

  • 1
    It's a bad idea to edit the padding pattern in the core files, you should use 'my display name's method to change the padding pattern. – Timon de Groot Jun 28 '17 at 9:22
  • 1
    What magento is this? (admin > Stores > Sales > Order Number ) I use MAG 2.2.2 but the menu order seems to be changed. – Mohammed Joraid Feb 18 '18 at 3:56
1

Change the Order Increment Id

  1. Open your PHP Admin
  2. Find & click on the table called: sequence_order_1
  3. Click on "Operations" tab & under "AUTO_INCREMENT" edit to the desired order number.

For another Store same step follow

  1. Find & click on the table called: sequence_order_2
  2. Click on "Operations" tab & under "AUTO_INCREMENT" edit to the desired order number.

This will change the order number for the multiple stores

0

To Adjust Invoice numbers Via PHPMYADMIN

INSERT INTO sequence_order_1 (sequence_value) VALUES ('2000');

2000 will be the new start number

0

You can make changes to Order Number Directly in Database Magento 2 by this tip

First of all, you need to open your PHP Admin database. Then find and open the table “sales_sequence_profile”.

After that, you can make changes to default order number in your Magento 2 website:

a. Change Order Increment ID

b. Change Order Number Prefix

c. Change Order Number Suffix

d. Change Order Number Start-value

e. Change Pad-length

I see the full tutorial in this article: Complete Tutorial Guide to Change Order Number in Magento 2

  • The name of the table is incorrect. it should be "sales_sequence_profile" – Dennis van Schaik Jul 24 '18 at 8:55
0

To permantly get rid of (or change) the extra zeros you must not override core files. In your app/etc/di.xml add the following and change the "%s%'.09d%s" value accordingly (in this example two 00 are padded):

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Framework\DB\Sequence\SequenceInterface">
        <arguments>
            <argument name="pattern" xsi:type="string">%s%'.02d%s</argument>
        </arguments>
    </type>
</config>

The database edits for order and invoice numbers are perfectly described in Rakesh Jesadiya's answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.