SearchKit: DB Error: no such field a.total_amount when adding Tax Exclusive Amount and Balance (starting with Line Items : Contributions)
Overview
There's an "Unknown column a.total_amount
when trying to add these fields onto a SearchKit search that starts with Line Items
and with Contributions
:
Line Item Contribution: Tax Exclusive Amount Line Item Contribution: Balance
Note this only happens when starting with Line Items and not when starting with Contributions (and optionally adding Line Items).
It's adding a field like this:
COALESCE(a.total_amount, 0) - COALESCE(a.tax_amount, 0) AS `LineItem_Contribution_contribution_id_01.tax_exclusive_amount`
and like this:
a.total_amount - COALESCE((SELECT SUM(ft.total_amount) FROM civicrm_financial_trxn ft
INNER JOIN civicrm_entity_financial_trxn eft ON (eft.financial_trxn_id = ft.id AND eft.entity_table = 'civicrm_contribution')
WHERE eft.entity_id = a.id AND ft.is_payment = 1 AND ft.status_id IN (1,7)), 0) AS `LineItem_Contribution_contribution_id_01.balance_amount`
where a
is civicrm_line_item
. It's assuming, I think, that the first table is civicrm_contributions
.
Current behaviour
What happens currently. Please provide error messages, screenshots or gifs (LICEcap, SilentCast) where appropriate.
Environment information
- CiviCRM: 5.58.1 (but I looked through the release notes and I don't think it was fixed in 5.59.x)