Heya,
I’ve been experimenting with IP and I’ve done a lot of customization in the process (custom fields, templates, etc). Now I want to transfer everything to a live environment, but I want every invoice/quote/client and accosiated records deleted and all the numbering to start from 1 again. Is there a SQL script that will handle everything by itself?
Thanks!
There is no SQL script.
I would suggest deleting all records in the payments, clients, invoices, invoice_*, quotes and quote_* tables.
Thank you Kovah for the prompt reply 
This worked well but let me add one more thing… To reset the internal invoice numbering from the beginning (i.e. invoice_id = invoice_number), I also reset the AUTO_INCREMENT of the ip_invoices table. I suppose this needs to be done to other tables.
Case closed. Cheers! 
Oh i forgot this: you also have to set the “next ID” in the invoice groups to zero. (Also in database)
Reviving this topic to add something useful…
Since I do all sorts of testing I need an easy way to RESET the system, without touching settings, languages, clients and products. So I created the following SQL script that will DELETE all invoices, quotes, payments and will reset the numbering of all records to 1.
Be very careful with it and have fun!
TRUNCATE ip_invoices;
TRUNCATE ip_invoice_amounts;
TRUNCATE ip_invoice_custom;
TRUNCATE ip_invoice_items;
TRUNCATE ip_invoice_item_amounts;
TRUNCATE ip_invoice_tax_rates;
TRUNCATE ip_payments;
TRUNCATE ip_payment_custom;
TRUNCATE ip_quotes;
TRUNCATE ip_quote_amounts;
TRUNCATE ip_quote_custom;
TRUNCATE ip_quote_items;
TRUNCATE ip_quote_item_amounts;
TRUNCATE ip_quote_tax_rates;
ALTER TABLE ip_invoices AUTO_INCREMENT = 1;
ALTER TABLE ip_invoice_amounts AUTO_INCREMENT = 1;
ALTER TABLE ip_invoice_custom AUTO_INCREMENT = 1;
ALTER TABLE ip_invoice_items AUTO_INCREMENT = 1;
ALTER TABLE ip_invoice_item_amounts AUTO_INCREMENT = 1;
ALTER TABLE ip_invoice_tax_rates AUTO_INCREMENT = 1;
ALTER TABLE ip_payments AUTO_INCREMENT = 1;
ALTER TABLE ip_payment_custom AUTO_INCREMENT = 1;
ALTER TABLE ip_quotes AUTO_INCREMENT = 1;
ALTER TABLE ip_quote_amounts AUTO_INCREMENT = 1;
ALTER TABLE ip_quote_custom AUTO_INCREMENT = 1;
ALTER TABLE ip_quote_items AUTO_INCREMENT = 1;
ALTER TABLE ip_quote_item_amounts AUTO_INCREMENT = 1;
ALTER TABLE ip_quote_tax_rates AUTO_INCREMENT = 1;
UPDATE ip_invoice_groups SET invoice_group_next_id = 1 WHERE invoice_group_id = 3;
UPDATE ip_invoice_groups SET invoice_group_next_id = 1 WHERE invoice_group_id = 4;What if you want reset everything to scratch?
If you want to reset EVERYTHING, I suppose you could DROP all the tables in the database (with phpmyadmin for example). Next time IP connects to the DB it will recreate the tables as needed.
P.S. Drop the tables only, not delete the whole database.
Just broke it, let the learning curve commence!
So dropped all the tables, every single one…then this happened.
A Database Error Occurred
Error Number: 1146Table ‘…ip_settings’ doesn’t existSELECT *
FROM (ip_settings)Filename: …/modules/settings/models/mdl_settings.phpLine Number: 59
Needed to update to 1.4.8 so uploading as I type.
Then will I reset the setup.php? Should that then allow the db rebuild?
Sorry for the statements and questions mix…just hoping this can help others.
To correct the erro I made should I only have DROPPED certain tables?
Forgot to mention that after you drop all the tables you need to visit the URL/setup URL to recreate the tables as needed.
Update. Dropped all tables, ran setup to recreate tables then finally re-import all data as per templates…voila!
Hi,
in my country by tax law we have to follow strict numbering in invoces. Every year it has to be started from number “1” following a letter and one more number. This is the first invoice number in year 2017:
1-P-1.
First “1” is the id
Second letter P which is an identifier of a “sellers place” - seller can have more than one place. Letter P is self picked, it can be any letter.
Third number “1” is the identifier of a cache desk.
So 1-P-1 defindes first invoce in my place “P” on cache desk “!”. Only first number will change, other two are fixed.
So, second invoice will have 2-P-1, third invoice 3-P-1 and so on. At the start of year 2018 this has to be again 1-P-1 and cyclus for 2018 year starts again.
Please advise how to configure invoice numbering? Is my only option to delete everyhing and start from scratch every year?
This invoice numbering is not possible with InvoicePlane as all invoices must have an unique invoice number. If you reset the invoice number each year it must contain a yearly identifier. For example it would be 1-P-1 (2017).
Thank you Kovah 
Yea, that crossed my mind but by tax law there should be no prefixes or sufixes (rigid country). BUT I will do it like this then, this will be my number:
{{{year}}}/INVOICE {{{id}}}-P-1
This way they can have their numbering but I will keep it uniqe. Is the space between INVOICE and { allowed and where there be any problems? And I just need to set number to 1 in Invoice gruoups when new year starts, I do not need to do something directly in DB, clean anything or similar
By the way: you may not use slashes (/ or ) in your invoice numbers. May lead to problems.
Back to topic:
If you have to send your invoices as PDFs to the officials you may alter the invoice numbers in the templates.
So 2017 INVOICE 1-P-1 may become 1-P-1.
Example:
<?php echo trans('invoice') . ' ' . preg_replace('/([0-9].*\sINVOICE\s)/i','', $invoice->invoice_number); ?>
This would automatically delete 2017 INVOICE from the invoice number where the year is dynamic.
Cool, gonna try this! But spaces are ok in identifier formatting?
Yes, just slashes should not be used