Magento 2 - What is the correct way to change default Order Id?

Question

Magento 2. After a 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 the default Id for Orders, Invoice or Shipment we simply had to change values in the 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.

Answer 1

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.

Answer 2

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;

Answer 3

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.

Answer 4

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 !!

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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

Answer 9

STEP – 1 First of all, you need to register a new module by creating a registration.php file in the app/code/MageDelight/Testgrid/ folder and paste the following code.

<?php

\Magento\Framework\Component\ComponentRegistrar::register(

     \Magento\Framework\Component\ComponentRegistrar::MODULE,

     'MageDelight_Testgrid',

     __DIR__

);

STEP – 2 After that, you need to create a module.xml file to define installation and update functions in the app/code/MageDelight/Testgrid/etc folder and paste the following code.

<?xml version="1.0"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">

     <module name="MageDelight_Testgrid" setup_version="1.0.0">

     </module>

</config>

STEP – 3 Next, you need to create a di.xml file in the app/code/MageDelight/Testgrid/etc/ folder and paste the following code.

<?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">ABC%s%'.06d%sDFG</argument>

         </arguments>

     </type>

     <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">

         <arguments>

             <argument name="collections" xsi:type="array">

                 <item name="sales_order_grid_data_source" 

xsi:type="string">MageDelight\Testgrid\Model\ResourceModel\Order\Grid\Collection</item>

             </argument>

         </arguments>

     </type>

     <type name="MageDelight\Testgrid\Model\ResourceModel\Order\Grid\Collection">

         <arguments>

             <argument name="mainTable" xsi:type="string">sales_order_grid</argument>

             <argument name="resourceModel"

xsi:type="string">Magento\Sales\Model\ResourceModel\Order</argument>

         </arguments>

     </type>

</config>

STEP – 4 Lastly, you need to change the pattern from %s%’.09d%s to ABC%s%’.06d%sDFG, and it will change the default Magento 2 order number.

Answer 10

To Adjust Invoice numbers Via PHPMYADMIN

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

2000 will be the new start number

Answer 11

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

Answer 12

1. Access the database to be able to perform SQL queries (via for instance mysql-client or phpMyAdmin).
2. Then select your Magento 2 database.
3. To change prefix to 913 for order-numbers execute:

UPDATE `sales_sequence_profile` SET `prefix` = '913' WHERE `profile_id` 
IN (SELECT `meta_id` FROM `sales_sequence_meta` WHERE `entity_type` = "order")
LIMIT 1;

Answer 13

In Magento 2, after a fresh install, the new order ID starts from 100000001, 100000002, and so on. If you want to change the increment ID to a higher number, you can use the following methods:

Using the Magento 2 Admin Panel:

1. Go to Stores > Settings > Configuration > Sales > Sales > Order Settings
2. Scroll down to the "Order Increment ID" field, and enter the desired number.
3. Save the configuration and clear the cache.

Using SQL:

1. Log in to your database and run the following SQL query:

UPDATE sequence_sales_order SET sequence_value = <new_increment_id> WHERE sequence_name = 'sales_order';

2. Replace <new_increment_id> with the desired value.

3. Clear the cache and test that the new order ID is being used.

Using PhpMyAdmin:

1. Open PhpMyAdmin and select your Magento database.
2. Go to the table "sequence_sales_order"
3. find the record with sequence_name = 'sales_order'
4. change the value in the "sequence_value" field to your desired number
5. save and clear the cache.

It's important to note that changing the order ID can have an impact on the order management, reporting, and other functionalities in Magento that rely on the increment ID of the order, so it's important to test the changes and make sure that everything is working as expected before going live.

Also, it's worth mentioning that if you already have orders in the store, and you change the order ID, it could cause confusion in the order management, so it's better to make these changes before starting to take orders.

Answer 14

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