Schema support for rounding
We have an ongoing issue with rounding which I essentially believe is because our schema does not adequately support the variations on rounding in use, and I think we need to add additional fields to support this. The principle I think we need to work to is:
Can we calculate the 'missing' values with the data we have, if not we need to store it not treat it as calculable
Our exising code assumes that if you have 2 out of 3 of tax_amount, tax_rate and one of tax_inclusive or tax_exclusive you can calculate the third. However, if what you have stored is numbers with some rounding applied you actually can't reverse engineer them.
Specific proposal I propose we add to the schema
- civicrm_contribution.total_amount_exclusive
- civicrm_line_item.line_total_inclusive
- civicrm_line_item.tax_rate (note this CAN be calculated from the price field value id but could change over time either because of legislative changes such as sales tax increases - it only ever increases - or configuration changes.)
- civicrm_price_field_value.amount_inclusive
These would be calculated, if not provided, at the point of save. v4 Order api and Form Builder & BAO layer would support more nuanced usage of these but I don't anticipate any immediate changes to existing contribution or configuration quick forms to accommodate these. Search kit would expose them.
There is a bit of a gotcha around not-exposing them & being able to edit them on forms - so it is likely the contribution edit form would need to be adjusted to avoid re-saving in a way that causes problems. Possibly we need to get the ability to edit amount off the main form & into a sub-form which incorporates line item editor - this is something we have talked about before.
Current situation The variations of rounding approaches we have people reporting using are
- start with tax inclusive
- start with tax exclusive e.g
Version | Tax exclusive | tax rate | tax amount | tax inclusive |
---|---|---|---|---|
start from inclusive | 869.57 | 15% | 130.43 | 1000 |
start from exclusive | 869.57 | 15% | 130.44 | 1000.01 |
-
Round each line item before totalling (I think we are doing this) - apply tax as a % of the rounded amount, round the tax on each line
-
Store each line item un-rounded - apply tax per line item & add up
-
Total and then round the sum of the line items - apply tax to the total as a %, the amount that does not fit the line items is a rounding line item ?
The relevant existing tables /fields are
civicrm_contribution
- total_amount (inclusive)
- tax_amount ~~ - non_deductible_amount~~
civicrm_line_item
- line_total (exclusive)
- tax_amount ~~ - non_deductible_amount~~
civicrm_price_field_value
- amount (exclusive) ~~ - non_deductible_amount~~
civicrm_financial_account
- tax_rate
Related issues #3714 financial#189 financial#52
Other
Api v4 contribution.get supports returning tax_exclusive_amount
for the field proposed as total_amount_exclusive above