Showing grandchild rows Issue

[vshishporenok]

Hello!

I have a 3 level relationship in my Application.

Orders (Grandparent) -----> Campaigns(Parent)-------> Campaigns_Stages(Grandchild).

What I want to see is the correct inline view in "Orders" view that will show respective Campaign Stages related to this Order.

Using trick from this thread :https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/How-to-effectively-display-grandchild-relations...

I succesfully established the chained relationship and my current "Orders" view shows Campaign Stages in inline view.

1.Campaigns (Parent table) has IDs of Orders(Grandparent) in "IO_NUMBER" column. It has REF type and Source is "Orders" (Grandparent) table

2.Campaign Stages (Grandchild table) has Reference to Parent's table via CampaignID that is held there (with REF type and source is "Campaigns")

3.I created virtual column in Campaign Stages (Grandchild table) that pulls IDs of Orders ("IO_NUMBER") into this table. On one of screenshots below it is shown that this data is being pulled correctly

4.I created REF_ROWS formula in Orders (Grandparent) table that pulls rows from Campaign Stages (Grandchild table)  via Virtual column created on step 3 (REF_ROWS("Campaign Stages", "IO_NUMBER"))

The Issue is that this view shows ALL rows from "Campaign Stages" table not taking into account Order ID that is related to them

When testing the REF_ROWS formula in Orders (Grandparent) I saw that it returns all Campaign Stages (Grandchild) IDs

Does anyone has ideas on why does this reference technically works, but does not limit number of rows to only those respective to certain Order?

[Steve]

How are FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() related?

[vshishporenok · Accepted solution]

Figured it out myself. In Grandchild table relation was pointed at grandparent directly. Changed to parent's table and now it is working.