A nicer way to get values from a row from an unknown table?

1

I want a select control/column to be able select from multiple tables. That part is easy

Table A.ListCombine(Table B)

and values from both tables are shown.

The issue is when I want to get a value from the selected row. In a select control, autocomplete only lets me choose columns from the currently selected value, and in a select column nothing. Even if “trick” autocomplete to offer everything, by selecting different values from all tables between formula edits, in the end the formula still reports an error.

This is just a sample, but it illustrates the idea:

SwitchIf(
  SelectedRow.IsBlank(), "Nothing selected",
  SelectedRow.IsFromTable(Table A), SelectedRow.DataA,
  SelectedRow.IsFromTable(Table B), SelectedRow.DataB
)

In short, my question is is there a nice way to “cast” a row so it’s data can be accessed directly?

It seems to me that there is a missing complementary function to IsFromTable, something like Cast(SelectedRow, Table A), or IsFromTable should return the already properly typed row or “null” (or whatever is considered an empty value in Coda) if the row is not from the passed table.

Solutions I can currently think of:

  • Convert the row to json with “_Merge” and parse until I find the value. Very brittle, and using a hidden formula.
  • After checking to which table the row belongs to, perform a table search to get the same row but properly typed. More code, potentially slow with a lot of data.
SwitchIf(
  SelectedRow.IsBlank(), "Nothing selected",
  SelectedRow.IsFromTable(Table A), Table A.Filter(SelectedRow).First().DataA,
  SelectedRow.IsFromTable(Table B), Table B.Filter(SelectedRow).First().DataB
)
  • Anything else?
2

Hello @Nikola_Lajic !

I have another solution but it wouldn’t be very manageable if you use a great number of sources.
You could create a relation column for each table, which for Table A would look like :

If(
  SelectedRow.IsNotBlank() && SelectedRow.IsFromTable(Table A),
  SelectedRow, 
  ""
)

Then reference this new columns in your other formula and have access to all properties.

1 Like
3

Thank you @Martin_Portevin. It’s not a perfect solution, but it could be useful in some cases. It cleans up “the main” column, at the expense of additional columns. At least that bit of code will be easier to read.

Also the plus of your approach is that it negates any possible performance penalties, since there is no additional lookup for each row.

4

There is a hidden formula that does the casting. In the description it says it’s deprecated and soon will be removed, but I’ve been seeing that description for three years already, and for the lack of a better solution even some Coda templates are using it.

The function is _Deref() and is used like _Deref(SelectedRow, TargetTable.TargetColumn). It returns the value of TargetColumn as if SelectedRow was a row from TargetTable. If the cast is unsuccessful (i.e. SelectedRow is not a row from TargetTable), blank/undefined is returned.

2 Likes
5

Thank you @Paul_Danyliuk, that is exactly what I was looking for. Unfortunately as you said, it’s deprecated and will be removed “soon”. Hopefully we get someone from the Coda team to chime in and let us know what the future plans for the function are.

6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.