• start over
  • who we are
  • what we offer
  • what we've done
  • what we say

Elias

2008
16
Nov

Magento eCommerce: How To Reset All Test Order Information and Set Unique Prefix For Orders, Invoices, Shipments, and Credit Memos

About: How To Reset Test Orders, Dashboard Information, and Set Unique Starting Values for Orders, Invoices, Shipments, and Credit Memos

Who’s Interested: Informative to the technical gurus
What: Plug ‘n Play Database Script that runs queries to customize after store is installed and product(s) have been created
Magento Version Relevence: 1.1.6
Magento Store Setup: Single-Store Setup
 

A client of ours went in to test products, orders, invoices, etc. to get the feel for the overall “purchase order to shipment” process from start to finish. After creating many test users, orders, etc. - they now desire to have everything reset back to “0″ (zero).

 

Not only that, but they also want to separate the prefixes for the following items just for ease of dissimilarity when using the Magento ADMIN tools:

+ Orders (set prefix to begin with “1″)
+ Invoices (set prefix to begin with “2″)
+ Shipments (set prefix to begin with “3″)
+ Credit Memo’s (set prefix to begin with “4″) 

 

Thus, we have the following script (and a special thanks to Tomislav at Inchoo for getting us started!)

 

Instructions:

  1. Find “YOUR_DB_NAME” down below and change to match your database name (though leave the ticks “)
  2. Copy and paste code into SQL manager (PhpMyAdmin or similar) and then run the query
  3. Feel free to Create test order, invoice, shipment, and credit memo’s via the ADMIN (easier than going through order process on frontend)
  4. Once you like the changes you see, go back and run the same SQL query

    5. …the following will now be in effect:
    + All data pertaining to orders and search terms (Dashboard) are reset to 0 (zero)
    + Order number prefix with 0, Invoice number prefix with 2, Shipment number prefix with 3, Credit Memo number prefix with 4

     

Please feel free to copy and paste into your PhpMyAdmin


-- Reset Magento TEST Data

SET FOREIGN_KEY_CHECKS=0;

-- reset dashboard search queries
TRUNCATE `catalogsearch_query`;
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;

-- reset sales order info
TRUNCATE `sales_order`;
TRUNCATE `sales_order_datetime`;
TRUNCATE `sales_order_decimal`;
TRUNCATE `sales_order_entity`;
TRUNCATE `sales_order_entity_datetime`;
TRUNCATE `sales_order_entity_decimal`;
TRUNCATE `sales_order_entity_int`;
TRUNCATE `sales_order_entity_text`;
TRUNCATE `sales_order_entity_varchar`;
TRUNCATE `sales_order_int`;
TRUNCATE `sales_order_text`;
TRUNCATE `sales_order_varchar`;
TRUNCATE `sales_flat_quote`;
TRUNCATE `sales_flat_quote_address`;
TRUNCATE `sales_flat_quote_address_item`;
TRUNCATE `sales_flat_quote_item`;
TRUNCATE `sales_flat_quote_item_option`;
TRUNCATE `sales_flat_order_item`;
TRUNCATE `sendfriend_log`;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `wishlist`;
TRUNCATE `log_quote`;
TRUNCATE `report_event`;

ALTER TABLE `sales_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_datetime` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_decimal` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_int` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_text` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_varchar` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;
ALTER TABLE `tag` AUTO_INCREMENT=1;
ALTER TABLE `tag_relation` AUTO_INCREMENT=1;
ALTER TABLE `tag_summary` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;

-- Reset all ID counters
TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

-- set appropriate prefixes for orders, invoices, shipments, credit memos
INSERT INTO  `YOUR_DB_NAME`.`eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES ('1',  '11',  '1',  '1',  '000000000');
update `eav_entity_store` set `increment_prefix`= 1 where `entity_type_id`='4' and `store_id`='1';
update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='4' and `store_id`='1';

INSERT INTO  `YOUR_DB_NAME`.`eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES ('2',  '16',  '1',  '2',  '000000000');
update `eav_entity_store` set `increment_prefix`= 2 where `entity_type_id`='18' and `store_id`='1';
update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='18' and `store_id`='1';

