In a Softr app, I like to display all projects with tasks that are due or overdue.
For this, I created two tables for projects and for tasks in a Softr database. They two tables are linked through a linked record field.
Every task comes with a due date (date field) and a done (checkbox field).
To display the amount of tasks that are due in every project, I created a rollup field to sum up how many tasks are due or overdue by using the COUNTA formula on the tasks’ Record ID field with 2 filters: done = false and due date on or before today.
The rollup works correctly the moment I set it up. Two days later though, the rollup field still shows the same number of due tasks, even though more tasks are now due. This only changes after I start editing the table.
Has anyone encountered a similar issue and has any solution for it?
I could run a workflow every night to update the number of due tasks, though in my understanding, Rollup fields are there for exactly this usecase?
This is expected behavior rather than a bug, though I agree it’s not obvious.
Rollup fields recalculate when a related record changes. A new day starting isn’t a record change, so nothing triggers the recalculation. Your filter depends on “today”, which moves on its own, but there’s no write event for the rollup to react to. That’s why it’s correct when you set it up, then stays frozen, then corrects as soon as you edit the table. @Temima, this also explains the inconsistency you’re seeing, the value refreshes whenever something happens to edit a linked task.
One thing to avoid: creating a formula field on Tasks that calculates whether a task is due, then rolling that up. It has the same problem, since that formula also depends on today and also only recalculates on write.
Your nightly workflow idea is the right direction, but I’d structure it slightly differently so you’re not rewriting every project record:
Add an “Is due” checkbox field to your Tasks table.
Create a scheduled workflow that runs nightly and sets “Is due” to true for tasks whose due date is that day. This only touches the handful of tasks that just became due, not the whole table.
Change your rollup to count that checkbox, with the done = false filter.
Since “Is due” is a stored value with no dependency on the current date, the rollup behaves normally from then on. It updates instantly when tasks are edited or completed, and the nightly run only handles the date rollover, which is the one thing rollups can’t detect on their own.
One alternative worth considering: if you mainly need to show the tasks themselves rather than a count on the project, a list block filtered by done = false and due date on or before today will work without any of this. Block filters are evaluated when the page loads, so “today” is always current there.