List options filtered by 2 variables

1

I’ve been trying to do a filtered list option for some time but haven’t been able to figure it out.

Here’s the idea

Table 1 is called Inbox, which uses the headings:

  • Task
  • Project (lookup of Project heading from Table 2)
  • Goal (filtered as Project.filter(Project=thisRow.Project).Goal
    - Objective (filtered as Project.filter(Project=thisRow.Project AND Goal=thisRow.Goal).Objective ===> this one isn’t working

Table 2 is called Project, which uses the headings:

  • Project
  • Goal
  • Objective

Any ideas on creating a list that’s filtered based on 2 variables?

2

Hey @Augustus_Loi ! Welcome to the Coda community :slight_smile:

When asking the community for help on something like this its always better if you create a sample document that you can share and show us what the problem is and where … with tables, schemas, formulas there is so much detail its easy to spend a lot of effort chasing the wrong thing.

I went ahead and tried to recreate your issue … and it’s working for me - can you take a look and let me know what the problem is?

3

From a different angle - why not use one table? Generally use one table, unless you absolutely have to split into a second table.

This is not an environment to strive for third normal form databases.

4

Thanks for the tip.

Here’s a sample of what I was going for.

Mainly it’s the goal and selector list.

5

Hey @Augustus_Loi you need to change the permissions so its viewable to the public.

6

Oh, of course! Sorry about that. Does it work now? Linked here as well: https://coda.io/@augustus-loi/sample

7

No it still doesn’t work as an embed or when I click through.

8

How about now? The settings say it’s discoverable by anyone and anyone on the internet with the link can edit.

9

So you’re trying to solve for the Goal and Objective column select returning something?

The problem lies in your Selectable Items formula for each. Lets take a look at Goal:

Projects.Filter(Project=thisRow.Project).Goal

Normally this would work, however you have multiple rows with the Display Column value of ‘Project 1’. So its trying to match the row which you selected in Project column to the multiple text values of 'Project ' in your Projects` table.

The solution is to tell it to match the value for Project column in the Project row you are selecting in your Tasks table by adding .Project to .Project:

Projects.Filter(Project=thisRow.Project.Project).Goal

10

Oh wow, that’s totally it! Didn’t realise you had to add that, which resolves it. Thanks!