Filtering table entries by today()

I use Coda to host a points tracking system for my students. Someone behaves well, I give them a point. The point is recorded as a row in a table, like a transaction on a ledger.

Student Date Point
13 May 2025 -1
J 13 May 2025 -1
J 13 May 2025 -1
D 14 May 2025 1
T 14 May 2025 1
D 14 May 2025 1
D 14 May 2025 1

Points for each student are totalled up in a column on the main roster table, by this formula:

sum([Student Points].Filter(Student = thisRow.Name).Point) :white_check_mark:
And points awarded today (eg. in this class) are tracked with this one:
sum([Student Points].Filter(Student = thisRow.Name).Filter(Date = Today()).Point):white_check_mark:

Now, I recently set up a separate set of points, with the exact same set up, this one for doing their homework. Here’s the points total formula, which works:

sum([Academic Points] .Filter(Name = thisRow.Name).Point ):white_check_mark:

However the “points today” formula is malfunctioning. Its column is all zeroes:
sum([Academic Points].Filter(Name = thisRow.Name).Filter([Academic Points].Date = [Academic Points].Today).Point) :cross_mark:

I even set up a ‘date’ and ‘today’ column on the points table, just to expose the values:

Name Date Point Today
A 14 May 2025 1 14 May 2025
D 13 May 2025 1 14 May 2025
A 14 May 2025 1 14 May 2025
T 14 May 2025 1 14 May 2025

For some reason, Filter.(Points.date=today()) works for one column’s formula, but returns no values on the other.

Hi Joseph,

Welcome to the community!

It would be much easier to help you if you would share your doc.

But anyway, here’s a couple of pointers:

  • I would recommend you to try to minimize the number of tables, specially when the objects are so similar (in this case both are points).
    If I were you I would delete the second table and just add a ‘Type’ column in your original table with possible values Academic and Student. You won’t need to replicate all the common columns and formulas saving time and avoiding errors like the one you are experiencing.
  • You don’t need to stack filter() formulas, it’s probably worse for performance and not as flexible as using AND or OR between conditions.

This is how the formula would look like if you follow my tips
Points.Filter(type=Academic AND Name=thisRow.Name AND Date=Today()).sum()

Let me know if this helps you,

Pablo

1 Like