How to have common order sequence for multiple stores (under the same website)?

Question

Magento 2 uses table sales_sequence_meta and sales_sequence_profile for defining the entity prefix for stores.

If 2 and 3 are the store_id of two store views of the same website, the table data may look like this

mysql> SELECT * FROM sales_sequence_meta WHERE entity_type='order' AND store_id IN (2, 3);
+---------+-------------+----------+-------------------+
| meta_id | entity_type | store_id | sequence_table |
+---------+-------------+----------+-------------------+
| 11 | order | 2 | sequence_order_2 |
| 12 | order | 3 | sequence_order_3 |
+---------+-------------+----------+-------------------+

And sales_sequence_profile table looks like

mysql> SELECT * FROM sales_sequence_profile WHERE meta_id IN (11, 12);
+------------+---------+--------+--------+-------------+------+------------+---------------+-----------+
| profile_id | meta_id | prefix | suffix | start_value | step | max_value | warning_value | is_active |
+------------+---------+--------+--------+-------------+------+------------+---------------+-----------+
| 11 | 11 | 2 | | 1 | 1 | 4294967295 | 4294966295 | 1 |
| 12 | 12 | 3 | | 1 | 1 | 4294967295 | 4294966295 | 1 |
+------------+---------+--------+--------+-------------+------+------------+---------------+-----------+

With these, the generated order numbers may look like 2000000001 ... and 3000000001... for stores with id 2 and 3 respectively.

But I want the same sequence 200000000x (2 as the prefix) for orders from store_id = 3 as well.

From looking at table, I guess I can easily update as:

UPDATE sales_sequence_meta SET sequence_table = 'sequence_order_2' WHERE entity_type='order' AND store_id = 3;
UPDATE sales_sequence_profile SET prefix = '2' WHERE profile_id = 12;

But not sure if it's the correct way to do it.
Please share your views on this if you have any hands-on experience.

• dotancohen: What is your end goal in making these orders sequential? That might affect the recommended solution. What problem are you trying to solve?
• MagePsycho: I want English & French language stores to have a common prefix i.e. both starting from 2xxxxxxxxx (as shown above)
• dotancohen: Let me rephrase the question. Why do you want English & French language stores to have a common prefix? What problem are you trying to solve?
• MagePsycho: That's the client who wants that :) so that they know that this order belongs to XYZ stores(feel unified).

Answer 1

I think it is not meant to happen. If you take a look at the sales order module and particularly the db_schema.xml file, there is a constraint on the field increment_id

<constraint xsi:type="unique" referenceId="SALES_ORDER_INCREMENT_ID_STORE_ID">
 <column name="increment_id"/>
 <column name="store_id"/>
 </constraint>

Your change would imply this constraint has to be removed. Now, rest assured this might be technically doable. However, it is not advisable to break Magento database integrity as you may know.

The way round may be to add a new column that duplicates the field increment_id and add a business logic that shows the additional field.

The problem with this method is that you will need a lot of changes in various documents like invoice, order email and so on..

EDIT: Now that I understand your target better, I'd say, just change the prefix as per your query and then ensure the start_value is set so that both your store order id do not overlap.

The outcome will be what you are after and will likely work (I have not tested). The issues to watch out for are that your payment gateway will not let 2 increment_id with the same value to be created. Although in theory, it should not happen, here on this occasion you are making this scenario more likely.

• MagePsycho: I don't want the same order number but the same prefix, for example, 2 for both the stores.
• Herve Tribouilloy: np, I updated my answer. I'd say the requirement you're having is not conventional but I take you know this, good luck
• bfontaine: @HerveTribouilloy this is a legal requirement for invoices in France.
• Herve Tribouilloy: @bfontaine Magento is extremely flexible, we can work on this

Answer 2

I came across this issue today as well.

I believe the correct and easy solution is to update the records in sales_sequence_meta for all stores that you want to share the same sequence.

For example, if you want all stores to share the same sequence, run this:

update `sales_sequence_meta` set sequence_table = 'sequence_order_1' WHERE entity_type = 'order' and store_id <> 0;
update `sales_sequence_meta` set sequence_table = 'sequence_invoice_1' WHERE entity_type = 'invoice' and store_id <> 0;
update `sales_sequence_meta` set sequence_table = 'sequence_creditmemo_1' WHERE entity_type = 'creditmemo' and store_id <> 0;
update `sales_sequence_meta` set sequence_table = 'sequence_shipment_1' WHERE entity_type = 'shipment' and store_id <> 0;

This will make all stores share the sequence in the sequence table of store_id = 1.

But it's important not to update the sequence_table for store_id = 0, or to update the other store_id's sequence_table to sequence_order_0, because this causes the integrity constraint error when running bin/magento setup:upgrade

After that you should also equalize all prefixes for store_ids in sales_sequence_profile table:

Example, remove the prefixes:

update 'sales_sequence_profile' set prefix = null

You can also insert an initial sequence (increment_id) to have orders start from e.g. 100000000:

insert into 'sequence_order_1' values (100000000)

This mimics a prefix of "1".

If you're doing this on a live site (that already has many orders in all stores), just make sure that if you have store views that have a higher sequence/increment count than the store that was originally using sequence_order_1 - you then need to insert a higher counter in sequence_order_1 than the highest of the other stores.

This way you ensure that there won't be any conflicting increment_ids between stores.

Make sure to test before implementing this