Stuck on a SUM & lookupRecords Related Issue

Ask for Help
1

If Dmitry and Anais see this forum post, I’m sorry in advance. I swear I have been doing so much better in other columns with SUM() from the previous examples you have given me and I’ve been staring at some of the examples we worked on together, but I’m completely stumped on why this is resulting as 0. :sweat_smile:

So I have a table called Tutoring Payment Tracker and in this table one of the columns is called Lesson Info, which is referencing our Tutoring Orders table with an ident column. On the Tutoring Payment Tracker Table, there is a column called Number of Lessons Confirmed, which is the number of lessons that are confirmed to happen for that particular order.

What I’m trying to do is to get a the Total Number of Confirmed Lessons from the Tutoring Payment Tracker.

I currently have the formula set up as:
SUM(Tutoring_Payment_Tracker.lookupRecords(Lesson_Info=$id).Number_of_Lessons_Confirmed).

My thought process was that I am looking at the Tutoring Payment Tracker table, and I’m looking for all the instances of the Lesson Info (which is why it’s the ID), and then I’m looking for the specific column: Number of Lessons Confirmed, which should be a numeric and calling SUM on all of that should be 12 The 3 items in the table are all the instances of Jane, Test Student coming up through a filter, but I’m seeing 0 across the board.

Am I just doing this on the wrong table or am I using the wrong column for the ID? Here’s a screenshot of the formula and what this page looks like.

image
image2111×1249 276 KB

I would try to break it down to see what it looks like as just Tutoring_Payment_Tracker.lookupRecords(Lesson_Info=$id) and I would receive this:

image
image2146×1259 309 KB

So part of me is thinking maybe I’m not looking up the right thing, but I’m not 100% sure. One day, this will make sense and I don’t have to keep asking about it, but until then, thanks in advance for your help! :sweat_smile:

2

Hello there! Definitely a good idea to break it down and see what the lookup is returning. If it’s not returning what you expect, then maybe the formula is indeed looking up the wrong thing.

When you use “$id”, it’s the ID of the record for which the formula is being evaluated. So Tutoring_Payment_Tracker.lookupRecords(Lesson_Info=$id) says to look up all Tutoring_Payment_Tracker records whose Lesson_Info field is a Reference to this current record.

It’s hard to tell from the screenshot what the current table is, or what the destination of the Lesson_Info Reference is, but check if they match. For the current table (i.e. the one with title “B. Tutoring Payment Tracker Table”) you can see the name of the table it represents in the creator panel on the right, under Table > Data subtab. For a Reference column, the destination table is the one in the creator panel under Column tab, under “Data from Table”. If they do match, then the problem must be something else.

When you have many tables, it becomes harder to know what’s right. This gives me a couple of ideas how Grist could make it easier:

  • When editing a formula, we could replace the titles of page widgets with the names of the tables they are showing, similarly to how column labels turn into their Python identifiers.

  • When using lookupRecords(A=B), if A is a Reference column, we could ensure that the value B is a Reference to the same table, or $id of the same table, and fail otherwise.

1 Like