How can I Cancel & Delete 100,000+ Sales Invoices?

1

Hello Mates,
I just imported 100,000+ Sales Invoices and realized that I have made a mistake in data. Can you help me delete all records in this DocType?

I have tried the following bench console command method but seems it is not working:

for invoice in frappe.get_list(‘Sales Invoice’):
invoice = frappe.get_doc(‘Sales Invoice’, invoice.name)
invoice.cancel()
invoice.delete()

Thanks in advance for your help!

2

Perhaps try something like:

frappe.db.delete('Sales Invoice', {
    'creation': '<datetime>'
})

https://frappeframework.com/docs/user/en/api/database

2 Likes
3

Thanks for your help. How can I cancel the Document before deleting?

4

Maybe use the frappe.db.set_value(doctype, name, fieldname, value) method to change the value in Sales Invoice status to Cancelled. My use of the api has been limited. Make a back up before proceeding or clone the existing instance and use the duplicate to test with if possible.

1 Like
5

How did you import so many? the data import tool takes a long time and sometimes times out at around 1000 invoices…

6

Hi @satishvijayan, I have used Bench CLI to import large amount of data. Our 600,000+ journals took 24+ hours to import. I have also used tmux to run the process without keeping the session on.

You can also import using:
bench --site sitename data-import --doctype Item --file /path/to/file.csv

1 Like
7

Finally I did it using Bench Console

for invoice in frappe.get_list(‘Sales Invoice’):
invoice = frappe.get_doc(‘Sales Invoice’, invoice.name)
invoice.cancel()
invoice.delete()
frappe.db.commit()

Using tmux it took nearly 8 hours to complete the process.

2 Likes
8

I know this is 3 years old (& the bot reminded me of it too :laughing:)…
but if I wanted to do this on a specific range of POS & sales invoices, what would be the correct filtering syntax.

my use-case…I imported a large history of transactions, but I forgot that the VAT rate changed about halfway along, so I need to remove all the ones with the wrong VAT rate and then re-import them with the correct one

I could probably figure it out - but re-inventing the wheel when a person much smarter than I am (obviously I’m not smart, considering what I managed to do wrong) would be much safer & quicker

Thanks

9

Explanation: The commit makes it work because you need to end DB transaction periodically. If you don’t commit changes and wait for 1000s of cancellation to then finally issue a commit that will likely cause DB to crash and abort your transaction OR trigger framework’s max write per transaction validation.

This is one of few cases where commit is absolutely necessary :smile:

https://frappeframework.com/docs/user/en/api/database#database-transaction-model

2 Likes
10

OK - that part I will definitely do… is there a way to remove a specific date range only?

11

Is it that you need to select and replace a value that matches a pattern between or greater than a date(s) with something else or will the change result in a change to calculations in each transaction and have some accounting effect? Or is that something the procedure is going to correct?

12

Howdy @smino - thanks for the reply.
When I ran the original import I used the new VAT 15% for all transaction. I imported the sales WITH the matching payments, so all the old transactions are showing as short-paid
eg. Should have been

  • R100(cost) + R14(VAT14%) = R114 (total) → paid = R114(correct)

but came in as

  • R100(cost) + R15(VAT15%) = R115 (total) → paid = R114 (short by R1 which now shows as overdue)

I did consider doing an import of new adjustment data to fix them, but I have left it unfixed for so long I can’t remember what goes where in the export/import file. I can’t remember which columns are required/optional etc.

I figured I’d take the lazy man’s way and delete the transactions which fall before the change in VAT rate, and then rerun the sales import file which has far less columns to worry about.

I’m assuming (& hoping :pray:) that the delete/recreate will fix ALL of the above problems. AFAIK deleting a sales transaction will reverse out the stock position as well as the various accounting pieces - but I am very far from knowing what I’m doing i.t.o accounting? (I’m just a techie :pleading_face:)

13

I suspect that I could run a very small dataset test with <10 transactions and see what changes i.t.o stock and so on?

edit: in fact it was this thinking that necessitates even more of a need to filter the dataset on which this runs, hence the is there a way to remove a specific date range only? - although that would have been needed either way