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

01/27/21
Accepted

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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.

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • 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 NewcomePaul 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?

    thinkspi.com

  • 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å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:


    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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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.

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • 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!

Sign In or Register to comment.