This article shows how you can change/update the Order Increment ID, Invoice Increment ID, Creditmemo Increment ID, Shipment Increment ID of your Magento 2 store.
In Magento 1, you could simply change the increment prefix and last increment id from the database table entity_store_id.
In Magento 2, it’s different. You have the option to change the prefix, suffix, start value, step, and pad-length of the increment id.
There’s a specific pattern/formula to generate the increment ID in Magento 2. The pattern is defined in the class Magento\SalesSequence\Model\Sequence in the function calculateCurrentValue().
/**
* Calculate current value depends on start value
*
* @return string
*/
private function calculateCurrentValue()
{
return ($this->lastIncrementId - $this->meta->getActiveProfile()->getStartValue())
* $this->meta->getActiveProfile()->getStep() + $this->meta->getActiveProfile()->getStartValue();
}
The current value is then surrounded by prefix and suffix. It’s defined in the function getCurrentValue() of the same class Magento\SalesSequence\Model\Sequence.
Check Store, Sequence Meta & Sequence Profile
I have two frontend stores with code default and test. The store with the code admin is the admin store and not the frontend store.
mysql> SELECT * FROM `store`;
+----------+---------+------------+----------+--------------------+------------+-----------+
| store_id | code | website_id | group_id | name | sort_order | is_active |
+----------+---------+------------+----------+--------------------+------------+-----------+
| 0 | admin | 0 | 0 | Admin | 0 | 1 |
| 1 | default | 1 | 1 | Default Store View | 0 | 1 |
| 2 | test | 1 | 1 | Test | 0 | 1 |
+----------+---------+------------+----------+--------------------+------------+-----------+
3 rows in set (0.00 sec)
Both of the frontend stores are under a single website nameed Main Website.
mysql> SELECT * FROM `store_website`;
+------------+-------+--------------+------------+------------------+------------+
| website_id | code | name | sort_order | default_group_id | is_default |
+------------+-------+--------------+------------+------------------+------------+
| 0 | admin | Admin | 0 | 0 | 0 |
| 1 | base | Main Website | 0 | 1 | 1 |
+------------+-------+--------------+------------+------------------+------------+
2 rows in set (0.00 sec)
The prefix, suffix, start_value, and step data is stored in the table named sales_sequence_profile.
mysql> SELECT * FROM `sales_sequence_profile`;
+------------+---------+--------+--------+-------------+------+------------+---------------+-----------+
| profile_id | meta_id | prefix | suffix | start_value | step | max_value | warning_value | is_active |
+------------+---------+--------+--------+-------------+------+------------+---------------+-----------+
| 1 | 1 | NULL | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 2 | 2 | NULL | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 3 | 3 | NULL | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 4 | 4 | NULL | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 5 | 5 | NULL | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 6 | 6 | NULL | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 7 | 7 | NULL | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 8 | 8 | NULL | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 9 | 9 | 2 | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 10 | 10 | 2 | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 11 | 11 | 2 | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 12 | 12 | 2 | NULL | 1 | 1 | 4294967295 | 4294966295 | 1 |
+------------+---------+--------+--------+-------------+------+------------+---------------+-----------+
12 rows in set (0.00 sec)
The meta_id present in the above table comes from the table sales_sequence_meta.
mysql> SELECT * FROM `sales_sequence_meta`;
+---------+-------------+----------+-----------------------+
| meta_id | entity_type | store_id | sequence_table |
+---------+-------------+----------+-----------------------+
| 1 | order | 0 | sequence_order_0 |
| 2 | invoice | 0 | sequence_invoice_0 |
| 3 | creditmemo | 0 | sequence_creditmemo_0 |
| 4 | shipment | 0 | sequence_shipment_0 |
| 5 | order | 1 | sequence_order_1 |
| 6 | invoice | 1 | sequence_invoice_1 |
| 7 | creditmemo | 1 | sequence_creditmemo_1 |
| 8 | shipment | 1 | sequence_shipment_1 |
| 9 | order | 2 | sequence_order_2 |
| 10 | invoice | 2 | sequence_invoice_2 |
| 11 | creditmemo | 2 | sequence_creditmemo_2 |
| 12 | shipment | 2 | sequence_shipment_2 |
+---------+-------------+----------+-----------------------+
12 rows in set (0.00 sec)
Joining sales_sequence_meta and store tables to display the store code and store name in same result view with the following query:
SELECT meta.*, store.name, store.code, store.is_active
FROM `sales_sequence_meta` AS meta
LEFT JOIN store
ON store.store_id = meta.store_id;
We can join the three tables sales_sequence_profile, sales_sequence_meta and store to see a more clear data view.
We can use the following query:
SELECT profile.*, meta.entity_type, meta.store_id, meta.code, meta.name, meta.sequence_table, meta.is_active AS is_active_store
FROM `sales_sequence_profile` AS profile
LEFT JOIN (
SELECT meta.*, store.name, store.code, store.is_active
FROM `sales_sequence_meta` AS meta
LEFT JOIN store
ON store.store_id = meta.store_id
) AS meta
ON profile.meta_id = meta.meta_id;
Selecting speficic rows from the sales_sequence_profile table.
SELECT profile.profile_id, profile.meta_id, profile.prefix, profile.suffix, profile.start_value, profile.step, meta.entity_type, meta.store_id, meta.code, meta.sequence_table
FROM `sales_sequence_profile` AS profile
LEFT JOIN (
SELECT meta.*, store.name, store.code, store.is_active
FROM `sales_sequence_meta` AS meta
LEFT JOIN store
ON store.store_id = meta.store_id
) AS meta
ON profile.meta_id = meta.meta_id;
Here’s the result:
mysql> SELECT profile.profile_id, profile.meta_id, profile.prefix, profile.suffix, profile.start_value, profile.step, meta.entity_type, meta.store_id, meta.code, meta.sequence_table
-> FROM `sales_sequence_profile` AS profile
-> LEFT JOIN (
-> SELECT meta.*, store.name, store.code, store.is_active
-> FROM `sales_sequence_meta` AS meta
-> LEFT JOIN store
-> ON store.store_id = meta.store_id
-> ) AS meta
-> ON profile.meta_id = meta.meta_id;
+------------+---------+--------+--------+-------------+------+-------------+----------+---------+-----------------------+
| profile_id | meta_id | prefix | suffix | start_value | step | entity_type | store_id | code | sequence_table |
+------------+---------+--------+--------+-------------+------+-------------+----------+---------+-----------------------+
| 1 | 1 | NULL | NULL | 1 | 1 | order | 0 | admin | sequence_order_0 |
| 2 | 2 | NULL | NULL | 1 | 1 | invoice | 0 | admin | sequence_invoice_0 |
| 3 | 3 | NULL | NULL | 1 | 1 | creditmemo | 0 | admin | sequence_creditmemo_0 |
| 4 | 4 | NULL | NULL | 1 | 1 | shipment | 0 | admin | sequence_shipment_0 |
| 5 | 5 | NULL | NULL | 1 | 1 | order | 1 | default | sequence_order_1 |
| 6 | 6 | NULL | NULL | 1 | 1 | invoice | 1 | default | sequence_invoice_1 |
| 7 | 7 | NULL | NULL | 1 | 1 | creditmemo | 1 | default | sequence_creditmemo_1 |
| 8 | 8 | NULL | NULL | 1 | 1 | shipment | 1 | default | sequence_shipment_1 |
| 9 | 9 | 2 | NULL | 1 | 1 | order | 2 | test | sequence_order_2 |
| 10 | 10 | 2 | NULL | 1 | 1 | invoice | 2 | test | sequence_invoice_2 |
| 11 | 11 | 2 | NULL | 1 | 1 | creditmemo | 2 | test | sequence_creditmemo_2 |
| 12 | 12 | 2 | NULL | 1 | 1 | shipment | 2 | test | sequence_shipment_2 |
+------------+---------+--------+--------+-------------+------+-------------+----------+---------+-----------------------+
12 rows in set (0.01 sec)
Change Prefix
Let’s change the prefix of the order for the test store.
In the above query result, you can see that for the
orderentity_typeof theteststore,profile_id = 9
meta_id = 9
I am setting the prefix as TEST-.
Here’s the query to update the prefix of the order for the test store:
UPDATE `sales_sequence_profile` SET `prefix` = 'TEST-' WHERE `meta_id` = 9;
You may also use profile_id instead of the meta_id. Both will result in the same.
UPDATE `sales_sequence_profile` SET `prefix` = 'TEST-' WHERE `profile_id` = 9;
You can do the same for invoice, credit memo, and shipment for all the stores available in your database table.
Change Suffix
I am setting the suffix as -MAG2.
Here’s the query to update the suffix of the order for the test store:
UPDATE `sales_sequence_profile` SET `suffix` = '-MAG2' WHERE `meta_id` = 9;
Change Start Value
I am setting the start value as 5.
Here’s the query to update the start value of the order for the test store:
UPDATE `sales_sequence_profile` SET `start_value` = 5 WHERE `meta_id` = 9;
Change Step
I am setting the step as 10.
Here’s the query to update the step of the order for the test store:
UPDATE `sales_sequence_profile` SET `step` = 10 WHERE `meta_id` = 9;
Change Pad-length
The default sequence pattern is present as a constant in the class file: Magento\SalesSequence\Model\Sequence.
File: vendor/magento/module-sales-sequence/Model/Sequence.php
/**
* Default pattern for Sequence
*/
const DEFAULT_PATTERN = "%s%'.09d%s";
You can change it by creating a custom module. Write the following code in your custom module’s etc/di.xml file.
File: app/code/YourNamespace/YourModule/etc/di.xml
<!--?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%'.05d%s</argument>
</arguments>
</type>
</config>
Change last inserted order, invoice, credit memo, shipment number/id
For any order of any store, the last inserted order number will be stored in the table sequence_order_StoreId.
For example,
– The store ID of the store
testis2.
– For the test store’s order, the last inserted order number will be stored in the tablesequence_order_2.
It’s the same case for invoice, credit memo, and shipment of all the stores.
For example,
– For the test store’s invoice, the last inserted invoice number will be stored in the table
sequence_invoice_2.
View the last inserted id of the order
Here, we check the last inserted id for the order of the test store.
mysql> SELECT auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'magento22' AND TABLE_NAME = 'sequence_order_2';
+----------------+
| auto_increment |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
Change the auto_increment value of the sequence table
We can increase the auto_increment value of the table with the following query:
ALTER TABLE `sequence_order_2` AUTO_INCREMENT=1000;
Add the new value to the sequence table
We are trying to start new orders from 1000 as we have set the value of auto_increment to 1000. So, we also need to add a row with the value 1000 into the table.
INSERT INTO `sequence_order_2` (`sequence_value`) VALUES ('1000');
Reference:
1. How to Change Magento 2 Increment ID for Orders, Invoices, Credit Memos and Shipments
Hope this helps. Thanks.