I keep getting stumped on what seems like something that should be easy to accomplish:
Lets say you have three tables:
House
Room
Furnishings
House can have many Rooms that belong to it, Room can have many Furnishings that belong to it. That works fine and when I make a selector for Room using House and a selector for Furnishings using Room, they automatically fill in the parent. But not the Parents Parent.
I added House to Furnishings as a reference and if fill it in manually it works but i want Grist to auto fill that for me. is it possible? since House is a parent of Room which is a parent of Furnishings.
Another question:
I can create a summary widget for Furnishings and use the Parent or Grandparent as a selector.
when i create a new project and create the same tables and references i cant use the Grandparent (House) as the selector. what did i do differently the first time?
i also tried creating one more level of granularity on the first project “Accessories” and still cant select House, Room, only Furnishing as a selector. what gives?
Because they were defined as String. Internally, references are integers pointing to the (hidden) id column of referred table. So you should select again the Furniture, and the other columns should be updated.
Looked better at it, and the problem is your formula points to the bad information: instead of $Furniture.Room.Room, just write $Furniture.Room ; instead of $Furniture.House.Address, $Furniture.House. Or you could put the equivalent: $Furniture.Room.id and $Furniture.House.id.