How to have filtered reference list?

[GrittingGrister]

Hi there!
I have pages:
Clients (Rows: Client, ContactMethod, SignupDate etc)
This is just a list of the clients.

ClientProjects(Rows: Client(Reference Clients.Client), Project)
This is a list of the clients projects. So Client has many duplicates for each of the different projects for that Client

Jobs(Rows: Client(Referenced from Clients.Client), Project(Reference ClientProjects.Project), Description, CompletionDate, etc)
Right now Project obviously shows ALL Projects. I want it to be a list of only the chosen Client’s projects.
How can this be done?

Example:

Clients:
Client, ContactMethod, SignupDate
Ron, Owl, 2001
Harry, Owl, 2001
Hermione, Owl, 2001

ClientProjects:
Client, Project
Ron, FinishSchoolYear
Ron, MakeFriends
Hermione, Study
Hermione, AbuseTime
Harry, FinishSchoolYear
Harry, DarkLordMurder
Harry, MakeFriends

Jobs:
Client, Project, Description, CompletionDate
Harry, DarkLordMurder, Try not to die while the Darklord does things, 2007(?)

In this example, when I am filling in DarkLordMurder for Harry’s Job, I see ALL the various Clients Projects. I want to ONLY see Harry’s (FinishSchoolYear, DarkLordMurder, MakeFriends). How can I do this?

P.S. Apologies for the terrible example. I lack creativity today.

[GrittingGrister]

From here: Function reference - Grist Help Center
I recon what I need is a separate column that displays a list of all records in ClientProjects that match Client.
I tried creating a new column in Jobs called FilteredProjects. Reference List > ClientProjects > Project
Formula: ClientProjects.lookupRecords(Client=$Client.Client).Page
But this just return nothing. I’m quite confused.

I created a test table where it spit out a list [[1, 2, 3]], at which point I don’t understand how I can convert that to a list to choose from.

[dmitry-grist]

Hello there!

The reason you are struggling is that what you are looking for isn’t available in Grist, at least not yet. There have been other requests for it. Multiple overlapping wishes are summarized and linked in More context in autocomplete results · Issue #249 · gristlabs/grist-core · GitHub. There is a chance this will get implemented before long. There are some design proposals floating around.

Meanwhile, one workaround suggestion that may work for you too is described in this response to a similar request:

Populating a reference/drop down cell with filtered data from another table

Hello and welcome to the community! We don’t yet have the feature you are looking for here but it’s a great idea that we would like to implement. Our team was able to come up with a workaround for you. Check it out at the link below: https://public.getgrist.com/xe274zrR9ZXy/Community-657/m/fork On the first page, ‘By Billing Group’, you will be able to add worksheet items based on group and client. First, select your billing group. Then, you’ll select the ‘Assigned By’ Name. The name selecti…

[GrittingGrister]

Thank you! Glad there’s a workaround at least! Have a good one Dmitry.