WarpConduit Computing

How to Change the Order Increment ID and Prefix in Magento

by

Magento Order NumbersSometimes the need arises where you must change the Magento order numbering. Perhaps it is necessary to avoid conflict with a separate Magento installation or other ecommerce platform. Or maybe you just want to increase your order number to look like you’ve processed hundreds of thousands of orders. Whatever the case may be here are some SQL commands you can run to check the status of your store’s order numbers, and commands to also change the numbering. In addition, I’ve included SQL commands to modify the invoice, shipment, and credit memo increment IDs as well.

Note: These queries were tested on Magento Community Edition v1.6.2.0. I fully expect the increment ID queries to work on v1.4.1 and above, but DO NOT set the increment prefix to NULL on anything less than v1.6.0. Backup your database before making any changes in case your copy of Magento reacts unexpectedly.

Update 12/6/2017: This method has been successfully tested on Magento CE v1.9.3.6.

Find the Current Increment IDs for All Stores

SELECT core_store_group.name as group_name, core_website.name as website_name, core_store.name as store_name, core_store.store_id, increment_prefix, increment_last_id, entity_type_code
FROM eav_entity_store
INNER JOIN eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
INNER JOIN core_store ON core_store.store_id = eav_entity_store.store_id
INNER JOIN core_store_group ON core_store_group.group_id = core_store.group_id
INNER JOIN core_website ON core_website.website_id = core_store.website_id
WHERE eav_entity_store.store_id != 0 ORDER BY eav_entity_store.store_id;

This will display your current increment ID and prefix for all document types (quotes, orders, invoices, shipments, and credit memos). In addition it will show you the website group, website name, store name, and store ID for each type of increment ID to help you in updating a specific store among multiple stores.
When making changes keep in mind that the increment_prefix field type is varchar(20) and that the increment_last_id field type is varchar(50).

Order Increment ID and Prefix

Change your Order Increment ID on All Stores

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';

Replace the X‘s with your desired order number and run the query.

Change your Order Prefix on All Stores

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';

Replace the X with your desired order prefix or remove the quotes and set X to NULL (no quotes) to disable the order prefix, then run the query.

Change your Order Increment ID on a Specific Store

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';

Replace the X‘s with your desired order number, replace Y with the store ID of the store you want to modify, then run the query.

Change your Order Prefix on a Specific Store

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';

Replace the X with your desired order prefix or remove the quotes and set X to NULL (no quotes) to disable the order prefix, then replace Y with the store ID of the store you want to modify. Run the query.

Invoice Increment ID and Prefix

Change your Invoice Increment ID on All Stores

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';

Replace the X‘s with your desired invoice number and run the query.

Change your Invoice Prefix on All Stores

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';

Replace the X with your desired invoice prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then run the query.

Change your Invoice Increment ID on a Specific Store

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';

Replace the X‘s with your desired invoice number, replace Y with the store ID of the store you want to modify, then run the query.

Change your Invoice Prefix on a Specific Store

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';

Replace the X with your desired invoice prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then replace Y with the store ID of the store you want to modify. Run the query.

Shipment Increment ID and Prefix

Change your Shipment Increment ID on All Stores

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';

Replace the X‘s with your desired shipment number and run the query.

Change your Shipment Prefix on All Stores

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';

Replace the X with your desired shipment prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then run the query.

Change your Shipment Increment ID on a Specific Store

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';

Replace the X‘s with your desired shipment number, replace Y with the store ID of the store you want to modify, then run the query.

Change your Shipment Prefix on a Specific Store

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';

Replace the X with your desired shipment prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then replace Y with the store ID of the store you want to modify. Run the query.

Credit Memo Increment ID and Prefix

Change your Credit Memo Increment ID on All Stores

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';

Replace the X‘s with your desired credit memo number and run the query.

Change your Credit Memo Prefix on All Stores

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';

Replace the X with your desired credit memo prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then run the query.

Change your Credit Memo Increment ID on a Specific Store

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';

Replace the X‘s with your desired credit memo number, replace Y with the store ID of the store you want to modify, then run the query.

Change your Credit Memo Prefix on a Specific Store

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';

Replace the X with your desired credit memo prefix or remove the quotes and set X to NULL (no quotes) to disable the prefix, then replace Y with the store ID of the store you want to modify. Run the query.

