Alert recurrence that looks at a date field - possible?

05/04/21
Answered - Pending Review

I'm trying to set a recurring alert workflow to remind someone that they need to approve a submission. I have a "submission date" field and I would like a workflow that sends a reminder one week after submission date is reached and then weekly after that, as long as the status field is not changed to "approved".

I was able to set an alert to occur only ONCE, one week after the "submission date" field is reached.

I was also able to set a recurring WEEKLY alert, but I don't see an option to set the start date as the date located in the "submission date" field. The only recurring option I see is to send the reminder weekly, starting on a set date I have to enter from a calendar, which is not feasible as all of my rows will have different submission dates.

Any input on how to achieve this would be appreciated. I really don't want to set 20+ different workflows for each week...

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Insert a date type column which can be used to trigger the alert. Have the alert set to run on this date column using this formula:

    =IF([email protected] <> "Approved", [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row) * 7))


    The meat of this formula takes the current week number and subtracts the Submission Date week number from it to tell us how many weeks in the past the Submission Date is. We then multiply that by 7 to get the number of days between the Submission Date and the same day of the week for the current week and add it to the Submission Date.


    Finally we wrap that in an IF to only populate a date if the Status is not "Approved".

  • edited 05/04/21

    @Paul Newcome Got it to work but when it's a current submission and the week difference is 0, this formula populates the submission date. I don't want that to be the trigger, it needs to be a week after...

  • Heather DHeather D ✭✭✭✭✭

    @Melisa Dannhauser try moving one of the parenthesis at the end:

    =IF([email protected] <> "Approved", [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7)

  • @Paul Newcome Yup, figured that out and got it to work. But now, when it's a current submission and the week difference is 0, this formula populates the submission date. I don't want that to be the trigger, it needs to be a week after...

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Heather D Thanks for catching that.


    @Melisa Dannhauser Sorry about that parenthesis mix-up. Ugh. Those things can be such a pain sometimes. Let's give this a try...

    =IF(AND(WEEKNUMBER([Submission Date]@row) <> WEEKNUMBER(TODAY()), [email protected] <> "Approved"), [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7)

  • That works perfectly, thank you! I had accomplished the same with a more complicated formula so I always appreciate a shortcut!! This was mine:


    =IF(IF([Status]@row <> "Approved", [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7), "") = [Submission Date]@row, "", IF([Status]@row <> "Approved", [Submission Date]@row + ((WEEKNUMBER(TODAY()) - WEEKNUMBER([Submission Date]@row)) * 7))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

Sign In or Register to comment.