Is it possible to dynamically reference columns from multiple linked tables?

1

Is there a way to reference columns from multiple related/linked tables with a single formula?

For example, I have a Combined Table where the Name column is a select list generated with the formula: ListCombine(Fruit, Vegg).

Is it possible for the Price column in the Combined Table to dynamically reference the Price column of either the Fruit or Vegg table, based on the source table of the selected Name?

Note: This project involves MANY source tables, so I’m looking for a dynamic solution that avoids hardcoding multiple if() or switch() statements. Ideally, the formula should adapt to the selected Name’s originating table without manual intervention.

Here’s a test doc

Thanks so much!

1 Like
2

Welcome to my suggestion, you are lucky post number #10 :laughing:

3

I know your example is a simplification, but do you have the possibility to combine ALL your source tables in a single one with a more generic name like ‘Items’?

You could add a ‘Type’ field with values like Fruits, Vegetables or whatever. I’m assuming you have different source tables because each type requires different columns, but in this generic table you could just add any number of columns that is relevant for any of the types. For any given row, most of the columns will be empty since they won’t be relevant to the specific type.

Hope this helps,

Pablo

1 Like
4

I STRONGLY support Pablo’s suggestion above. Even if you sell/ buy Fruit, Vegetables and Car parts, use a product type column and views to manage your data. It is much more flexible doing it that way.

P

5

btw, If you really must have all those different source tables, you could use @Paul_Danyliuk 's pack → The most powerful Coda Pack: Formula Tables

1 Like
6

This is the exact use case for the Merge Table pack! Here’s what it would look like with your test doc:

7

Not an ideal solution, but if you plan to pull in only a single column:

SwitchIf(
  thisRow.Name.IsFromTable(Vegetables), 
  _Deref(thisRow.Name, Vegetables.Price),
  thisRow.Name.IsFromTable(Fruits), 
  _Deref(thisRow.Name, Fruits.Price)
)

this way you only have to update one switch when you add or remove tables.

Or if you want a shorter version you can chain .ifBlank statements:

_Deref(thisRow.Name, Vegetables.Price)
  .IfBlank(_Deref(thisRow.Name, Fruits.Price))
  .ifBlank(_Deref(thisRow.Name, OtherTable.Price))
  .ifBlank(...)