Canvas Control & filters

1

I’ve spent the better part of the last 72 hours just trying to connect two tables using a single filter. It’s a very basic setup.

I have two tables… Accounts and Opportunities. There will never be more than one account per brand, but a single account can have multiple opportunities. I’ve kept these tables separate since they serve different purposes, but they are connected through a relational column (Account Name).

I then created a canvas control (or whatever it’s called) with a search bar to filter by Account Name at the top of the page. The filter works on the Accounts table, but I need it to also apply to the Opportunities table. So, I wrote a formula to do that — and after trying about a hundred variations, every single one is throwing an error.

I’m doing what seems like a pretty basic formula:

Opportunities.[Related Account] = [Search Accounts Account Name]

But nothing is working. I even right clicked on ‘Related Account’ and just selected the canvas control, but that didn’t work either.

2

Hi Jordan,

Welcome to the community! Coda does have a steep learning curve, but the rewards once you master do really compensate the effort. Hang on and don’t hesitate to come here for support!

I’m not sure if you are aware that there are also ‘Relation Controls’, so that instead of typing the account name you can just select one or several from a dropdown. But I’m going to assume you knew about it and that you anyway prefer users to type in a search box, as the formulas would be a bit different.

Assuming your searchbox is called SearchAccounts and that your Accounts table contains a field called Name, these would be the filter formulas:

  • Accounts table: CurrentValue.Name.lower().ContainsText(SearchAccounts.Name.lower())
  • Opportunities table: CurrentValue.[Related Account].Name.lower().ContainsText(SearchAccounts.Name.lower())

Hope this helps,

Pablo