Send email Alert to those who haven' submitted their status for the day

Marvin

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.

Paul Newcome

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.

Paul Newcome

How is your current sheet set up/how are you currently tracking when someone has made their update?

Marvin

We create a report per team based from the master sheet for each manager per team.

Paul Newcome

Are you using an automated column, or are people manually entering their names when they make an update, or...?

Marvin

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.

Paul Newcome

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?

Marvin

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

Andrée Starå

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:

Use a URL Query String to Specify Different Default Values for a Form | Smartsheet Learning Center

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

When you create a form in Smartsheet, one way you can distribute that form is with a URL.

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!

Paul Newcome

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.

Marvin

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.

Paul Newcome

Happy to help. 👍️

Ginny Shoemaker

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!