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

Ask for Help Formulas
#1

First of all, I am completely new to this so my apologies if I don’t use the correct language when explaining…
I am trying to work out how to populate a reference/drop down cell with data that has been filtered.

I have 3 tables:

  1. Worksheets
  2. Clients
  3. Billing_Groups

Worksheet columns: ID, Date, Billing_Group (referenced from Billing_Groups $Group_Acronym), Assigned_by, Activity

Client columns: ID, Name, Billing_Group (referenced from Worksheet $Billing_Group and can have multiple)

Billing_Groups columns: ID, Group_Name, Group_Acronym, Billing_Name

I am trying to fill the drop down cell in column “$Assigned_by” with “$Name” from table “Clients” but only the “$Names” who’s “$Billing_Group” matches the “$Billing_Group” cell from the “Worksheets” table.

I am assuming I set the “$Assigned_by” Column Type to “Reference” / Data from Table to “Clients” / Show Column to “Name” and then some formula under “Transform - Apply Formula to Data”?

Would this be the correct setup?
How would I structure the Formula to achieve this in Grist?

#2

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 selection will filter based on which group is selected in the first step. Then, enter your Activity. The Billing Group and Assigned By columns will auto populate.

image
image1858×612 141 KB

You can hide columns in each of these tables as desired.

If you need to assign a name to a group, select the group then add the Name in the Name column. It’ll correctly add that client to the billing group on all other tables.

In the Worksheets table, you’ll see the Group + Client column. This is a helper column that is a reference to our helper table, Billing Group + Client. These are required for setting up the first page.

I hope this helps! Please let us know if you have any questions.

#3

Thanks Natalie and team… that work around did the trick once I got my head around it :slight_smile:

2 Likes