How to filter inside a relation in Coda
Limiting the options for the users intelligently
Introduction
When you have a project and you have to distribute 5 tasks over 5 people and participants cannot share tasks, it is one unique task per participant. In Coda the rather well known solution you find below in two steps. The doc related to this blog, you find at the end.
First we get the employees who love to work via the options. I reused the Coda settings and then clicked on the f to access the formula editor. I would have written CurrentValue.task.contains(loves to work) to show how the item (named as CurrentValue) is linked via chaining to task. Coda removes the CurrentValue and makes it look like a stand alone formula, which it is not. It is a chained formula.
The second step is to limit inside this sub set based on who is already present. That you see below:
We got here via the logic below in which we combine the previous part with a new part.
My blog explaining this logic in more detail you find below⤵️.
I show this as an introduction; it helps to understand what follows.
Only one person for a specific role
It happens that for certain roles you can have multiple people and for specific roles only one person. We leave the context of workers and tasks behind us and we focus on the job of an assistant working at a legal firm. She has to link people to several roles and to the will they have written by the lawyer of the firm.
We start with the challenge that for the role Testator, we only can have one person, while for other roles (like beneficiaries) we can have multiple people. It means that in the settings we have to allow for multiple options. This you have to keep in mind, because we can only partly circumvent this option. We work in two steps. First we make it impossible to select a second, third etc contact when the role is set as Testator.
This means that once a contact is selected, you cannot add a second one. However — and here we face the limits of Coda settings — when you are inside the lists with contacts for the first time you can select multiple people. This native Coda setting we cannot disable via a function. To make sure people notice their mistake, we color the cell red in that specific case.
As you can see, the function has two conditions. First the role should be Testator and second the count of that role when greater or equal than one sets the selection as inactive via false, other ways it is active.
As a remainder, the inside of a filter we manipulate via options, should output true or false, always.
Roles that exclude each other
Mostly we find via chaining how the CurrentValue relates to a property. In my blog on working days, we could use the interval to get the right list. Here it is different. Since contacts can be related to different roles in different configurations, we have a highly dynamic situation we only can solve inside the table.
Once a will relates to a testator, that person cannot be beneficiary or executor at the same time for that will. The filter logic has thus two variables that relate to the contact: the roles and the will.
The roles
In my set up it is important to me that people without a profound Coda knowledge (PCK) can change the settings of these filters. By adding a column to the main table that defines the relations, it gets easier. Blank simply means that anything goes.
Before we integrate a code snippet, we write it in an extra column to check the result. Below you see that we filter on the wills and second we ask for contacts that do not fit the roles we excluded. This filter shows the outcome of choices made. As you can see: contacts related to the roles Accountant and Lawyer are not excluded.
We need a code snippet to direct outcomes and the above helps to get there. When I don’t see the solution directly, I often create a temporary column to clarify my thinking.
The CurrentValue is one of the items living in the column Contacts. We check if this one contains the value we just defined. By adding Not() we exclude these values in this list. The result is list of valid contacts.
This is it.
To see this logic in action, check out the doc you find below. It starts with the simple logic and shows the tables that served as example in this blog.
More than one route to take
There are multiple ways to solve this puzzle. When I discussed the issue with the great Coda connaisseur Joostmineur he showed me the power of Switch(). I would not have thought about it. The power of Switch() is that it permits you to think out loud and note what you say in your code editor.
In this context it would be great if Coda would turn makers into real makers and editors in real editors and not in what they are today: makers light. My set up is flexible, but when someone plays with the table that directs the relation, it easily goes wrong. Please Coda fix your foundations.
Not yet enough and interested in more insights on filters inside relations?
I hope this article was informative and helpful. Did it help you to solve a problem you unlike would have solved other ways? What about a donation?
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
