Total Contributions in Last 12 Months Not Calculating Properly

progressivetech / net.ourpowerbase.sumfields

The Summary Fields extension creates read-only custom data fields that extend Contact and are automatically populated with up-to-date totals such as total lifetime contributions, last contribution amount, last attended event, etc.

Other

8 stars 29 forks source link

Total Contributions in Last 12 Months Not Calculating Properly #77

Open mdorfman95 opened 4 years ago

mdorfman95 commented 4 years ago

The field 'Total Contributions in the Last 12 Months' is returning the amount '1260' even though only 2 contributions of 180 have occurred in the last 12 months.

twomice commented 4 years ago

I'm also seeing incorrect totals on this field; in my case, the calculated total amount is lower than it should be. However, I'm also seeing incorrect amounts in other fields, so can't say yet how and whether my problem may be related to the OP's. I hope to investigate and report findings.

lcarterBOT commented 2 years ago

@twomice Has this issue been reviewed since August 2020? It seems like several of the fields are either still calculating at the contribution level, or are including the whole transaction if any line item in it has an appropriate financial type. In the attached screenshot, the user made a payment of $140, of which only $20 was of a financial type included in the settings. That's being correctly calculated in some of the fields, as seen, but not in others, which is confusing to say the least (and makes the fields a bit undependable to use). 2022-04-14_13-31-16

twomice commented 2 years ago

@lcarterBOT Looking back at my notes from those days, it appears that we just worked around this issue by doing something completely different without addressing this issue further. The client had some other related issues including performance of Summary Fields calculations on some very large datasets, so we just created some read-only custom fields and custom SQL queries to populate those fields on a nightly cron schedule; that approach removed the need to solve the issue in this ticket, so we just kind of moved on. :-/

lcarterBOT commented 2 years ago

Thanks for the update!

jmcclelland commented 2 years ago

I have found two general causes of discrepancies like this:

Something goes wrong with the SQL trigger. I have no solid leads here - but there are a number of potential problems. What happens if a contribution is started but something goes wrong and it's rolled back? What happens if a contribution is started and it doesn't roll back? If manually tweak the contribution in certain ways it won't trigger and new total on the summary field. I often simply re-generate the summary fields to resolve these problems. It's not a great solution and doesn't lend confidence to the results, but it's the best we have.
Confusion between the total amount in the contribution table and the line items. The extension gives the option to calculate based on the line items table or the (simplified) total amount field in the contribution table. These are sometimes different - when a contribution is split between different line items that have different financial types. It's really quite hard to configure summary fields to operate the way you expect it to.

lcarterBOT commented 2 years ago

I think we're seeing issue #2 in your list... there are three line items in the transaction, only one of which should be included in the summary fields, and while some of the fields are correctly limiting the calc to just that one line item, others are not.

jmcclelland commented 2 years ago

Number 2 is user error - usually a group doesn't realize that they have summary fields configured to track line items and they are looking at the contribution total (or vice versa). Or, they don't have the financial types configured properly. It's fixed by aligning the summary fields configuration with what you are expecting.

If Summary fields is configured to track line items and your line items with matching finanical types don't add up to what summary fields is providing and you have tried re-calculating the summary fields (to eliminate the possiblity of the first problem), then it might be a bug.

If that's the case... can you try upgrading to the most recent version of this git repo and try again?

lcarterBOT commented 2 years ago

If it was user error I'd expect all the fields to be wrong, not just a few. See the screenshot I posted above - total lifetime contributions is correct based on the line items, but the amount of first contribution is the full transaction amount. Summary fields are definitely set to pull in line items and the financial types are correctly configured. I believe we're on the most current version but I'll doublecheck that.

jmcclelland commented 2 years ago

Yes - I agree that it doesn't sound like user error and was surprised you thought it sounded like the second option. I suspect it's the first, but only way to know is to have you re-save your summary fields configuration which should cause everything to be recalculated. If, after recalculating, the numbers are correct, then it's definitely some problem with a trigger.

On the other hand, if you recalculate and the totals are still incorrect, then it may be a bug in the code or even some corrupt configuration problem.

It is possible to configure summary fields to use line items for some fields and the contribution table for others. If your site was upgraded from a previous version that did not support line items to one that does support line items, something may have gone screwy in the transition.

The nice thing with summary fields is that your configuration is the only data lost if you uninstall and re-install since everything else is calculated.