The Sum function regularly fails to accurately Sum the values in relevant (filtered) records of connected Data Tables.
WARNING: Updating a record that has a Sum function DOES NOT cause the Sum to be recalculated. Therefore, if data has changed in the fields to be summed it they are NOT GUARANTEED to be summed accurately.
I reported the issue to Knack and they said Knack “will only update the field when any connections are added … or at the internally scheduled time for the month.” They suggested that the workaround would be to manually “change and add a filter to the sum field then hit save, after waiting a bit then to remove the filter.”
This workaround is totally impractical as it requires a developer to make updates on the Data tables.
The logical solution is for Knack to force a recalculation of the Sum function whenever a record is updated in the same way as happens with ordinary formulae. However, they have not committed to implementing this necessary solution.
Please comment if you would like this to be sorted, or note that Sum fields may be reporting inaccurate data - and there is no way to be sure or check.
Hey @SteveV, I suggest putting this in the Feature Requests section if you want other forum members to vote for it.
If you want to ensure that sums are up to date more frequently, you can run a daily Task that updates a field in the connected records. This way it will then force recalculation.
I usually do this on parent tables involving an ‘active’ status so that it isn’t having to go and update all connected records in the database.
Thanks @StephenChapman. Unfortunately, the moderator has not allowed me to submit a feature request as it is a bug fix. But I will try again!
Also, I’m afraid that your suggestion to force a recalculation by using a task currently only works for Formulae. It is not guaranteed to force the recalculation of Sums of records from connected Data Tables.
However, this is precisely what I am suggesting that Knack implements. Updating a record via a task or any other method should Always also force the recalculation of Sums.
The feature requests category in the forum is specifically for new feature requests for the platform that does not include fixing any underlining issues that should not already be occurring with our current features. However, I have shared this post with our team and we are actively discussing next steps to address this issue.
Thank you both for sharing info, and I appreciate discussions here that are intended to help other users facing the same issue with their apps.
@SteveV
I’ve recorded a short video here demonstrating how updating connected records does update the sum in the parent table.
EDIT: Obviously this isn’t taking into account 1,000’s of records, so not to say that you/others may be experiencing issues (like I have in the past), but it is showing that updating via tasks also recalculates sum fields.
Hi @SteveV
While I don’t have your support ticket, I asked an engineer why this may have happened, as I could not repro it.
They thought that perhaps this was a sum where a filter with “date is during the current month” (or similar) was not updating and possibly the job to update it failed for some reason, so in that case, we won’t automatically recalculate again until the start of the next month (or another forced change).
Totally agree with you Sums need recalc’ing when a record update and I hope that this does not occur for you again.
Cheers,
Kara
I back this issue / request; I have had it a number of times with any of the SUM / COUNT type fields and when it they are showing real-time live data it can’t be relied upon. I like the suggestion of @StephenChapman to use the task to refresh but this shouldn’t be necessary. Bugs like this are surely more important than native integrations…
I have found a reliable - although NOT intuitive workaround.
I still strongly recommend that Knack forces a recalculation of the Sum function whenever a record is updated.
The workaround is to refresh the CONNECTION field in the child record (the one being summed) in ALL the possible records. For example by setting a task to run daily that copies the value of the connected table back into the same field. Note that from my experience, simply updating other fields in the child (or parent) record is not sufficient to force a recalculation.
Experienced users who really understand this issue, may be able to implement this sort of thing, but I suspect that there are will be many users who don’t know that their Sum functions have invalid data.
The sum function bit me this week, unfortunately it is near the end of the year and is causing us to revert to Excel to track something that has worked previously. We have an Activity table that is connected to our Accounts table with 2 connections. The one connection is so we can track the user that updates any records in the Activity table. The other connection is so the user can log their activities (work hours, paid time off, on call work etc.) and have them automatically tied to their Account. We are seeing a few instances where the sum is simply not correct. This is not a filtered sum, but simply summing a numeric field in a connected table. The fact that it isn’t working is one issue, the other is we’d like to add functionality but are nervous because of how unstable it is. I can implement a task, but I’m not sure what is meant by copying 'the value of the connected table back into the same field" Can you elaborate?
