How to change order increment prefix?

Question

I run a shop (Magento CE 1.6.2) with 4 different store-views, which have (for historical reasons) ID's 1, 3, 4 and 7. Order numbers have been looking like this 10000001, 30000001, 40000001, 70000001.

I have set-up a brand new install with Magento CE 1.9.1, but now the store-ID's are 1, 2, 3, 4...

How do I change this in the old situation?

I came across this post --> http://www.thevortexcode.com/how-to-change-order-prefix-and-default-value-of-ordershipmentinvoice-numbercredit-memo-in-magento/ explaining how to set/cahnge the eav_entity_store.increment_prefix

But this does not seem to work in 1.9...

Answer 1

Just edit the ID of the stores in the store_id column in the core_store database table. Because of the foreign keys, this will get updated in almost all of the other tables.

Additionally you should update:

• default_store_id column in core_store_group table

---

As commented, changing your actual database ID's would be a bit radical. Also you would still have to update the eav_entity_store table.

So ,you could also simply set a per store configuration for your increment ID's. You can do this in the eav_entity_store table:

• For store ID 1, set increment_prefix to 1
• For store ID 2, set increment_prefix to 3
• For store ID 3, set increment_prefix to 4
• For store ID 4, set increment_prefix to 7

Answer 2

Better use model wrappers rather than raw sql statements.

Example:

$productEntityType = Mage::getModel('eav/entity_type')
    ->loadByCode(Mage_Catalog_Model_Product::ENTITY);
$entityStoreConfig = Mage::getModel('eav/entity_store')
    ->loadByEntityStore($productEntityType->getId(), 0);
$entityStoreConfig->setEntityTypeId($productEntityType->getId())
    ->setStoreId(0)
    ->setIncrementPrefix($prefix)
    ->setIncrementLastId($lastId)
    ->save();

See:

• https://www.schmengler-se.de/en/2014/12/magento-tutorial-use-increment-models-generate-ids-or-skus/
• https://www.atwix.com/magento/custom-order-and-customer-numbers/

Answer 3

I had to do this many times for different reasons and this is what I do:

This query will show you the increment id only for entity_type_code = order

SELECT * FROM  eav_entity_store join eav_entity_type ON eav_entity_type.entity_type_id = eav_entity_store.entity_type_id
WHERE eav_entity_type.entity_type_code='order';

Once you located the rows you would like to update you can change them like this:

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.eav_entity_store_id = {row_id_here};

Notice that the value for increment_last_id already contains the increment_prefix, so I usually removed the old increment_prefix from increment_last_id so the new orders increment id looks good.

If you have quotes with a reserved increment_order_id then is possible that you will still see orders with the old increment_id. Once you start generating new quotes after the update all should be fine.

Usually in my development environment I just set all the quotes to is_active = 0 after updating the increment_id just to have a fresh start.

For more details check here: http://www.warpconduit.net/2012/04/18/how-to-change-the-order-increment-id-and-prefix-in-magento/

Answer 4

I really try and avoid direct queries into a magento db. If possible, create a simple module that has an install or upgrade script which will do this for you. For example, you can use the below script to change the increment id and increment prefix:

<?php

    $this->startSetup();

    /*
     * Get the resource model
    */
    $resource = Mage::getSingleton('core/resource');

    /*
    * Retrieve the write connection
    */
    $writeConnection = $resource->getConnection('core_write');

   /*
    * Retrieve our table name
    */
    $table = $resource->getTableName('eav/entity_store');

    $table2=$resource->getTableName('eav/entity_type');


    $query = "UPDATE {$table}
    INNER JOIN {$table2} ON {$table2}.entity_type_id ={$table}.entity_type_id
    SET {$table}.increment_last_id='XXXXXXXXX'
    WHERE {$table2}.entity_type_code='order' AND eav_entity_store.store_id = 'Y'";

   /*
   * Execute the query
   */
   $writeConnection->query($query);

   $this->endSetup();

Where XXXXXXXXX is your increment id you want to start at and Y being the store id you want to do this for.

To change the actual prefix, use the query below:

        $query = "UPDATE {$table}
    INNER JOIN {$table2} ON {$table2}.entity_type_id ={$table}.entity_type_id
    SET {$table}.increment_prefix='X'
    WHERE {$table2}.entity_type_code='order' AND eav_entity_store.store_id = 'Y'";

Where X is the prefix you want to use and Y being the sotre id you want to do this for.