Duplicate Values causing multiple emails being sent with the same info

CoryG

So I have a sheet that is essentially tracking missed attendances. And we want to know when a miss happens each time. I have it set up currently to check for duplicates and then a box gets check for each miss up to 3. Then I have a workflow to notify different people based on the number of miss it was. My issue is that when it sends the workflow for 2 and 3 that it is sending the same email 2(or 3) times. Is there any way to make it so only one email is sent for the instance? Or is there a better way to count for a 2 or 3rd miss(as in the first miss still has the check for 1 miss but then only the second time is just checked for that row)? Right now my formulas are

=IF(COUNTIF([Holder email]:[Holder email], [Holder email]@row) = 1, 1) 1 miss

=IF(COUNTIF([Holder email]:[Holder email], [Holder email]@row) > 1, 1) 2nd miss

=IF(COUNTIF([Holder email]:[Holder email], [Holder email]@row) > 2, 1) 3rd miss

Paul Newcome

You would build it into your existing COUNTIFS

COUNTIFS([Holder email]:[Holder email], [Holder email]@row, Created:Created, @cell<= Created@row)

Paul Newcome

Are these in three separate columns? How are your automations set up? Are you able to provide screenshots for reference?

CoryG

https://community.smartsheet.com/discussion/comment/338927#Comment_338927

Yes they are in their own Columns. The Automations are set up so when the first box is checked to notify a group of people. And when the Second Box or Third Box is checked to do the same. So for instance when a user shows up twice on the list the 2nd miss box gets checked for both values and then 2 emails containing the same info are sent. The 3 columns tracking are all looking at the Holder email column. I have attached a screenshot with a general idea of how it works.

SShelp.png

Here are the formulas as well for each checkbox. These Checkboxes are what the workflows are based off for whos needs to be notified.

=IF(COUNTIF([Holder email]:[Holder email], [Holder email]@row) = 1, 1) 1 miss

=IF(COUNTIF([Holder email]:[Holder email], [Holder email]@row) > 1, 1) 2nd miss

=IF(COUNTIF([Holder email]:[Holder email], [Holder email]@row) > 2, 1) 3rd miss

Andrée Starå

Hi @CoryG

I hope you're well and safe!

You could use a Workflow to register when an email has been sent in another so-called helper column, so it only triggers once for each row.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

I would suggest inserting a system generated Created (date) type column. You can then leverage this in an IF/COUNTIFS to count as you have been but also count where this created date is less than or equal to the created date "@row". This will only check the box once in each column which in turn would only trigger a single email being sent out for each miss.

CoryG

@Paul Newcome I like that idea. Although I am having trouble getting the syntax correct. How exactly would it look? I have made the Created Column and it is just called "Created"

Paul Newcome

You would build it into your existing COUNTIFS

COUNTIFS([Holder email]:[Holder email], [Holder email]@row, Created:Created, @cell<= Created@row)

CoryG

@Paul Newcome Awesome that works like a charm. Your a life saver!!

Paul Newcome

Happy to help. 👍️