Hey all, I am having a heck of a time with filters. I’ve shared an image that is super easy to follow, so please use that as a reference point.
To make a long story short, I have two tables – accounts and opportunities. These are two different tables but each have ‘account name’ as a column. I applied a relational column to opportunities as Account is the highest level in hierarchy.
I created a canvas filter on the accounts table that lets me search by account. It works flawlessly. Next, I need to ensure that canvas filter applies to opportunities, too.
I selected ‘filter’, I added a formula, and I think I tried something like account.name=[insert canvas filter name]. I’ve tried about 100 different variations and cannot figure it out. Oddly, I have done this before so I know it’s possible.
an analogy of the theory behind this error is comparing “apples to apples” so i.e.
Need to type match the two columns e.g. “account name” needs a .ToText() appended to it to match the “Search Accounts” which is a text column. Formula should look like
account name.ToText() = Search Accounts
(note: no need for the Opportunities table chip before “Search Accounts”)
It looks like you are missing a dot there
Unfortunately using emojis as the first character in column names makes it very difficult to write formulas, because the editor won’t suggest them when you start typing them.
There are several ways to achieve what you are aiming for, but you must understand that when you are setting up a table’s filter in Options > Filter, you are already in the context of the table, so you must reference columns directly, rather than using the Table.Column notation. Referencing the table you are already in will just confuse Coda.
In your case, the filter formulaAccount_Name.Matches(Search_Accounts) should work. The Matches() formula works better for a search-type filter because partial matches work. The = sign only accepts identical matches.
Basically, you need three elements to make your desired setup work:
A text control, that can be referenced in filters.
A filter in the Accounts table to only show columns where Account_Name.Matches(Text_Control).
A filter in the Opportunities table to only show columns were Account.Matches(Text_Control).
(You should replace my generic column and control names with your real column and control names)
Just for future doc making purposes - as per your screenshot… why this was an error was because there was a dot / fullstop missing between Account Name and ToText().
Within the formula bar, there are hints to point you in the right direction when there are formula errors. The 1st one in your screenshot is there is a red squiggly line under ToText(). The next hint is at the bottom of screenshot it says “value.ToText()” so Coda is telling you thats how they want their formula written.
Also why i advised there was no need for the Opportunities before Account Name was because its within the filter so a reference to the table is not required.
