Help needed on advanced Filter function

1

Greetings,

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.

2

Okay, I got the first part sorted:

  • [ClientsTable].filter(Location.contains(“Casper”)).Nth(RowId(thisRow))

Now I just need to figure out how to take the results and filter out any values that appear in [ScheduleTable].Casper

3

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:

  1. 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.

  2. 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

[ClientTable].Filter(Location=X).ClientName
1 Like
4

Hi Joseph,

Thanks for the quick response.

This data is appearing in a table. Here are the table headers (or at least the relevant ones):

ClientTable

  • ClientName | Location (multi select dropdown list) | StartDate | EndDate
  • This table is connected to some buttons that create new rows

ScheduleTable

  • 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.

UnassignedClients

  • 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.

5

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:

  1. 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”

image
image.png543×582 19.3 KB

  1. On this view table, I renamed it to UnassignedClients. I then added the following formula to the filter:

image
image.png847×366 14.9 KB

For reference, this is what my Client table looked like:

image

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.

6

That tip on chaining has really helped me out, thanks!

7

Glad to hear it helped!