Reference Lookup - Only Show Contents From Select Spreadsheet

[Jennifer_Bledsoe]

Hello,

I have a column that will look up the ID number. However, I only want to show the ID numbers belonging to the client. Does anyone know how to accomplish this?

[jojo-grist]

Hi Jennifer,

There is currently no way in Grist to limit what references are shown in a reference column based on another field. This idea of dependent references has come up before and is something we’d like to do in the future. I’ll add you as a +1 vote for the feature!

[Jennifer_Bledsoe]

Okay, thank you for letting me know

[Rogerio_Penna]

there are workarounds… not ideal

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

There is another active thread with the same question. I did a demonstration for THAT thread, it may help you. It uses ACTION BUTTONS.

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

[Jennifer_Bledsoe]

Thank you for this information.

[Rogerio_Penna]

there is additional tests I did on this thread, check it up

How to have filtered reference list?

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 [image] You can also give warnings. [image] or another way is check the company of the selected employee. If it’s different than the company selected, then it shows an ER…

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