How to have filtered reference list?

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?


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

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

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.

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.


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:

1 Like

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

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):
So only the persons which are employees of the company should be shown in the (filtered) reference.

Here is the workaround proposed by Dmitry.

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.

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.

Check this thread RalphGL

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?


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

if $Company.Name == $CompanyAction:
    return $Employee
    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

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.

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