Send email Alert to those who haven' submitted their status for the day
Hi,
I've been trying to figure out how to create an automation send Alert email to resources who haven't submit their status for the day. Can you help me or suggest on how am I able to achieve this?
Thank you.
Best Answer
-
I would suggest a helper Date type column with this formula...
=IF([Creation Date]@row = MAX(COLLECT([Creation Date]:[Creation Date], [Name Column]:[Name Column], [Name Column]@row)), DATEONLY([Creation Date]@row)
This will pull the date for the most recent submission for that person into the helper column. You can then adjust your alert to trigger only on rows where that column is not blank and is is the past.
Answers
-
How is your current sheet set up/how are you currently tracking when someone has made their update?
-
We create a report per team based from the master sheet for each manager per team.
-
Are you using an automated column, or are people manually entering their names when they make an update, or...?
-
People are submitting their entry via Form they don't have access to the sheet.
What I am trying to do is on the sheet under automation. I am creating a trigger to send a daily email if by 10am to the member who haven't done submitting their entry.
-
I understand what you are trying to do. To be able to provide a possible working solution though I would need to know more details about your actual process.
When the user submits the form, are they required to be logged in, are you capturing any kind of identifying data when they submit the form such as names and/or email?
-
No they are not required to be logged in. Yes they need to put name, team and other free form textboxes.
In the sheet upon submission, I have "Creation Date" column.
So my trigger looks like this below, upon testing even if I already have an entry I still receive the alert email:
Trigger: When date is reached, weekdays only, 10AM
Condition: Name (Is NOT one OF): Marvin
AND Creation Date (Is Today)
Action: Alert Someone (Send to specific People) Marvin
-
Hi @Marvin
To add to Paul's excellent advice/answer.
You could use the URL-query feature to create pre-filled links for each employee, and then they only need to add the other information, and you can link the submission to the right person and then use that to know who hasn't submitted.
More info:
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 help the Community by marking it as the accepted answer/helpful. 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 a helper Date type column with this formula...
=IF([Creation Date]@row = MAX(COLLECT([Creation Date]:[Creation Date], [Name Column]:[Name Column], [Name Column]@row)), DATEONLY([Creation Date]@row)
This will pull the date for the most recent submission for that person into the helper column. You can then adjust your alert to trigger only on rows where that column is not blank and is is the past.
-
Thanks Paul, it worked. I never thought of creating a Date helper column will be a good supplement to automation that is a game changer.
-
Happy to help. 👍️
-
Paul, could you explain the Date Helper a little more? I’m basically trying to do the same thing, as well as creating an automatic RYG symbol for if the create time was before a shift time. Thank you!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives