Hi there! I am struggling a bit with understanding how this works, I’ve been at it for a few hours now and this particularly function is getting frustrating. Not sure if I don’t understand how it works or if there’s a bug.
Showcase of this: https://docs.getgrist.com/xrmCHUSsDQy7/BugShowCase
Steps:
- Create a table “students” on 1 page with names.
- Create another table on another page with Classes.
- Assign classes to Names.
- Create a formula showing the classes for each student on “Students” page.
If I create a list from Classes directly it creates a python list that is “errored”
If I create a list from Classes that has been referenced, it creates a normal, functional list.
If I create a duplicate “Class” column that is a reference to the existing Class column, just a reference rather than value directly… It works. Although I cannot automate this duplicate table, as if I add a formula it shows up as red. I need to manually select it for it to work.
Why?
Thank you in advance.
Found a workaround that does work.
Same thing as before but creating the duplicate thing, set the trigger to $id. I don’t quite understand why, but this then takes the value and automatically fills it in the “duplicate” field. So this then fills it automatically, letting the other fields automatically fill. Using only a single duplicate column, rather than anything more.
Hi! The formula for the Classes direct column in your example is:
Classes.lookupRecords(Name=$id).Class
and the type of the column is ReferenceList for table Classes. Change it to just this:
Classes.lookupRecords(Name=$id)
and you’ll get a list of classes as you are hoping for.
To understand what’s going on, I suggest trying these steps:
-
dd a new column (of the default type Any) with the formula
Classes.lookupRecords(Name=$id)
it would show results like Classes[[1,6]]. This is a list of matching records from the table Classes. Although I say “a list of records”, lookupRecords technically returns a “RecordSet” that’s more efficient than being a copy of the matching records, it’s more like a list of IDs (or pointers, or references) to records, which may be used to conveniently access fields of those records.
-
If you now change that formula (still in the column of type Any) to
Classes.lookupRecords(Name=$id).Class
you’ll see values like Maths, Science (no error). Here, each cell is the list of values from the Class column of the records returned by the lookup.
The thing to note is that (1) is a list of records (or references, or IDs), while (2) is a list of strings (names of classes).
The confusion comes when you set the column type. If you were to set the column type to Any or Text, you’d get something reasonable in both cases. When you set the column type to ReferenceList, the values really ought to be lists of records (or references, or numeric record IDs), because those are the precise identifiers of records in the Classes table. If the values are lists of strings (e.g. class names), then you get errors.
The final drop of confusion is that although the ReferenceList is populated with a list of numeric record IDs, it shows class names. That’s because the column type has another setting for which field to show from the referenced table. That’s done as a separate step, and in fact, the “SHOW COLUMN” setting does not affect the real value of that column, only what it shows.
In short, for a ReferenceList populated by a formula, Table.lookupRecords(...) is all you need, without any .Field part after it.
Wow, I definitely misunderstood how that works. Thank you so much for taking the time to properly explain it. I really appreciate that Dmitry!
The cell value vs what’s shown is definitely not easy to understand. Thank you again.