How would you accomplish this?

I keep getting stumped on what seems like something that should be easy to accomplish:
Lets say you have three tables:

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.

Here is a mockup:

You should make the House column of Furniture table a formula, with this content : $Room.House.

1 Like

Well that was stupid easy. I trip myself up with column vs table names sometimes.

Thanks for the quick answer

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?

Ensure you define the type of columns referring to ancestors as reference to the good table.

Okay, I did that and I can use the them as a selector now but why are those formulas showing a red background with no error?

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: $ and $

1 Like

That was it. Thanks so much