Combine data from two tables based on selection in third

Hey Coda friends, I’ve just started working with Coda and have basic lookups working.

I wonder now if the following is possible:

Table 1 has Stages and Activities
Table 2 has Stages and Stakeholders (Based on the same stages)
Table 3 pulls data for Activities and Stakeholders from Tables 1 and 2 based on which Stage you select

I’ll assume that the behaviour in Table 3 should be that the you would need to manually add rows and choose a stage for each, and not have it auto-populated with each combination.

In Table 3:

Activity

[Table 1].Filter(Stage = thisRow.Stage 2).First().Activity

Stakeholder

[Table 2].Filter(Stage = thisRow.Stage 2).First().Stakeholders

Note that these formula select the first match (arbitrarily) if there is more than one. For example if there were two stakeholders with Stage Planning, the result could be either. You could either enforce that this is not the case, or pick some strategy to resolve which is chosen.

Also, the syntax might not be exact but should be close.

Yep that is the correct behavior loucadufault and worked well.

Thanks for pointing me in the right direction and familiarizing me with filters.

Cheers and have a great day.

1 Like