Magento 2: Change Increment ID Prefix, Suffix, Start value, Step, Pad length of Order, Invoice, Creditmemo & Shipment

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 order entity_type of the test store,

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 test is 2.
– For the test store’s order, the last inserted order number will be stored in the table sequence_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.