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.

[RalphGL]

I have the same issue, but - sorry - I didn’t understand your solution. It looks very complicated and I don’t understand how it solves the “filtered reference” issue.

For example I try to select (“reference to”) a person (from table “employee”) which (as contact person) is an employee of a company (in company table is a field with reference list to employees/persons):
https://docs.getgrist.com/pPM8qjuECJ7G/Sample-Filtered-Reference/p/1
So only the persons which are employees of the company should be shown in the (filtered) reference.

[GrittingGrister]

Here is the workaround proposed by Dmitry.
https://docs.getgrist.com/doc/pPM8qjuECJ7G~5MVyHrtVubq29xuUaHZhRN~31088/p/3

In short:

1. Create table with companies
2. Add Widget to page - Employees, Select by Company

Now, in the future you will be able to choose the company, and the list of employees will change, selected by the company highlighted.

[RalphGL]

I think there is a misunderstanding and we are trying different things. I want that the displayed list of persons is filtered and only the persons which are referenced to an other field of the actual table (i.e. the company field) should be selectable.

[Rogerio_Penna]

Check this thread RalphGL

How to create a filtered, referenced dropdown list to choose from?

there is a simpler way " You can accomplish that today by adding to the Employees table another column, say, Name_If_Manager with a formula like $Full_Name if $Role == "manager" else "". This will duplicate the Full_Name column but only for managers. Then in the Projects table, set your Managers column to point to table Employees and for “SHOW COLUMN” select Name_If_Manager. This will limit the dropdown to non-empty values, which happen to be only the managers." so here is your first table. …

[Rogerio_Penna]

hi Ralph! I have created an app that does something similar to what you want.

filtered… showing only employees from the italian company

filtered, showing only employees from the american company.

HOWEVER, you must click the update employees button every time you change the company.

if you don´t click the button, it will keep showing employees from the previously selected company

So, what is happening?

COMPANY TABLE

EMPLOYEE TABLE
CompanyAction column is filled by the ActionButton at Form table
Employee if Company has this formula

if $Company.Name == $CompanyAction:
    return $Employee
else:
    return " "

basically, if Company of the employee is the same as company in the current row of Form table when you clicked the button (it fills all rows of Employees table with the currently selected company) then it repeats the Employee Name.

The Form.Employee references EMPLOYEE IF COMPANY. And Reference drop downs DO NOT SHOW empty rows. So the Form.Employee dont show rows from Employees table where Company is different from CompanyAction

and here is the formula for the action button

[Rogerio_Penna]

there are no ideal ways to force the user to click the Update Employees button before trying to select employees
Here, I had two FORM cards showing different fields, with the Button Widget in the middle… so in theory, user will change Company THEN go to Button, click and then reach the part where he must select employee

You can also give warnings.

or
another way is check the company of the selected employee. If it’s different than the company selected, then it shows an ERROR message telling you to click the update button and reselect employee… and it also leaves the employee field in red (conditional formatting)

---

there is a BIG problem… whenever you re-click the update button, it changes the Employees referenced column leaving empty the rows where current CompanyName doesn´t match Employee company.

So, if I selected AmericanCompany, clicked the button, selected Jessie

Then create a new record, select ItalianCompany, click the button… in the other table, the reference column for Jessie will be empty (because she is AmericanCompany and the CompanyAction column shows ItalianCompany

It doesn´t really erase the record. In fact, you referenced the ID of the row, is just the column it’s looking for the dropdown that is empty.

If you are looking at a table or card list, every row where the previously selected employee doesn´t match the current company after the button was clicked will appear BLANK.

BUT you can have an extra column to show employee name (remember? The reference is to rowid!) instead of the column that changes all the time

what I did here is having a single card widget to create new records… the action button below it and on the right a form card list showing the referenced employee, so you see the employee even if it’s another company after you click the button