Duplicate Values causing multiple emails being sent with the same info
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
Best Answer
-
You would build it into your existing COUNTIFS
COUNTIFS([Holder email]:[Holder email], [Holder email]@row, Created:Created, @cell<= Created@row)
Answers
-
Are these in three separate columns? How are your automations set up? Are you able to provide screenshots for reference?
-
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.
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
@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"
-
You would build it into your existing COUNTIFS
COUNTIFS([Holder email]:[Holder email], [Holder email]@row, Created:Created, @cell<= Created@row)
-
@Paul Newcome Awesome that works like a charm. Your a life saver!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!