Auto Reminders

Shane Cornwell
Shane Cornwell ✭✭
in Formulas and Functions

Hello,

We are setting up a workflow in smartsheet that once submitted via form, will flow from one approver to the next. There is no standard as for when the entries will be submitted. I need to set up a reminder for each approver so after 48 hours, if they have not approved, they receive a reminder that an action is pending their review.

I see how to set up reminders for a row or based on specific dates, but is there a way to set up reminders for a sheet to send out after 48 hours of an action being sent to a person?

Tags:
· Share on FacebookShare on Twitter

Answers

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭

    Hello @Shane Cornwell

    If I'm understanding your question correctly, perhaps something like this may work:

    Step 1

    Create a column with a Follow Up Date. Then the formula below will check for "Approved" or "Not Approved"

    If "Not Approved" it will add 2 workdays from the date.

    =IF([Approval Status]@row = "Not Approved", WORKDAY(Date@row, +2), "")

    image.png

    Step 2

    Enter the Manage Workflows screen and trigger as follows in my screen shot below

    (you can modify this to suit your needs better if needed)


    image.png


    Let me know if this works for you!

    · Share on FacebookShare on Twitter
  • Shane Cornwell
    Shane Cornwell ✭✭

    Thanks for both the quick answer and the knowledge. However, I am having a little trouble with the date, everything is coming up UNPARESABLE

    image.png


    I have also tried it without the Director Received in front of the Date but there was no change in the result. Any ideas?

    · Share on FacebookShare on Twitter
  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    edited 09/08/22

    I believe you will need brackets [ ] around [Director Received Date] example below:

    =IF([Director Review Status]@row = "Not Started", WORKDAY([Director Received Date]@row, +2), "")

    Also note, if status is "Not Started" and you don't have a Date in the cell where your formula is searching for, you will get an error for #INVALID DATA TYPE. This formula requires a date to be listed when Not Started is there.

    If you are expecting a blank field, and want to avoid the error, you use this formula to leave it blank until a date is entered:

    =IFERROR(IF([Director Review Status]@row = "Not Started", WORKDAY([Director Received Date]@row, +2), ""), "")

    Lastly, make sure your Formula Result Column Properties are set to Column Type: DATE

    · Share on FacebookShare on Twitter
  • Shane Cornwell
    Shane Cornwell ✭✭

    Thanks again for your assistance, I was able to get the formula entered and the workflows set up:

    image.png


    image.png

    A couple of follow up questions, and I can be available for a call if it would be more efficient:

    1) We are using a form to enter data into the sheet. How do I set up the sheet so the formula in the date columns show up when a new entry is added?

    2) I tried to drag the formulas down, but when I did, new entries were entered on rows that had not yet been updated to show the formula in the needed columns

    3) Automation is set up as shown above, but it does not appear to be sending the updates as intended. Per my screenshot, updates should have been sent yesterday, but according to the automated rule, it has not yet been run:

    image.png


    Any thoughts/insights/feedback would be greatly appreciated.

    · Share on FacebookShare on Twitter
  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    edited 10/07/22

    *double post*

    · Share on FacebookShare on Twitter
  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭

    You can try a few things:

    1: Make sure the contact that is assigned to be notified has the correct email address, I usually assign Contacts in a Column for this, and in that column I select the people who need to be notified.

    2: Under your Conditions, try "Alert Someone" instead of "Request Approval".

    I hope this helps.

    · Share on FacebookShare on Twitter
  • Shane Cornwell
    Shane Cornwell ✭✭

    Thanks I will try those steps. Any thought on the issues 1 & 2 I mentioned?

    · Share on FacebookShare on Twitter
  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/346538#Comment_346538


    Q: 1) We are using a form to enter data into the sheet. How do I set up the sheet so the formula in the date columns show up when a new entry is added?

    A: I'm not sure what the entire scope of the criteria is involving. You may be able to add an IF formula see if the cell is not blank to populate the dates, or whatever criteria you need. There's a lot of resources online to show you how to formulate IF's to best suit your needs.

    2) I tried to drag the formulas down, but when I did, new entries were entered on rows that had not yet been updated to show the formula in the needed columns

    A: I'm not sure what you mean here. I wish I could look into it more, my workflow has increased quite a bit at my day job. Unfortunately I don't have more bandwidth to spare as I'm a volunteer here on the Smartsheet Community.

    I hope you're able to get it to work as you need!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!