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

GrittingGrister · July 28, 2023, 10:40am

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.

Rogerio_Penna · July 28, 2023, 12:15pm

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.

Rogerio_Penna · July 28, 2023, 12:17pm

there is a simpler way

github.com/gristlabs/grist-core

Filtering possible values in reference lists

opened 11:51PM - 17 Feb 22 UTC

closed 04:33AM - 27 Apr 22 UTC

torwag

Sometimes, one would like to create a reference but only enable the selection of… a subset of that reference. This becomes particular useful if the list is otherwise very large. It would be nice, if one could assign a filter to that list first, so hat the entire list gets shorten. Example: In the table project, I would like to assign one or more managers to a project. I create a "Managers" column and make a reference list which points to my employee table. However, in that table, all employees and their different roles are listed. Obviously I do not want to assign Bob, the 17-year-old intern, to become the manager of a project. So what I need is to say Create a reference list from the table employee show name, but only if in table employee the column role is "manager".

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

GrittingGrister · July 28, 2023, 12:34pm

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!

GrittingGrister · July 28, 2023, 1:14pm

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!

Rogerio_Penna · July 28, 2023, 3:07pm

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?

GrittingGrister · July 28, 2023, 3:51pm

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

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.