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

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?

1 Like

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.

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.

1 Like

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

2 Likes

Hi Natalie & team! Very interesting workaround above i’ve also been looking for, thanx for your efforts :)).
To be honest i did not succeed in truly understanding the underlying technique though, for my case is probably tiny-bit more complicated and the steps to go through are:

  1. Create a summary table, grouped by 3 fields altogether, 2 of which are grouped in summary-like table on the left, and leverage the third field on a separate table (to the right) to ultimately filter the populating table below (all mentioned fields are of one table with some of fields being dropdowns from other respective tables)
  2. Key fields are: “BuildingSites” which have “WorkTypes” which have “Workers”.
    So the intention is be able to choose BuildSite and see WorkTypes filtered, and then upon selecting the latter, have only few Workers filtered up, (given one WorkType may have several Workers) and
  3. See the resulting super-filtered list in the bottom table with the last empty row for adding another rec and taking pleasure in auto-populating magic.

What i’ve come up to so far is: One summary table grouped by all three fields which serves(the last grouped field) as a filter for bottom table, but it looks/feels somewhat unwieldy counter-intuitive & almost ugly :(.
Here the need is to just put the third filter-column (“Workers”) in a separate table and use it as a second step filter (just like your tricky solution above :)).
Begging for help ))


Some conditional styling i used for discerning among the heap

I finally got it, what the trick is about :).
So, there IS some workaround for dependent dropdowns - generally grouping/regrouping the same base table & using the grouped ones as filter(s) above for the normal (listing) one below.
Pardon my modest english ))

…it just took me a whIiile to figure it out with tiny bit of a headache :smile:

Hi Ellie!

I apologize, I completely missed your question when it was submitted! But glad you were able to figure it out :smiley:

1 Like