Reference a grandchild table

[Olmis]

Hi all,

I’m trying to reference a column in grandchild table. I have a parent table “daily log” and in this I have ref to table “task”. One day has many tasks. Each task has a ref column to “project”.

In daily log, I’d like to concatenate name of the project that is connected to task.

In daily log [related tasks][related projects] gives keys (ID) of the project for each task, but I need the name of the project, which I did as follows:

1. Virtual column in “task” table: LOOKUP([RELATED PROJECT], “PROJECT”, “ID”, “NAME”).

2. In “daily log” table, CONCATENATE([related tasks][virtual column] works but just for curiousity, what would be the formula to fetch the data without the virtual column?

So from table “daily log” to table “task” to table “project” to get the name of teh project.

[Suvrutt_Gurjar]

The following implementation is with VCs only. However all VC expressions are based on system generated reference lists. So they should not be sync time expensive.

Understanding is Daily Log is grandparent, Task is parent and Project is a grandchild table.

If so, please add a VC in the Task table called say [Related Project Names] with an expression something like

[Related Projects][Project Name]

Then in the grandparent “Daily Log” table , please add a VC called say [All Related Project Names] with an expression something like

[Related Tasks][Related Project Names]

To do so with real columns, you may need to use actions. However, with real columns, the actions may need to rerun always when there is any relevant change in the Task or Project table. For example if Project Name changes in the Projects table for whatever reason, it will not reflect in the Daily Log table unless the action is rerun.

[Olmis]

Well that’s basically what I already mentioned, works with Virtual Columns.

What I’m trying to do here is learn a different way without polluting Virtual columns.

[Suvrutt_Gurjar]

Got it. I request you to note below.

@Olmis

Well that’s basically what I already mentioned, works worh Virtual Columns.

@Olmis

What I’m trying to do here is learn a different way without polluting Virtual columns.

I shared the VC expressions because the expressions you have used can be sync expensive. ( LOOKUP() , SELECT() etc.) I suggested a more standard way of using system generated virtual columns. All VCs are not necessarily bad always.

Please take a landmark post in the community that discussed VC impact in detail.

The poster @pravse is the creator and former CEO of AppSheet.

Solved: Improving performance by getting rid of unnecessar… - Google Cloud Community

@Olmis

What I’m trying to do here is learn a different way without polluting Virtual columns.

I have suggested the below as an alternative to VCs with the necessary precautions needed

@Suvrutt_Gurjar

To do so with real columns, you may need to use actions. However, with real columns, the actions may need to rerun always when there is any relevant change in the Task or Project table. For example if Project Name changes in the Projects table for whatever reason, it will not reflect in the Daily Log table unless the action is rerun.