How to fix Bookkeeping Transactions Report so it doesn't show redundant transactions (e.g., for changed Payment Method)?

Question

If you change the Payment Method on a contribution, e.g., from "Credit Card" to "Check" for a $10 contribution, CiviCRM will leave the existing transaction record in place, and add two more transactions, so the contribution now shows three transactions:

$10.00   Credit Card
-$10.00  Credit Card
$10.00   Check

(This started a few versions ago, I guess as a way to help leave an auditable record of financial changes.)

This works fine in the contributions tab because it just adds up all the amounts, and it's still an amount of $10.00.

But in the Bookkeeping Transactions Report, all of these 3 transactions appear in output, and there's no effective way to filter them out. In reality there's just one $10 transaction, but the report shows two for $10.00 and one for -$10.00.

I want to propose a fix so that only the most recent of those is displayed in the report. But "most recent" isn't enough. Sometimes there is more than one transaction on a contribution, e.g., in the case of multiple partial payments, in which case we do want to show all of those separate transactions.

Question:

Is there any way, strictly by looking at the data tables, to determine which transactions are "changes" and which are "multiple partial payments"?

Answer 1

So it would be an error in bookkeeping, not a fix, to omit some bookkeeping entries.

For code maintenance and simplicity the implementation creates these transactions whenever there may be required bookkeeping entries.

If an organisation happens to use the same financial account for credit cards and cheques the extra entries do not result in accounting errors so much as noise. By contrast, if an org does use different accounts it would result in accounting errors to filter as proposed.

Although an extension to change the SQL for your use case is likely possible, to carry through on your approach of simplifying the results by summarizing the bookkeeping entries into a tally for each account for each contribution may be done more easily by creating a new report. Or perhaps it could be a special sort of 'group by'.

As you may have noticed in the report code, the data is puled out in two ways depending on how the bookkeeping entries are stored in the database. Entries involving revenue and expense accounts have one side of the entry in the financial item table and the other in the financial trxn table using its to financial account. Entries that only involve asset accounts, like a payment on a pay later transaction, use the from and to account fields in the financial transaction table. (I don't want to rehash the troubled history that led to this schema design, I'm just explaining it.)

Assuming you don't want to see these entries, you could simplistically filter for the most recent entry for each line item for a contribution. If all of your contribution data is simple and there are not edits on other fields this will work. Note that if there are taxes you will need to get the latest of those entries separately for each line item as well.

An alternative to just pulling up the latest entry that would also handle edits to the amount to produce the amounts for the revenue account and tax accounts for each contribution would be to use a group by contrib id, line item id, financial item financial account id and then sum the financial item total field. You'll need to do this separately for the other side of the accounting entries, ie the asset account for the deposit or pay later: group by contrib id, financial trxn to financial account id. And as some bookkeeping entries do not involve a revenue account, you'd need to handle that as well.

Sorry this is not so simple. It would likely take me an hour or two to work up the sql.

An alternative that might be interesting is to check if the financial accounts and amount are the same for a 'change' entry. If so, an extension could avoid writing new bookkeeping entries into the database when, for example, the payment method changed but the same financial account was used for both payment methods. It would have to be an extension, I think, as we found that some people needed the extra entries for other reporting purposes.

Payments can be identified in the database by checking if civicrm_financial_trxn.is_payment is true. There can be single or multiple payments. Note that there can be changes to payments, either single or multiple partial ones, and the above discussion also applies to all of them.