I’ve a bit of an issue that I could use some help on. I’m trying to use the Filter function to show a list of clients that appear in one table but not in another.
Here are the details:
- Client Table - contains a list of clients, codes, locations, etc.
- Schedule Table - each column represents different locations while the rows represent the clients that appear in the location specified.
- Unassigned Clients - A table that displays clients that are assigned to a location (based on the Client Table) but do not appear in the appropriate column of Schedule Table.
I’ve been toying with variates of
- [ClientTable].ClientName.filter([ClientTable].Location = (“Location”))
just to get a list of clients assigned to each location but I can’t get that part to work.
Any help would be greatly appreciated.
Okay, I got the first part sorted:
Now I just need to figure out how to take the results and filter out any values that appear in [ScheduleTable].Casper
Hello. This sounds doable, but can you give any additional insight on where do you want the list to appear (in a table?), and how the tables are current structured?
Also, couple of immediate thoughts:
For the Schedule Table, if you have the column names as location names, you cannot easily reference those column names like you can in Excel/Sheets. That may not be an issue for you, but making sure you are aware.
If you are looking to create a list of items, the filter would go before the item you are trying to filter.
e.g. Instead of [ClientTable].ClientName.filter([ClientTable].Location = (“Location”)), you would do
Thanks for the quick response.
This data is appearing in a table. Here are the table headers (or at least the relevant ones):
- ClientName | Location (multi select dropdown list) | StartDate | EndDate
- This table is connected to some buttons that create new rows
- SlotNumber | Location1 | Location2 | Location3
- This table is designed to allow the user to place all the assigned clients into the correct order. There is a completely different table that will use this order to create a formal calendar.
- Location1 | Location2 | Location3
- This is the table I’m currently working on. The idea is that this table will generally be empty. Anytime a new client is added to the ClientsTable, it will appear in the UnassignedClients table until the client is given a slot in the ScheduleTable in the appropriate column.
Someday I would love for the ability to reference the header rows, but until then, I’ve got to manually reference the headers.
Thanks for the additional clarity.
Tackling the most immediate question, I built an UnassignedClients table by making a view of the ClientTable and adding a filter. I’ll walk through my steps below, with pardons if any individual step is obvious:
- First I created a view of the Client table by clicking the plus in the corner, and selecting the Client table under “Insert View Of”
- On this view table, I renamed it to UnassignedClients. I then added the following formula to the filter:
For reference, this is what my Client table looked like:
Now, as clients are added to the client table, if no location is assigned, they automatically are added to the UnassignedClients table.
For more information on Views, Coda has a write up here.
That tip on chaining has really helped me out, thanks!