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

Hi there!
I have 1 sheet that containing emails, timesUsed, and other data.
I have a second sheet where other data is recorded, but where the email is used.

1st sheet looks like this(Apologies for hiding a lot. I would consider it sensitive data):

In the second sheet, I want to be able to create a field where Email is referenced, but filtered to only show emails that have a date, and the value 0.
So in this case, I’d get a short list of around 8 emails, where the value is 0, and there is a date.

I tried first to create a second widget to sheet 1 where the emails are pre-filtered so I simply need to reference this widget:

The problem is that I don’t know how to create a reference to THIS widget, rather than the other one.
Is what I am after possible? Am I over complicating this?

EDIT: The above seemed difficult to understand. So to explain it differently. I want a dropdown list of emails that have a date, and the value 0.
So when I click to select an email, the list only contains emails that fit that criteria rather than all emails.

Thank you in advance.

I would probably create a third column with an IF and AND…

IF(AND(Date<>" ",TimesUsed==0), “YES”, “NO”)

then I would create a new table where only rows where that new column = YES would be copied. (not filtered). This would be done through Python

And use that second table as a source for your third table, where you will get only emails with that condition.

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.

check the formula… when there is a date and check is 0, then it is the email, else is blank.

SO only where your conditions are ok, it will repeat the email.


1 Like

This is fantastic! Thank you so much, yeah definitely I was just over complicating it hehe. Did not consider it could be that simple.

Thank you!

So, this solution kind of works. It’s not perfect but it’s good enough!
So, essentially, TimesUsed is the number of times emails showing in Table2.
With this in mind, the moment the email is selected from the list, it will disappear from the list… Thus removing it again.

So my solution is to create… Another column.
The emails:

Emails table has columns:
Email: Type: text
TimesUsed: Type: Numeric | Formula: len(Temp.lookupRecords(ChooseEmail=$id))
Date: Type: Date
UnusedEmail: Type: any | Formula: if $TimesUsed == 0 and $Date != None: return $Email

The secondary sheet to action them:

Secondary column has:
ChooseEmail: type: Reference | Data from table: emails | Show Column: UnusedEmail
Email: Type: Text | Formula: $ChooseEmail.Email

Final look:

Thanks again for the help!

1 Like

oh, so it’s a circular logic? I was wondering why “timesused” was only 1 or 0. I guess it could simply be named “used”? As soon as it’s included once, it’s used so it disappears from the list.

Do you need to update the tables, maybe by some clicking elsewhere, or reloading? Or the moment you select that email, Grist already counts it was used once and it disappears from the list?

Originally it was a checkbox, but there might be some time in the future where I might use an email multiple times, so I wanted to have it counter just to future proof it :slight_smile:

Honestly it surprised me but it “just works”. I fully expected it wouldn’t work since the $ChooseEmail is Blank. Yet, selecting something from there updates its value to the last chosen email. Despite it showing as blank. No need to do anything besides selecting the email via $ChooseEmail and it all updates correctly.