Sometimes you will be required to change the default increment ID’s
of orders, invoices, credit memos & shipments. To do this is
surprisingly simple, yet cannot be done via the backend Admin area
without an extension. Today I’ll show you how simple it is to change
these numbers with some simple SQL queries to run on your database.
The table in question is eav_entity_store. Which has the last used
increment id for each of the records we want to change. Knowing which is
which is relatively easy to find out when you know where to look. In
order to find out which is which you only need to take the
entity_type_id and look at the records contained within eav_entity_type.
From there you can spot which record corresponds to the records within
eav_entity_store.
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.
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).
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 order 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 order 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 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='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 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='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 order 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 order 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 order prefix, then replace Y
with the store ID of the store you want to modify. Run the query.
There you have it, it’s as easy as that! You can now easily update
order numbers, invoice numbers etc with these simple SQL queries.
Let me know in the comments if this has helped you........... Enjoy........Happy coding :)....
Thanks.
ReplyDeleteGreat blog.it helps me lot
I suggest using an extension, which can set up the new invoice number and modify the number of the next order at ease, you can check it out at: https://goo.gl/l1Y412 for more features.
ReplyDelete