Nested relations in Formula columns

[lldev0]

Checklist

• [X ] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: kubernetes
  • Budibase Version: 2.31.4
  • App Version: 2.31.4
  • Tested also with version: 2.32.5

Describe the bug
I have a 3 tables with 1:n relationship as per this model:

First have only defined relationship to second table and is working fine:

Second table is with formula generated column named landscape_name {{ asset.0.asset_name }} - {{ landscape_type }} as a display column and also working fine:

Third table also have formula generated display column named environment_name, but this one is not getting correct display column name from second table, tried 2 different approaches:
{{ landscape.0.landscape_name }} - {{ environment_type }} - {{ environment_region }}
and
{{ landscape.0.primaryDisplay }} - {{ environment_type }} - {{ environment_region }}

Formula generated column environment_name should look like this:

%asset_name% - %landscape_type% - %environment_type% - %environment_region%

Application 1 - prod - production - germany
Application 1 - prod - production - france
Application 1 - prod - preproduction - germany
Application 1 - nonprod - uat - usa
Application 1 - nonprod - dev - usa
etc...

So probably there is a bug which is preventing using nested formula columns. I tried this with MySQL and PostgreSQL database, same behaviour

Expected behavior
Formula generated column in third table should pick correct display name from second table

Desktop (please complete the following information):

• OS: Windows
• Browser: Edge
• Version: 128.0.2739.67

[linear]

BUDI-8652 Nested relations in Formula columns

[lldev0]

i just tested it with version 2.32.5 and situation is same

[aptkingston]

Hey @lldev0. The correct syntax here is the real column name landscape.0.landscape_name but if you want to reference nested formula columns like this then you'll need to use "static" formula columns rather than "dynamic" once. Dynamic formula columns are generated on the fly when fetching data, so are not always available when doing things like evaluating other formula columns due to how data is fetched internally.

Static formula column values are actually saved into the table. Try recreating this with static formula columns and see if that works. In my testing locally I can recreate this and get it to work by using static formula columns.

[lldev0]

could you please confirm if "static" formulas are working also if we are using MySQL or PostgreSQL database as a source ? because I see this option in your documentation https://docs.budibase.com/docs/formula but do not see it in our deployed app

[aptkingston]

Ah apologies - didn't realise it was MySQL. You're right, static formulas aren't available on external datasource so please ignore my answer!

Another workaround is probably just include the nested formula inside your other table formula. So instead of using landscape_name (the formula) you could reference the values that make up that formula. Although, you're using a relationship from another table in landscape_name so this won't be possible either as you can't access nested relationships in formula columns.

So I'm sorry but I don't think this is possible at the moment. Sounds like a valid feature request though!