A few of my column formulas have suddenly broken. I haven’t quite figured out what the problem is, but it could be this… I’ll try to explain the best I can:
Let’s say I have an Orders table with a relation column Company linked to table Companies.
In the Companies table I have the column Contacts linked to a Contacts table.
In the Orders table I have created a Linked Contacts column, which uses a formula: [Company].[Contacts]
Now this works fine, I get a list of contacts related to the order. The contacts show up normally in the Orders table column as chips that I can hover over and see details from the Contacts row.
If I now add a Contact emails column in Orders with a formula that tries to access the properties of the contacts in the Linked Contacts column, I get into problems.
My old formulas that used to work now give errors, usually “unexpected dot operator” error.
If I try to create a new formula, I can no more access any of the properties of the contacts.
I can go around this by creating a formula something like this: [Contacts].Filter(CurrentValue.In([thisRow.Linked Contacts]). But it seems quite silly to have to do a search on the Contacts table to get the rows that I already have in the Linked Contacts column.
Has anyone else encountered this? Is this a bug or a feature?
Hi Piet, I set up my example in a new doc… and it works as expected. So I can not reproduce the original problem here.
I cannot share the production doc where the problem is happening, but I simplified the problematic formula and took screenshots.
The Subscriptions column I reference here is a relation column that allows multiple values. I try to get the last referenced value. For reasons unknown, Coda outputs a text value rather than a row ref and the named currentValue Last Subscription becomes text. Understandably there is no Start date property for the text value, so I get the “Unexpected dot operator” error.
With my “fix” I get different problem. Here I filter the T Subscriptions table to get the row that is referenced last on this row’s Subscription column. This time Coda thinks the Filter(...) function and consequently the Last Subscription value outputs a table!
While working on these screenshots, I tried to edit the Subscriptions column and just re-selected the same table for the relation. This caused a small change:
The icon for the column reference changed from text to row… Unfortunately that did not fix the formula. The CurrentValue still outputs either a text type or a table type.
There’s a good chance I’m overlooking something and have caused this problem myself, but I just can’t figure out what’s wrong or how to go forward with troubleshooting.
I don’t exactly understand why ListCombine is needed here, but I’m happy the errors are gone. As often is the case, problems start to solve themselves when you spend the time and effort to explain them.
In my scenario I have believed it was related to Coda automatically changing the type of a column (see this post) because what is happening is a relation column in a ‘helper’ table (therefore there is only ever one row in the table) is being changed to a text column, which is what causes the “unexpected dot operator” error. Manually changing the column back to a relation fixes it.
In more detail -
I have a customers table, and a “send email” helper table with a “recipient” column that is a relation to the customers table.
Thus I can click a “send email” button in the customers table and a row is added to the helper table, the recipient column in the helper table is set to the customer (ie. "thisRow), and the new row is opened. But for some reason, which I have presumed to be related to the column “auto-typing” that coda does () the recipient column in the helper table gets automatically changed to a text column, meaning there is no reference back to the customer and so the send email button which has a reference to “Recipient.Email Address” shows the “unexpected dot operator” error.
Ironically, I got around the problem by setting the value of the recipient column to “thisRow.CustomerName.ToText()” which causes the column to match the text to a customer, and doesn’t auto-type the column to Text!!
That is quite hilarious. I’ve gotten used to trying ListCombine() whenever I have this kind of typing issues, but I have to add ToText() to my toolbox as well. Thanks for the tip!
Unfortunately that did not fix the formula. The CurrentValue still outputs either a text type or a table type.
) the recipient column in the helper table gets automatically changed to a text column, meaning there is no reference back to the customer and so the send email button which has a reference to “Recipient.Email Address” shows the “unexpected dot operator” error.
I’ve gotten used to trying