How to Create Custom Order Increment Id & Prefix in Magento

In Magento, the order number starts from 100000001 and it is set to increase by default. The order number enables the customer to the customer to learn how many orders are placed from this store. For example, an Order Id 100000005 reveals to the customer that his order is just 5 th from your store. Moreover, this format doesn’t tell you anything else as a store owner about the order apart from the fact that it is 5 th order. Many Companies want to use a custom Order Id so that customer does not know exactly how many orders have been placed before.

Here is some SQL commands you can run to change the number formats. In addition I have included SQL commands to modify the invoice, shipment and credit memo increment ID as well.

Change Order Increment Id and Prefix

Change Order Increment Id on all stores:

Execute the SQL command to change Order Incremnt Id on all stores.Replace ‘XXXXXXXXXX’ with your custom Order Increment Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_last_id='XXXXXXXXXX' WHERE eav_entity_type.entity_type_code='order';

Change Order Prefix on All stores:

Execute following SQL command to change Order Prefix value.Replace ‘X’ with your desired prefix.You can set prefix value to null also.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_prefix='X' WHERE eav_entity_type.entity_type_code='order';

Change Order Increment Id on a specific store:

You can update the Order Increment Id of a particular store by using store Id.The following  SQL command reveals store Id and name.

SELECT store_id, name FROM core_store;

Execute the following command to update Order Increment Id of a particular store.Replace   replace ‘XXXXXXXXXX’ with Order Increment Id and ‘Y’ with your store Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_last_id='XXXXXXXXXX' WHERE eav_entity_type.entity_type_code='order' AND eav_entity_store.store_id = 'Y'.

Change Order Prefix on a specific store:

Execute the following SQL command to change Order Prefix of a particular store. Replace ‘X’ with your desired prefix and ‘Y’ with your Store Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_prefix='X' WHERE eav_entity_type.entity_type_code='order' AND eav_entity_store.store_id = 'Y';

Change Invoice Increment Id and Prefix

Sometimes you will be required to change invoice increment id and prefix also.Here is the code for getting these tasks done.

Change Invoice Increment Id on all stores:

Execute the following commands to change Invoice Increment Id on all stores.Repalce ‘XXXXXXXXXX’ with  your Invoice Increment  Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_last_id='XXXXXXXXXX' WHERE eav_entity_type.entity_type_code='invoice';

Change Invoice Prefix on all stores:

Execute the following SQL command to change Invoice Prefix for all stores.Replece ‘X’ with your Invoice Prefix.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_prefix='X' WHERE eav_entity_type.entity_type_code='invoice';  

Change Invoice Increment Id on a specific store.

Execute the following SQL command to change Invoice Id of a specific store.Replace ‘XXXXXXXXXX’ with your invoice id and ‘Y’ with your Store Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_last_id='XXXXXXXXXX' WHERE eav_entity_type.entity_type_code='invoice' AND eav_entity_store.store_id = 'Y';

Change Invoice Prefix on a specific store:

Execute the following SQL  command to update Invoice Irefix on a specific store.Do not forgot to replace ‘X’ with your Increment Prefix and ‘Y’ with Store Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_prefix='X' WHERE eav_entity_type.entity_type_code='invoice' AND eav_entity_store.store_id = 'Y';  

Change Shipment Increment Id and Prefix

Sometimes you will be required to change Shipment Increment Id and Prefix .Here is the code for getting these tasks done.

Change Shipment Increment Id on all stores:

 Execute the following SQL to change shipment increment Id.Replace ‘ XXXXXXXXXX’ with your desired Shipment Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_last_id='XXXXXXXXXX' WHERE eav_entity_type.entity_type_code='shipment';

Change Shipment Prefix on all stores:

Execute the following SQL quey to change Shipment Prefix on all stores. Replce ‘X’ with  your desired shipment prefix and ‘Y’ with your  store ID.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_prefix='X' WHERE eav_entity_type.entity_type_code='shipment';

Change Shipment Increment Id on a specific store:

Execute the following SQL query to change Shipment Increment Id on a specific store.Replace ‘ XXXXXXXXXX’ with your Shipment Increment Id and ‘Y’ with your Store Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_last_id='XXXXXXXXXX' WHERE eav_entity_type.entity_type_code='shipment' AND eav_entity_store.store_id = 'Y';

Change Shipment Prefix on a Specific Store:

Execute the following SQL to change Shipment Prefix on a specific Store.Replce ‘X’ with your shipment prefix and ‘Y’ with your Store Id.

UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_prefix='X' WHERE eav_entity_type.entity_type_code='shipment' AND eav_entity_store.store_id = 'Y';

Change Credit Memo Increment Id and Prefix

Sometimes you will be required to change Credit Memo Increment Id and prefix .Here is the code for getting these tasks done. Change Credit Memo Incremnt Id on all stores: Execute the following SQL command to change Credit Memo Incremnt Id on all stores.Replce ‘ XXXXXXXXXX’ with your desired Credit Memo Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_last_id='XXXXXXXXXX' WHERE eav_entity_type.entity_type_code='creditmemo';

Change Credit Memo Prefix on all Stores:

Execute the following SQL to change the Credit Memo Prefix on all stores.Replace ‘X’ with your desired Increment Prefix .

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_prefix='X' WHERE eav_entity_type.entity_type_code='creditmemo';

Change Credit Memo Increment Id on a Specific Store:

Execute the following SQL to change Credit Memo Incremnt Id of a specific store. Replace ‘ XXXXXXXXXX’ with your Credit Memo Incremnt Id and ‘Y’ with your Store Id.

   UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_last_id='XXXXXXXXXX' WHERE eav_entity_type.entity_type_code='creditmemo' AND eav_entity_store.store_id = 'Y';

Change your Credit Memo Prefix on a specific store:

Execute the following SQL to change Credit Memo Prefix on a specific store. Replace ‘X’ with your Credit Memo prefix and ‘Y’ with your Store Id.

  UPDATE eav_entity_store INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id SET eav_entity_store.increment_prefix='X' WHERE eav_entity_type.entity_type_code='creditmemo' AND eav_entity_store.store_id = 'Y';

We can also create a magento module to accomplish this task, i would strike back with a detailed blog on this topic, and meanwhile let me know what you think about this post and feel free to drop a comment.

Know More About This Topic from our Techies