I have a task where I need to clean up payment instrument data. The basic problem was caused by a custom payment instrument of TBD, which is somehow preventing contributions from updating correctly, leaving the to_financial_account static and thus leaving contributions as unpaid. So I was given what seemed to be a straight forward task of purging these TBD's from the database and just replacing them as Check. However, I am having problems correctly updating the civicrm_contribution.payment_instrument_id when there are multiple transactions. I'll walk through what I'm trying to do with some screenshots and show the SQL statements I am using, including the one I would like help in fixing.

Here is my test case:

enter image description here

I need to change the two bottom TBD's to Check (financial_trxn.payment_instrument_id), while still having the contribution.payment_instrument_id reflect the most recent financial_trxn.payment_instrument_id. Which in this case is Credit Card. I realize that they all have the same time stamp, what I am really mean by most recent is the greatest civicrm_financial_trxn.id for that contribution, which I will go into more detail about soon.

Here are my two queries:

Query 1: (update financial_trxn.payment_instrument_id, aka purge TBD)

UPDATE civicrm_financial_trxn
SET civicrm_financial_trxn.payment_instrument_id = 
        WHEN (civicrm_financial_trxn.card_type_id IN (1,2,3,4)) THEN 1
        WHEN ((civicrm_financial_trxn.check_number IS NOT NULL) OR (civicrm_financial_trxn.check_number !='')) THEN 4
        WHEN civicrm_financial_trxn.payment_instrument_id = 6 THEN 4
        ElSE civicrm_financial_trxn.payment_instrument_id
WHERE civicrm_financial_trxn.payment_instrument_id IS NOT NULL

This query basically looks at all civicrm_financial_trxn where I might need to update the payment_instrument_id. If there is a credit card type or a check number, then assign the instrument id for check or credit card. If there is a TBD (id 6), assign it as a Check (id 4). Otherwise, leave it be. From what I can tell, this query works, but things go obviously wrong in the next query.

Query 2: Update contribution payment instrument based on the latest financial trxn payment instrument

UPDATE civicrm_contribution
INNER JOIN civicrm_contact ON civicrm_contact.id = civicrm_contribution.contact_id 
LEFT JOIN civicrm_entity_financial_trxn ON (civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id AND
          civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')
LEFT JOIN civicrm_financial_trxn ON   

MAX(civicrm_financial_trxn.id) FROM civicrm_financial_trxn 
) =
SET civicrm_contribution.payment_instrument_id =
        WHEN (civicrm_financial_trxn.payment_instrument_id = 1) THEN 1
        WHEN (civicrm_financial_trxn.payment_instrument_id = 2) THEN 2
        WHEN (civicrm_financial_trxn.payment_instrument_id = 3) THEN 3
        WHEN (civicrm_financial_trxn.payment_instrument_id = 4) THEN 4
        WHEN (civicrm_financial_trxn.payment_instrument_id = 5) THEN 5
        WHEN (civicrm_financial_trxn.payment_instrument_id = 6) THEN 4
        WHEN (civicrm_financial_trxn.payment_instrument_id = 7) THEN 7
        WHEN (civicrm_financial_trxn.payment_instrument_id = 8) THEN 8
WHERE civicrm_financial_trxn.payment_instrument_id IS NOT NULL  

In this query I want to update the contribution.payment_instrument_id to match the most recent financial_trxn.payment_instrument_id

When running the two queries back to back, I get this result though:

enter image description here

The problem is that the contribution payment method switched to check, when it should be Credit Card (last financial trxn). Query 2 works when there is a 1 to 1 relationship between financial_trxn.payment_instrument_id and contribution.payment_instrument_id. In this case there are three cases to one, so I am trying to select the most recent financial transaction in the query by using MAX(civicrm_financial_trxn.id) FROM civicrm_financial_trxn in a subquery.

Here is a shot of what I mean by wanting to update based on the greatest financial_trxn.id

enter image description here

The query I used to create that view by the way is:

SELECT civicrm_contact.id, civicrm_contribution.id, civicrm_contact.sort_name, civicrm_contribution.payment_instrument_id AS cont_payment_instrument, 
civicrm_financial_trxn.card_type_id, civicrm_financial_trxn.check_number,
civicrm_financial_trxn.payment_instrument_id AS ft_payment_instrument,civicrm_financial_trxn.id,
civicrm_financial_trxn.trxn_id, civicrm_entity_financial_trxn.amount
FROM civicrm_financial_trxn
INNER JOIN civicrm_entity_financial_trxn ON civicrm_financial_trxn.id = civicrm_entity_financial_trxn.financial_trxn_id
LEFT JOIN civicrm_contribution ON (civicrm_entity_financial_trxn.entity_id = civicrm_contribution.id AND
          civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')
LEFT JOIN civicrm_contact ON civicrm_contribution.contact_id = civicrm_contact.id
WHERE civicrm_contact.id = 1

Any thoughts on how to fix the second query, or perhaps an idea for a different solution?

  • Not trying to understand it all but in your second query, are you sure you don't need a join between civicrm_financial_trxn to civicrm_entity_financial_trxn ? i.e. civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id
    – samuelsov
    Apr 25, 2019 at 19:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.