Need slice or data of unique rows from table

[jmable]

I am trying to email unique users based on criteria. So I have a list of appointments on hold and some people need multiple appts so they're on the list multiple times like this:

Email: user1@gmail.com
Hold date range: Nov to Jan
Title: Appt 1

Email: user1@gmail.com
Hold date range: Nov to Jan
Title: Appt 2

I have a bot that I want to send an email to everyone with the "Hold date range" = "Nov to Jan" but I need the list to be UNIQUE so I don't email them twice.

In the slice I can only apply filters that return a Yes/No value.

I tried playing around with the code below in the Bot Filter Condition but I can't figure out how to get it to return more than the first Index one: 

[_THISROW] = INDEX( FILTER("Appointments" , [Hold Date Range] = "Nov to Jan") , 1 )

My brain isn't working beyond this so any insight would be greatly appreciated!

[WillowMobileSys · Accepted solution]

If your goal is to email Users only once, then your Bot should operate on the Users table...not the Appointments table.  I.e.  the Bot should select users who have an appointment with [Hold Date Range] = "Nov to Jan".

If you have the relationship between your Users table and Appointments table setup correctly, then you can do this....

Setup your Bot to run against the Users table.

In the Bot Event "Condition", create a filter expression like this:

COUNT(SELECT([Related Appointments][Appointment Key Value],  [Hold Date Range] = "Nov to Jan") > 0

NOTE: change [Appointment Key Value} to the name of the key column in your appointments table. 

In the Send Email step, you can now email the User with assurance you will be sending an email to them only once. 

You are also able to use the SELECT([Related Appointments][Appointment Key Value],  [Hold Date Range] = "Nov to Jan") expression to list ALL of the appointments for that user in this single email.

[jmable]

This AppSheet support community is the best! Thank you for being a genius and helping me!