We detected recently some duplicate contributions records. How to make a list of all duplicates? We use the field "source" to indicate the year of the contribution. So we are looking of a list of duplicate contributions for instance for source = "2017"

  • Could you clarify what's meant by "duplicate"? Is this an online donation that got recorded twice due to a bug? Is it someone doing data entry by hand that entered a batch of donations twice? Donations that were imported twice? Each one would have a different strategy in my opinion. – Jon G - Megaphone Tech Nov 6 '17 at 19:04
  • @JonG-MegaphoneTech. All contributions are manually updated by a volunteer, based on bank payments. – MarkDQ Nov 7 '17 at 0:03

We found a solution by using this SQL query:

/* === Duplicate contributions === */
/* c contact * b contribution * t temporary */
SELECT b.contact_id ContactID, c.last_name Last name, c.first_name First name,
       b.source Year, b.total_amount Contribution,
       b.receive_date Received,  b.receipt_date Processed
FROM civicrm_contribution b
    select b.contact_id, b.source, count(*)
    from civicrm_contribution b
    group by b.contact_id, b.source
    having count(*) > 1
    t on b.contact_id = t.contact_id and b.source = t.source
INNER JOIN civicrm_contact c ON b.contact_id=c.id
| improve this answer | |
  • 1
    The caveat is that this will only work if your contacts only make one contribution per year. – Jon G - Megaphone Tech Nov 7 '17 at 7:39
  • @JonG-MegaphoneTech. This is indeed the case, one yearly contribution for yearly membership. – MarkDQ Nov 7 '17 at 11:57

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.