INSERT INTO  `YOUR_DB_NAME`.`eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES ('3',  '19',  '1',  '3',  '000000000');
update `eav_entity_store` set `increment_prefix`= 3 where `entity_type_id`='24' and `store_id`='1';
update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='24' and `store_id`='1';

INSERT INTO  `YOUR_DB_NAME`.`eav_entity_store` (`entity_store_id` ,`entity_type_id` ,`store_id` ,`increment_prefix` ,`increment_last_id`) VALUES ('4',  '23',  '1',  '4',  '000000000');
update `eav_entity_store` set `increment_prefix`= 4 where `entity_type_id`='28' and `store_id`='1';
update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='28' and `store_id`='1';

 

For SQL Script download: Magento DB Modifications

Let us know how you like it!

Thanks,
Lee Taylor

Elias

69 Comments »

  • Eric Clark says:

    November 16th, 2008 at 8:24 pm

    Great post, Lee. I’m sure many will find this useful in the Magento community!

  • Marty says:

    November 17th, 2008 at 11:49 am

    Thanks for the script Lee. I found a typo in your script.

    After you “TRUNCATE `eav_entity_store`; ” you are doing an update on that table based on an ‘entity_type_id’ that does not exist. The last eight update statements do nothing.

    Also, it should be noted that this script assumes a single store setup. If you have multiple stores you would need to add additional insert statements with proper store_id.

    Thanks again for the script.

  • Joy says:

    November 17th, 2008 at 12:12 pm

    Why not use letters instead of numbers for the various prefixes? The table is set up to be a varchar already.

  • Marty says:

    November 17th, 2008 at 2:14 pm

    One more thing. I’ve been messing around in that part of the system today and I think you’ll definitely want to remove the “TRUNCATE `eav_entity_store`;” statement. The reason is; that table also holds counters for other parts of the system, such as customer number sequence. If you truncate it you may end up breaking other parts of Magento. Probably better to do updates only on that table.

  • timothyleetaylor says:

    November 17th, 2008 at 4:18 pm

    Hi @Marty,

    In reference to your first post: you are correct in seeing that this is assumed for a single-store Magento setup. We could go in and reconfigure some of the values to be modified for multiple stores.

    Also, with the TRUNCATE command, we don’t actually delete the table, but all the information in it. We then, in the last 8 commands (check lines 79, 83, 87, 91) create values inside that table to be modified. The first statement clears the table, and the second set of statements inserts the structure to be modified.

    As for using the TRUNCATE on that table, you may be correct in that it may be more safe to simply update on that table. What errors have you encountered by removing the customer number sequence?

    Thanks for your thoughts!
    Lee

  • timothyleetaylor says:

    November 17th, 2008 at 4:19 pm

    @Joy,

    The reason we used numbers was simply by client’s request. You can go ahead and insert letters as the datatype is varchar-appropriate.

    Thanks for mentioning the possibility!
    - Lee

  • Jesse says:

    November 17th, 2008 at 4:33 pm

    Thanks for the script Lee. In regard to the posts concerned with Truncating the table, have you experienced any problems yourself with live sites after having used this script? We plan on launching a site tomorrow and have yet to give your script a go. Would you still advise using it as is? Just hoping we won’t run into any problems along the way..

    Many thanks.

  • timothyleetaylor says:

    November 17th, 2008 at 5:54 pm

    Hi Jesse,

    This script should work correctly for you, as we’ve tested it on a couple sites thus far, though recently. Do you have anyone familiar with SQL code? If so, let them walk you through it and make you feel more comfortable with it.

    As always when modifying database data, I would be sure to create a backup via Magento tools in the ADMIN or via your SQL interface such as PhpMyAdmin.

    In regards to the concerns posted, this only involves using this script after the site has been acquired various customer accounts, thus possibly effecting the numbering sequence.

    So if you do have customer accounts you’d like to keep, it may be wise to wait for an updated script that I’ll try and get onto as time allows. Otherwise, please feel free to contact us with any needs you may have regarding web development, design, or marketing - and we’d be glad to assist you!

    Thanks,
    Lee

  • Jesse says:

    November 17th, 2008 at 7:01 pm

    Thanks for the quick reply. I don’t need to keep any accounts so I’ll give it a shot tomorrow.

  • Yaani says:

    November 18th, 2008 at 2:56 pm

    I tried this & it seemed to delete all the test orders but I also got this error

    SQL query:

    INSERT INTO `mystoredatabase`.`eav_entity_store` (
    `entity_store_id` ,
    `entity_type_id` ,
    `store_id` ,
    `increment_prefix` ,
    `increment_last_id`
    )
    VALUES (
    ‘3′, ‘19′, ‘1′, ‘3′, ‘000000000′
    );

    MySQL said: Documentation
    #1452 - Cannot add or update a child row: a foreign key constraint fails (`modsnroses/eav_entity_store`, CONSTRAINT `FK_eav_entity_store_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE)

    Can you tell me if there is anything else I need to do in regard to this error?

    Thanks!

  • Yaani says:

    November 18th, 2008 at 9:26 pm

    I think I found the answer to my error problem above here
    http://www.magentocommerce.com/wiki/restoring_a_backup_of_a_magento_database

    Thanks!

  • Yaani says:

    November 18th, 2008 at 9:39 pm

    No that didn’t fix the following error

    SQL query:

    INSERT INTO `modsnroses`.`eav_entity_store` (
    `entity_store_id` ,
    `entity_type_id` ,
    `store_id` ,
    `increment_prefix` ,
    `increment_last_id`
    )
    VALUES (
    ‘3′, ‘19′, ‘1′, ‘3′, ‘000000000′
    );

    MySQL said: Documentation
    #1452 - Cannot add or update a child row: a foreign key constraint fails (`modsnroses/eav_entity_store`, CONSTRAINT `FK_eav_entity_store_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE)

    Should I just try to create those last two table entries?

    It seems the first two tables are created
    1 11 1 1 000000000
    2 16 1 2 000000000

  • timothyleetaylor says:

    November 19th, 2008 at 2:55 pm

    Hi @Yaani,

    Thanks for your interest on this SQL script. Can you verify that you’re using Magento 1.1.6? The db structure is necessary for this script to be applicable. If you’re using a lower version, feel free to go into `eav_entity_type` and see what values are for orders, invoices, shipments, and credit memo’s. They may have a different value for `entity_type_id`.

    I’m thinking an indifference there may be what’s causing that kind of error.

    Thanks!
    Lee

  • Yaani says:

    November 19th, 2008 at 8:02 pm

    It’s Magento 1.1.6

    I went into the database & looked at the eav_intity_type table & it has two entries that contain the following data

    1 11 1 1 000000000
    2 16 1 2 000000000

  • yaani says:

    November 21st, 2008 at 10:28 am

    I was able to get rid of the mysql error by moving the line of code “SET FOREIGN_KEY_CHECKS=1;” to the end of the sql query & everything worked great! Thanks!

  • Kev says:

    November 23rd, 2008 at 11:06 pm

    Thanks! Works great for 1.1.6! I’m about to upgrade though, so I need to know if anyone has tried this with 1.1.7?

  • Matt Albiniak says:

    November 25th, 2008 at 9:21 am

    Great work, Lee. Thanks for sharing this with the community.

  • David says:

    November 26th, 2008 at 8:29 am

    I’m completely new to databases I open phpmyadmin and its like looking at the cockpit of a 747, but clearing out my test orders is something I really want to do. When I select my database in phpMyadmin and then select query paste the code with my database name in the SQL query on database XXXXX: it brings up the message: “You have to choose at least one column to display”. So, I’m lost, what am I missing?

  • Eric Clark says:

    November 26th, 2008 at 8:40 am

    Hi David, would you mind posting the exact error message from phpmyadmin? Also make sure you find/replace all instances of YOUR_DB_NAME in that query.

  • David says:

    November 26th, 2008 at 9:12 am

    Yes, I have replaced all instances of “YOUR_DB_NAME” and the error message is:
    “You have to choose at least one column to display”

    I know this is operator error. Here is a little more data, The screen I’m looking at is the Query tab of my database. There are 3 areas to work in. There is a box up top with the pulldowns field, and sort. A checkbox for show. A set of fields titled Criteria:. More checkboxes for ins:,Del:, And:, and Or: etc. The second box is “Use Tables”, I assume that I select all of them. The third box is “SQL query on database XXXXX: this is where I post the code.

    Thanks for the help

  • David says:

    November 26th, 2008 at 9:20 am

    A little more progress, I was selecting the wrong tab. It should have been the SQL tab. But its not over yet. now the error is:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘magento’ at line 1

  • timothyleetaylor says:

    November 30th, 2008 at 12:51 am

    Hi @David,

    I would recommend you start by Googling “How to run SQL query in PHPMyAdmin” or something similar. Then come back once you get the basics down.

    This is the best initial step for both of us before trying to troubleshoot with you I believe.

    Cheers!
    Lee

  • Boaz says:

    December 7th, 2008 at 2:51 pm

    Hi Lee,

    The script is running (after removing the SET FOREIGN_KEY_CHECKS=1) and orders are cleared but order and invoices number don’t change: they all start with 4 (the first is #400000001).

    Any ideas?

    Boaz

  • timothyleetaylor says:

    December 7th, 2008 at 5:52 pm

    Hi @Boaz,

    No problem, I’d be glad to help.

    If you’d like, try to go through and run the last several lines starting with the “INSERT” statements. They are designed to create (if there aren’t any remaining values) and modify to increment the prefixes for orders, invoice, shipments, and credit memos.

    To check manually, you can go into your db and access the `eav_entity_store` table to view. Please make sure there are incrementing values for`entity_store_id` and make sure the `entity_type_id` values are correct for your version of Magento. Lastly, make sure the prefixes are correctly stored in the `increment_prefix` column.

    Though this screenshot resembles version 1.1.6, please feel free to view this screenshot as an example.

    Let us know if you have any more problems!

    Thanks, and all the best,
    Lee

  • Boaz says:

    December 7th, 2008 at 6:20 pm

    Lee,

    Thanks for your quick replay.

    After posting my question I want and check the eav_entity_store table. I found the newly inserted records there and also another record with store_id=4 and increment_last_id = 400000001.
    I’m not sure why my store_id was 4, maybe it’s because I played around with creating and deleting stores, but anyway I tried deleting this record and changing the store_id to 4 in the script and rerun it, but it fails too. Magento just recreated an additional record with entity_type=4, store_id=4 and increment_last_id = 400000001.
    At this point I edited that record in the DB, change the increment_last_id to 000000001 and the increment_prefix to ” “. With this change I managed to get the order id starting with 0. Magento also created a record for the invoices with entity_type = 18 which I also changed manually to control the invoices numeration.

    So… I managed to manually make it work but I don’t really understand how the script was suppose to make it work. Just hope that my changes are not causing any internal Magento problems (so far it seems OK).

    Boaz

  • timothyleetaylor says:

    December 7th, 2008 at 6:30 pm

    Hi Boaz,

    It’s important to note here that the script is meant only for a single-store setup (which you can verify via the values being “1″ in the `store_id` column). Thus, the script will only insert values for your store associated with the `store_id` value of “1″.

    If you need help reading the SQL code, try this as a helpful resource: http://www.w3schools.com/sql/default.asp

    The script works correctly with what you performed manually, but for a single-store setup. My apologies if I hadn’t made that clear enough for you in this post. I’ll review and add notification for that up above.

    Thanks,
    Lee

  • Boaz says:

    December 7th, 2008 at 6:46 pm

    Lee,

    Thanks. I realized that I need to match the store_id the the id that I have (it’s a single store setup but I had several stores there before as a test which I deleted), but for some reason it didn’t work as expected even after changing the store_id in the script.
    SQL syntax is very clear. I don’t fully understand the logic: why do you create entity_types with new entity_ids (11,16,19, and 23) and then try to update record with entity_ids that doesn’t exists (deleted by the previous TRUNCATE `eav_entity_store` statement)? I’m sure that there is some logic here, I just don’t know what it is.

    In any case, this script was a great help. I would never dare to touch those tables without seeing how it’s done in this example.

    Thanks again,
    Boaz

  • timothyleetaylor says:

    December 7th, 2008 at 6:57 pm

    Hi Boaz,

    So glad you found this as beneficial towards your Magento efforts. We’ll continue sharing with the community findings we see as relevant as we progress.

    As for this specific script, we truncate to reset, prior to re-creating the separate entries for the necessary db values that in the `eav_entity_store` records. The prefixes are associated to a specific values that Magento uses set for `entity_type_id`. Each `entity_type_id` value relates to orders, invoices, shipments, and credit memo’s respectively.

    Thanks again for the dialogue and feedback!
    Lee

  • Shreehari says:

    December 16th, 2008 at 4:49 am

    Hi,
    Is is possible to refresh (reset) a part of the database.

  • timothyleetaylor says:

    December 16th, 2008 at 3:06 pm

    Hi @Shreehari,

    I’m not sure if I know what you mean exactly. Feel free to email us via the contact form below if you’re needing some consulting. Otherwise, try to be more specific with what you intend to happen, and we’ll do our best to help.

    Thanks!
    Lee

  • benz says:

    December 18th, 2008 at 3:11 pm

    Hi all,
    apologies if i have skimmed and missed some info -
    What I am wondering is if this will clear ALL of the orders in my Admin?

    I have several test orders (cancelled), and now several real orders - I would like to remove the tests whilst keeping any new orders that have come in..

    Will this script only remove cancelled orders?
    thanks in advance, and great job!
    Ben

  • timothyleetaylor says:

    December 18th, 2008 at 5:40 pm

    Hi @benz,

    Glad to hear from you! No worries with asking the question. This script actually removes all orders in the ADMIN. It’s definitely possible to create a script that removes test orders - though we’d probably want to work with you on that specifically together. Many variations in store setup/management would probably create the need for a specific-script relating to your store setup, rather than an easy “plug ‘n play” script like the one above.

    Should you desire this, please feel free to contact us via the contact form below and we’d be glad to assist you towards a solution. It shouldn’t take too much work to get something like this together for you.

    All the best, and feel free to let us know how you proceed!

    Thanks,
    Lee

  • david says:

    December 24th, 2008 at 11:21 am

    Beautiful! Absolut beauty!

    WORKS FOR 1.1.8

    Thanks for shearing!
    D

  • david says:

    December 24th, 2008 at 11:25 am

    Oh.. and… it cleared all the undesired data from all my stores and websites.

    My configuration:

    Website1 | store | storeview (ES)
    Website1 | store | storeview (EN)

    Website2 | store | storeview (ES)
    Website2 | store | storeview (EN)

    I just didn’t understand how to change the prefix from numbers to characters… I would like to do that.

  • Boaz says:

    January 11th, 2009 at 11:11 am

    Hi Lee,
    I wonder if you have any idea how to solve the following:
    My store has 2 store views: English and Spanish. Magento seem to use different store_id value for each store view. This means that there is a separate set of sequences used for the order ids and invoice ids of each view.
    I don’t care so much about the order id but the separate invoice ids are a big problem. This is one store with different views and according to law I must have sequential invoice numbers for all transactions.
    I’m looking for a way to impose all the invoices from all the store views to use one sequence.
    Any ideas will be appreciated.

    Boaz

  • Don says:

    January 14th, 2009 at 9:03 pm

    Thank you so much. Worked perfect right off the bat. Reset the data and boom. What I am going to do now is change the bottom part where it resets all the order numbers and stuff to “00000000″ and just put some arbitrary numbers in there. I just don’t like all of those 0’s.

    Thanks again!!!!

  • timothyleetaylor says:

    January 16th, 2009 at 12:03 am

    Way to go Don!

    We’re glad this was helpful for you. Rock on with Magento, and keep taking advantage of what it provides.

    Thanks!
    Lee

  • timothyleetaylor says:

    January 16th, 2009 at 12:05 am

    Hey @Boaz, what you mention here will take a bit more customization than what we could discuss via blog comments.

    Should you desire to hire someone to make this functionality present, feel free to contact us via our contact form below.

    All the best with your endeavors!
    Lee

  • Boaz says:

    January 16th, 2009 at 1:56 am

    Lee,

    Thanks for the offer. I may use it for other things but for this specific problem I found a simple solution that seems to be working perfectly:

    In eav_entity_store added two entries:
    For orders: 
    entity_type: 11
    store_id: 0
    increment_prefix: 0
    increment_last_id: 010000000 (of any other start id that you choose)

    For invoices: 
    entity_type: 16
    store_id: 0
    increment_prefix: 0
    increment_last_id: 000000001 (of any other start id that you choose)

    additionally: changed the values of “increment_per_store” from 1 to 0 for entity_type_code 11 and 16 (order and invoice) in eav_entity_type.

    Credits to Linz (http://www.magentocommerce.com/boards/viewthread/5893/P15/)

    Boaz

  • Stewart Goodwin says:

    January 17th, 2009 at 2:09 pm

    Does this work for version 1.2.0.2?

  • Charles says:

    January 21st, 2009 at 5:03 am

    Ditto!

    Does this work for version 1.2.0.2?

  • iTeam - Magento design says:

    January 22nd, 2009 at 3:44 pm

    Stewart - tried running it on Magento 1.2.0.2 - and while it did remove the pipeline results the orders’ still there:

    I did get this error in phpmyadmin after running the script:

    – reset dashboard search queries
    TRUNCATE `catalogsearch_query` ;

    MySQL returned: Documentation
    #1146 - Table ‘mydatabase_id.catalogsearch_query’ doesn’t exist

    Ideas?

  • Stewart Goodwin says:

    January 24th, 2009 at 1:21 pm

    I have been unsuccesful in getting it to work on 1.2.0.2. We are going live Feb 1. Anybody have any success removing test orders?

  • timothyleetaylor says:

    January 24th, 2009 at 11:54 pm

    Hi Stewart,

    We have not updated this for version 1.2.0.2 yet. I’m interested in what errors you’re getting? Would you mind posting them?

    Also, be sure to check and make sure the following fields contain the proper updated values (values can be seen via phpMyAdmin or similar software):

    `entity_store_id` ,
    `entity_type_id` ,
    `store_id` ,
    `increment_prefix` ,
    `increment_last_id`

    Thanks,
    Lee

  • Mads Nygaard Pedersen says:

    January 26th, 2009 at 5:08 am

    Thanks Lee - will check the 1.2.0.2 version out - meanwhile the 1.2.0.3 has been released - hopefully not obstacles there ;-)

  • Stewart says:

    January 26th, 2009 at 10:30 am

    To be honest I am a little nervous running the script knowing it won’t work. The site is set to go live Friday and any setbacks would definitely hurt time wise… Any suggestions for getting rid of the test transactions or do you have any time line for when an updated script will be ready? Thanks!

  • timothyleetaylor says:

    January 28th, 2009 at 1:20 am

    Hi @Stewart,

    I completely understand your hesitation here. It can get a bit tricky/sensitive when working with the Magento database.

    We are available for hire should you desire to get this job done professionally with the best Magento practices!

    Unfortunately outside of that, we do not yet have a planned date for the new compatibility release. We’ve just been too busy at this point. I apologize for the inconvenience.

    Thanks, and let us know if you need assistance with this,
    Lee

  • Stewart says:

    January 28th, 2009 at 3:15 pm

    well… I figured I would give it a shot before subbing it out. The script works perfectly on 1.2.0.3. Something to note on the errors above. Most likely you got the “does not exist” errors because you have a table prefix (which most hosts use for databases). Once I added the db prefix to all the tables in the script everything worked great.

    One question though… How do I delete customer downloadable link info? For example when I log into my customer account it still shows downloadable product links from old orders [the links don't work obviously]?

  • Stewart says:

    January 28th, 2009 at 3:20 pm

    …found it! Just delete the unneeded entries from downloadable_link_purchased_item and downloadable_link_purchased

    hope this helps someone.

  • Natalie says:

    February 11th, 2009 at 10:09 pm

    Hi

    Thanks Lee for the script

    It works for me in ver 1.2.1

    I added four extra tables to be truncated:
    `sales_flat_quote_payment`;
    `sales_flat_quote_shipping_rate`;
    `sales_order_tax`;
    `wishlist_item`;

    Let me know if you think there’s any reason not to empty these tables.

    Also I manually updated the eav_entity_store table as I my entity_type_id field had different values from the script. I also wanted to keep the customer sequence number.

    I tried using a letter for the increment_prefix, which seemed to work in most places but failed in the list of orders / invoices etc in the admin backend, so I stuck to numeric prefixes.

  • Rune says:

    February 12th, 2009 at 3:39 am

    For some reason this causes my payment module from Quickpay to crash. When placing my order I get the error msg: “There was an error processing your order. Please contact us or try again later.”

    I’ve enabled Magento logging and in exception.log i get:

    http://pastebin.com/m33e9c1a

    Once I restore the DB from before truncating - everything works fine. But I have to truncate date before going live.
    I really hope to get some help. Thank you :)

  • Rune says:

    February 12th, 2009 at 5:22 am

    I solved the issue. Since I truncated the Magento order tables the order id was starting from the beginning. When submitting the order to Quickpay they refused because the order id was already in their system - due to my previous testing.

    Hope this helps someone :)

  • timothyleetaylor says:

    February 12th, 2009 at 5:23 pm

    Hey @Rune,

    I apologize for not getting a chance to get back to you before you found the solution. But it looks like you have been able to sort things out yourself! Great work, and thanks for reporting here to let everyone in on the findings.

    Keep us updated with how things are going with you!

    Thanks,
    Lee

  • timothyleetaylor says:

    February 12th, 2009 at 5:26 pm

    Hi @Natalie,

    Thank you as well for your input on the discussion here. I’m curious as to what failed for you, and what errors you were getting when trying to run the script.

    I haven’t verified that the script works with 1.2 yet, but hope to soon.

    And as for those tables you mentioned, I don’t see any issue in truncating them unless you have a need for maintaining that information relating to current customers you have. At first glance, I think you should be fine - though what is your situation regarding maintaining current customer data?

    Thanks!

  • natalie says:

    February 12th, 2009 at 5:45 pm

    Hi @Lee

    No errors when I ran the “truncate” part of the script

    I only added the extra tables to truncate as it seemed logical that they also be emptied out

    I did the eav_entity_store table by hand as I could see my entity_type_id fields were different and it was easier to update manually than edit the script.

    I have no need to maintain current customer data - re orders etc, but wanted to retain the customers already entered. They are the client ones and it’s easier for them to keep the same customers for their testing at the moment

    Thanks again

  • Wijzigen ordernummer en factuurnummer - Magento webshop | Nederlands Magento forum voor webwinkels en ontwikkelaars says:

    February 21st, 2009 at 2:22 am

    [...] ik kwam de volgende tutorial tegen: Magento eCommerce: How To Reset All Test Order Information and Set Unique Prefix For Orders, Invoice… (niet [...]

  • David G. says:

    February 23rd, 2009 at 6:55 pm

    You may all be interested in this extension to Delete Orders:
    http://www.magentocommerce.com/extension/873/delete-orders

    Extension KEY: magento-community/BoutikCircus_DeleteOrders-beta

    (it works!)

  • Condoms Girl says:

    February 25th, 2009 at 7:51 am

    Thanks for the post :-) very useful. I’m going to go away and have a look at my magento site, http://www.condomgirl.co.uk, and see if I can generate random invoice numbers rather than consecutive.

  • Mads Nygaard Pedersen says:

    March 8th, 2009 at 3:54 am

    David G. - thanks for the tip :-)

    /Mads

  • david says:

    March 12th, 2009 at 7:26 am

    Hello. I’m experiencing serious problems in orders and since I used this script before going live I wonder if it has anything to do with it or if any of you experienced these errors.

    I have PayPal standard and Saved CC payment methods available.
    When I make an order with a credit card and a real address the new order has the following problems:

    - Payment method shows wrongly as a PayPal Standard payment instead of Credit Card purchase.
    - The shipping and billing addresses are correct in the e-mail of a new order notification but they are wrong in the backend (it shows a random address saved somewhere in the DB from when I was testing).
    - No credit card information in backend!! The credit card info is shown in the new order e-mail but not in the backend. It shows as a paypal standard payment and its not.

    I tried disabling paypal and test gave same wrong result.

    - Running Magento 1.2.1.2

    The site is live… Is this something you’ve seen before??

  • South Lake Tahoe Web Design Blog - RogueEasyweb Blog » Magento Tips, Tricks and Hacks says:

    April 13th, 2009 at 12:49 pm

    [...] Instead of running through all this I’m just going to link you to the site that helped me out, eliasenteractive.com [...]

  • Jay Benfield says:

    April 17th, 2009 at 4:45 pm

    Just what I was looking for - thanks!

  • Shinobi says:

    April 21st, 2009 at 9:55 am

    Magento noob here. This is great. Just what I’m looking for. Question though - is it possible with this script to not just prefix orders, invoices, etc. but to start the numbering with something like say 595080398? By changing the ‘last_increment_id’ to whatever number and then it increments by 1 from there? or whatever increment amount I want?

  • Shinobi says:

    April 21st, 2009 at 11:29 am

    OK, I gave it a shot on a local instance of the store I’m developing. Magento v1.2.1.1 - seems to work fine. Deleted all my test orders and reset everything.

    However, if you notice in the script where it updates Orders, Invoices, etc., the Update statement uses the Entity IDs 11, 16,19, and 23 while the Insert Statements reference the IDs 4, 18, 24 and 28. Was that just a typo or a change from versions or something? I made them match and now my orders have been reset, are prefixed with the numbers and start at the new number I set .

    Thank you very much. My store is almost ready and I needed to clean things up before going live and my client asked to change the starting order number. Perfect!

  • Mosses Akizian says:

    April 21st, 2009 at 3:03 pm

    Tried it in v. 1.3.1 and seems to be working fine for checkout with one store. Was also able to use alphanumeric prefix.

    Tested for multi-store by copying last 4 main commands. Worked fine at backend via phpMyAdmin. Don’t forget to assign 2nd store_id and increment per entity_store_id. Didn’t test for checkout though. Anyone with success, please report.

  • Gabriel Zamprogna says:

    May 5th, 2009 at 7:41 pm

    I am experiencing a problem here: after I delete the test orders, I can’t create invoice for any new order! The system says “Order not longer exist”.
    It seems like something got wrong in my database, but I can’t find what…

    My version is 1.3.1 and I have only 1 day to complete this specific installation.

  • Remove all orders « Phi & Fashion, turned into a Magento blog..? says:

    May 21st, 2009 at 9:01 am

    [...] http://eliasinteractive.com/blog/magento-ecommerce-how-to-reset-all-test-order-information-and-set-u... [...]

  • Brady says:

    May 27th, 2009 at 5:13 pm

    Wow - what an excellent article!

    I’m trying to piece together a personal solution. I’m using Magento 1.3.1.1 and need to add a prefix to only Credit Memos.

    In my database, the Credit Memos are entry_type_id = 23. I change the increment_prefix to “R1″.

    Everything seems to go through fine, except when I look in the Admin > Sales > Refunds, the Refund Number simply shows up as 0, even though in Admin > Sales > Orders, then clicking on the Refund tab shows the correct refund number.

    Correct: http://img.skitch.com/20090528-pqei694783ahcqhkywdrcr34n4.jpg

    Incorrect: http://img.skitch.com/20090528-k1fthj41a26ihgswp4sdx8t6rn.jpg

  • Ratna says:

    June 28th, 2009 at 9:55 pm

    Thank you for all the info.

    The script worked splendidly in 1.3.1. You may want to add the following two tables as well -

    TRUNCATE `downloadable_link_purchased`;
    TRUNCATE `downloadable_link_purchased_item`;

Leave a comment

 
 


« Greg the Architect on Focus Elias: Making It Personal »