# How can I find the transaction date of a payment by check?

The question is double :

1. Is there a way to simply display this trxn date (not the Contribution date, but the date when the check was recorded) ?
2. The transaction is refered in civicrm_financial_trxn table : id, check #, trxn_date : how can I link this id to the contribution ?
• One question per question ;-) – petednz - fuzion Nov 13 '17 at 23:16
• you could retroactively fix this but cutting Q2 and pasting as a separate question. – petednz - fuzion Nov 13 '17 at 23:17
• Thank you Pete, I admit, but both questions appeared to me to be linked together. Anyway, it is not a problem. Don't worry. – Jacques-Marie Moranne Nov 14 '17 at 6:45

In short, it's already linked and being used and displayed in the system. Being able to find it depends on if you used the field.

The trxn_date field in the civicrm_finacial_trxn table stores the receipt date, which shows up in the transaction detail and on the bookkeeping report. If nothing is entered for receipt date, it uses the received date automatically.

Receipt date can be used for when the contributor received the receipt and for when you received the payment, since these should be the same date.

Received Date comes up in the report for date received. Receipt Date comes up in the report and detail as the transaction date.

I did an experiment in MySQL to make sure this is correct:

• Sorry Christia, don't take it personnaly : I admit that you answered my first question, with the bookkeeping report, but I wanted to know how the Contribution and the Transaction(s) were linked : I found that the link was managed via the civicrm_entity_financial_trxn table. I also discovered that you could have 2 transactions for 1 contribution. – Jacques-Marie Moranne Nov 13 '17 at 17:04
• But OK to accept your answer instead of mine : I should not have asked 2 questions in 1 – Jacques-Marie Moranne Nov 13 '17 at 17:11

trxn_id is the common denominator on the civicrm_financial_trxn table and the civicrm_contribution table

civicrm_financial_trxn.trxn_id civicrm_contribution.trxn_id

• Of course, I thought of that, but In those tables, for (event fee pay later) payments by check, trxn_id is NULL. – Jacques-Marie Moranne Nov 12 '17 at 16:30
• We have never done Pay Later so I cannot look to see what those look like. Seems that when they do pay, there would have to be a trxn_id generated then. ???? Do you manually enter those checks? If so, is one of the field options a transaction id? If so, come up with a system to enter a transaction id that is not duplicated. – Iowa Boy Nov 13 '17 at 5:23

Thank you Christia : I did not know this Bookkeeping Report, and this answers my 1st question.

Concerning the second question, I think I have found the answer :

For each contribution, with "pay later" by check, you have two Transactions, which are identified in table civicrm_entity_financial_trxn, which links the Contribution to its pertaining Transactions in civicrm_financial_trxn table :

1. One which concerns the Contribution itself : financial_account_to = 7 : receivable (with the contribution date)
2. One which concerns the Check record : financial_account_to = 6 : deposit (with the check # & deposit date)