Comments

  1. Christopher Wang says

    Just wanted to point out that your code is a bit off. For specific stores it should be eav_entity_store.store_id = ‘Y’; instead of just eav_entity_store = ‘Y’;

    Otherwise, thanks a lot for the help!

  4. Niels says

    Great detailed explanation Josh, just what I am looking for. If I look at the comment dates I presume this will also work fine on a 1.7.0.2 installation with mutiple websites.. cculd anyone confirm this? — >

    Also has anyone tried the following scenerio: 4 websites A, B, C and D with 6 stores per website due to languages. Website A seperate prefix and increment from other websites but shared by all its stores, same goes for website B. Now for the part I think might cause an issue: Website C & D and both their stores will need to share the same prefix and share increment.

    Would be great if someone has encountered this setup already and shed some light on wether this will be possible in this way or not.

    Thanks again for the article Josh, you put me in the right direction and saved me loads of time even before I got started on this 🙂

    Looking forward to any replies on my scenerio.

  5. tonio says

    It works with 1.7.0.2
    Warning !!
    You can’t have same number with different stores, so your prefix have to be different.

    i suggest this link : you can increment invoices even if you have different stores :
    http://www.magentocommerce.com/boards/viewthread/25668

    Find in app/code/mage/sales/model/entity/setup.php the following piece of code:
    'invoice' => array( 'entity_model' => 'sales/order_invoice', 'table' => 'sales/order_entity', 'increment_model' => 'eav/entity_increment_numeric', 'increment_per_store' => false,
    and change
    'increment_per_store' => false
    to
    'increment_per_store' => true

    After creating a new invoice, table eav_entity_store (MySQL, accessible via phpMyAdmin) should show a new entry with store_id=0. You may delete the previous entry, with entity=16 (at least in version 1.3.2.2 this is the invoice entry).

    Also, I changed in table eav_entity_type the increment_per_store from the invoice entity to 0. This is the most important step actually!

  8. Zookdcity says

    Hi,

    Just one question.

    In Spain we have to reset invoice number each year.

    So with that can I change prefix to have storecode+year and star invoice number and credit memo from the beginning ?

    In this scenario I think there will be not duplicates.

    Regards

    • Josh Hartman says

      Yes, each year you would modify increment_prefix and reset the increment_last_id. See the following example where all prefixes and increment ids are modified for a specific store.

      UPDATE eav_entity_store
SET eav_entity_store.increment_prefix='ABC2013', eav_entity_store.increment_last_id='100000000'
WHERE eav_entity_store.store_id = '1';

      I have tested this on a local Magento install, but I encourage you to do your own testing. Thank you for your comment.

  9. Jason says

    Josh-

    I’m having an issue updating the specific increment_id for a handful of individual orders. Our ERP/Order Processing system is not compatible with the -1, -2, etc. pattern that Magento uses for modified orders, so I need to change the order number for any existing orders in the database that have that appended to the end.

    The issue is that increment_id appears to be littered throughout multiple tables, so I’m wondering how important it is to catch all of those, or if I can just update them in sales_flat_order without negatively impacting Magento queries.

    Any insight?

    Thanks in advance.

    • Josh Hartman says

      I haven’t messed with this myself, but if you are using a modern version of Magento (1.5+), then modifying the increment_id column in the sales_flat_order table should do it. See this article for a little more insight. Make a backup before trying anything, or create a development environment for testing.

  10. Boat Audio says

    Thanks Josh. sales_flat_order has a lot of foreign keys, so it’s going to take some time to decipher what exactly needs to be updated, but that at least confirms my suspicions.

  11. Abhilash says

    Hi

    I want to remove my prefix from order id.
    When i used NULL, my order id got prefixed with NULL like NULL56821.
    Any fixes ?

  12. krissy pineda says

    Im having some problems though with my definitelydiff.com. I received several admin emails this morning with this error:
    Payment transaction failed.
    Reason
    SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘DDC13-2147483648′ for key 2

    The only change i can think of that i have made is changing the increment ID and Prefix IDs for Order, Invoice, Shipment and Credit memo.

    Order Increment ID and Prefix
    Change your Order Increment ID on All Stores
    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’;

    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’;

    etc. (the same for invoice, shipment, credit memo)

    If you can help me, i would be very grateful.

    • Josh Hartman says

      Do you have multiple stores on that Magento installation?

      I’ve recently learned that you can’t have the same increment ID across all stores.

  13. N says

    I think this is going to fix my HUGE problem with Postback errors for payments! Thanks!!!

  14. Pavel says

    For those who is not a developer and need really quick solution there is a ready-made tool for changing the order number. Custom Order number from Amasty.

  15. Roger says

    Thanks for the instructions. Please check the titles for “Shipment Increment ID and Prefix”. It has two subtitles with “order” in them, while they are about “shipment”.

  16. Ram says

    working fine , but when view order , edit order detail it’s showing error..
    There has been an error processing your request

    How to correct it.

    • Josh Hartman says

      Haven’t heard of it causing an admin issue. Make sure to follow the insructions and don’t just copy/paste/execute.
      The debug backtrace will tell you where the error occured. Use that to debug and find a solution.

  17. Perry says

    Hi! There is also a quick solution – Custom Order Number by Amasty. It helps easily change order, invoice numbers for non-developers (like me :))

  19. Charles Coleman says

    This solutions still seems to work just fine on Magento 1.9. Thanks for the tips… this is much better than buying an extension that you must reply on long term when you could just make a single DB update and be set for life.

  20. Rick says

    I’d like to change the backorder’s invoice no. increment. Currently, it append’s and starts with ‘-1’ (ex. 10001-1, 10001-2, etc.) after the original invoice no. How do I start the increment to ‘2’ instead of ‘1’?

    Thanks.

  21. James says

    Nice guide, thanks 🙂
    Is there any reason why I shouldn’t change the numbers to 8 digits instead of 9?
    We have some 16 bit shipping software that I’m trying to integrate with our magento which only supports 8.3 file name format, so I’d like to remove one of the zeros from our order/invoice/shipment numbers.
    thanks,
    James

  22. sIiiS Hyper says

    How can I erase numbers before my main order ID ?
    I change order ID to 15 and did set prefix but after changing my order Id is : ABX-00000015
    I want order ID like this : ABX-15

    • Josh Hartman says

      It doesn’t look like that’s possible. After further testing on 1.9.1.0 I can confirm that the method in this article doesn’t allow you to use an increment id with less than 8 numeric digits. There is an extension that does what you want: Custom Order Number Pro