2

I'm not 100% sure, if the next process is safe or not, so first of all, I ask...

In my company's CiviCRM are more than 3900 Contributions, which should be deleted. These Contributions can be listed as result of a report in CiviReport, but not as result from the SearchBuilder because the query is too difficult for SearchBuilder (with 'NOT IN' clauses etc.).

On the report's page, on the Developer tab I see the SQL query of the report, and I have access to the CiviCRM's database, so I can run this query. I can rewrite the SELECT query to a DELETE query.

Is it safe - after backuping the database - run the DELETE query to delete all unneeded Contributions from the civicrm_contribution table, or it causes database inconsistency?

Thank you!

  • 1
    HI Pál - will depend how the Contrib was added and if a payment or line items involved. if so there are many other table that could have linked data in which you would need to think through. you could look at this via the ERD doc.symbiotic.coop/dev/civicrm/latest/schema/index.html. Not sure what the gotcha is for you with SearchBuilder since i thought it did offer Not In but if not, have you considered the Include/Exclude custom search? We may have a query that clears out all the tables. If i can get hold of it quickly i will add as an answer – petednz - fuzion May 11 at 23:33
  • Hi @petednz-fuzion - the clauses are the following: Is deleted (the contact): not, Contribution OR soft credit: Contribution only, Contribution status: completed, and these are custom fields: Recipient: empty, Account No: NOT IN (and here are five values). What I cam't make from SearchBuilder is the empty field. All these contributions was imported, and nothing was involved. – Pál Rudan May 12 at 6:37
  • ... Sorry. All these contributions was imported, in the original import file are only the absolute minimum necessary fields are filled. – Pál Rudan May 12 at 6:44
  • ok. then you may be okay to wield the SQL axe :-) – petednz - fuzion May 12 at 9:08
  • I used :-) I have to delete rows from civicrm_contribution and civicrm_line_item tables. Thank you for the ERD, it's very useful. – Pál Rudan May 13 at 10:11
2

It will depend on how the contributions were created and whether payments were associated with it.

The best ERD I know of is this one doc.symbiotic.coop/dev/civicrm/latest/schema/index.html

At a minimum you will probably need to check the civicrm_line_item table as well as civicrm_contribution.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.