Automatic filter based on formula?

Ask for Help
1

Similar to this question, I’m looking for a way to automatically apply a filter to a table widget via a formula, so that users with a specific role only see records relevant to them at the time.

Use Case

My sports club runs several league competitions throughout the year, and uses a table to track individual competitors’ results on the day. One column in that table is a date/time stamp that’s automatically populated with today’s date when new rows are created, and the whole table is manually filtered to only show today’s records via two widgets: one for entry scores entry and another which mirrors the live results to a big screen in the clubhouse.

The problem is that this solution requires the use of manual filters in order to hide scores from previous competitions, which might be erroneously edited, and also presents a barrier to adoption because users in the data entry role are “non technical” and can’t be relied upon to use manual filters.

Ideally I’d like a way of presenting only those records where the date / time stamp contains today’s date.

2

This sounds similar to this request: Filter Values when creating a Summary Table? - #7 by Travitron

3

Similar but not the same. I don’t want a summary table, I want something that appears to be a regular table but showing only records whose timestamp matches today’s date.

EDIT: I can add a static filter on ‘count’ and then hide that column, but if the end users discover the filter button then the result is potentially more confusing for them than if they had just filtered by date in the first place. They also can’t add a new record using the same widget.

4

did you save the filter?

5

It sounds like you’re describing a “view” on a table (in traditional database terms), and that’s basically what a Summary table is. A summary table doesn’t necessarily have to aggregate data.

So the idea of that other post is that you could create whatever filters you want (no columns needed), and it would be part of the Summary table (view) that end-users see, but they can’t see the conditions/filters that resulted in that table. That way, you get around needing to create special columns and hiding them (and hoping no one notices).

6

Traditionally speaking a View is exactly what I’m after, and in pseudo-SQL it would be defined something like this:

SELECT competitor, result FROM scores WHERE created == TODAY();

There might be 1,000 results from hundreds of competitions but all the entry guys see is those results from today.

I tried creating a summary table view grouped by competitor with one count formula column giving me something I can filter by:

sum(1 for r in $group if datetime.datetime.date(r.Created) != datetime.datetime.now().date())

so that I get a 0 in count which I can then use to filter out those results not from today, but it still needs a filter.

Apologies if I’ve misunderstood the proposed solution or am using Grist in the wrong way …

7

Circling back on this topic now that I’ve found a best-scenario solution for only showing those records whose timestamp matches today’s date. I created a boolean formula column to detect whether the linked competition’s date is actually today:

IF($Comp.Date == datetime.datetime.now().date(),True,False)

From there it’s a simple matter of installing a filter (Is Today == true), saving the filter, and denying the data input user the ability to alter structure. True, they can still click around and find their way to records outside of today’s date, but finding their way back to today is just a matter of resetting the filter.