Multiple Trigger Points Based on Date

Options

Hi everyone,

My organization has multiple directives across multiple departments. I would like to set up automatic notifications to people who are involved with a directive. I have set workflows based on the due date and it works. Some directives have different reporting frequencies (annually, monthly, quarterly, no frequency). everything is based on the FINAL DUE DATE, depending on the reporting frequency an individual will get a reminder X days before the final due date. What I forgot to administer was the reoccurring notification. I understand I can do this under custom, but it's not exactly what i'm looking for.



What I would like is this: If reporting frequency equals monthly then it will send an email notification X days before the next months due date up until the final due date. (i.e directive is submitted September 1 and it is monthly frequency up until January 1, then an individual would receive an email notification X days before October 1, November 1, December 1, and January 1).


Is this possible? any ideas?

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Lila De Vera

    Based on the way your sheet is set up, I would actually use the Custom function under the Date trigger to set recurring alerts, and then use the Condition block to specify each frequency. This would mean creating multiple alerts, one per frequency. Here are some examples:


    If this wouldn't work for you, would you mind explaining a little further?

    Thanks!

    Genevieve

  • Lila De Vera
    Lila De Vera ✭✭✭✭✭
    edited 09/08/20
    Options

    Hi @Genevieve P , thank you for the response. The issue I have is that the reminder must occur monthly, quarterly, or annually before a set date. For example, on the top row a Families Directive was issued Sept 4, 2020 and is Due Sept 6, 2025 with a annual reporting frequency. Therefore, I would like to have an email reminder sent every year up to the Final Due up until September 6, 2025.

    Directives will be added frequently, and It would be ideal if I can organize this and have it send reminders automatically, If i need to create multiple other columns and hide them that works too!

    I hope this makes sense.


  • Lila De Vera
    Lila De Vera ✭✭✭✭✭
    Options

    Could this work and would it repeat automatically if a row is added when Agriculture is the department and the reporting frequency is set to quarterly? The start date is irrelevant because the first reporting date is X after the directive is set (i.e would the above workflow work for all new rows that are added?)

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Lila De Vera

    The issue with Monthly, Quarterly, or Annually repeating alerts is that they need a set day of the week to run; this date can't be adjusted per-row.

    For example, you could set up a monthly alert, but it won't take into account the date in that date column - it will run on the same day that month for all month rows - ex. the 1st of the Month (versus being varied for the 6th, 8th, 10th, depending on the row).

    One way you could get around this would be with a helper column and some additional workflows... this is what I'm thinking:

    MONTHLY -

    I have a Helper Column with a formula to either return 1, 15, or 25, depending on where the date falls in the month. Then you could set up 3 workflows:

    • A workflow that sends out on the 1st of each month
    • A workflow that sends out on the 15th of each month
    • A workflow that sends out on the 25th of each month

    Then it could use this "Day of the Month" column as part of the criteria. So, if the Reporting Frequency is Monthly and if the Day of Month is 1, then it sends on the 1st. If it's Monthly and the day is 15, etc. Does that make sense?

    It wouldn't be exactly on the same day of the month as your Final Due Date (you could also have one that's just a standard "send an alert one month before this date" as a one-off, as well, so it's on the proper day of the week).

    If you like this idea but you want more variables, you can create more IF statements to create other days... 1, 5, 10, etc, but each one would need its own separate workflow (which is why I limited it to just 3).


    ANNUALLY -

    You'll notice I also have a Month of Year column - this uses the following formula:

    =MONTH([Final Due Date]@row)

    This is then to filter your Yearly alerts. Here you'd need to set up 12 Yearly workflows (unless you don't mind grouping months together). Ex:

    • A workflow that sends on the 1st of January if the Reporting Frequency is Annually and the Month of Year is 1.
    • A workflow that sends on the 1st of February if the Reporting Frequency is Annually and the Month of Year is 2.... etc


    YOUR IMAGE -

    The workflow you have pictured would trigger for all new rows with Agriculture and Quarterly, however it would send in 10 Tuesdays from now for all those rows. So, if in 9 weeks from now you added a new row, the first alert would send 1 with that row just week after (instead of 10 from when the row was added). Does that make sense?

    You would get a Quarterly alert for all that say Quarterly, but it wouldn't read your End Date column to adjust based on that.


    Let me know if you think the formulas above would work for you, or if you have any questions about them!

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    @Lila De Vera

    I've though of another idea! Instead of trying to build this out with automations, we could use Helper Checkbox columns for each of these more complicated workflows!

    For example, instead of having the automation look for a date, we could have it look to see if this Helper Checkbox is checked:

    =IF(AND([Reporting Frequency]@row = "Monthly", DAY(TODAY()) = DAY([Final Due Date]@row)), 1, 0)

    This column would just be for MONTHLY alerts. It look to make sure the Reporting Frequency is Monthly, and then it checks what day of the month the Due Date is. If that day is the same as today, it checks the box. Then set up your automation to look at that column, and make sure the due date is in the future:

    This would mean you only need ONE workflow for all your monthly tasks (but an additional helper column). Does that make sense? The only issue is that we're using the TODAY() function, so this sheet would need to be opened and saved every day. If you won't be touching the sheet each day, there are ways to auto-refresh the Today function. Let me know if this is the case and I can go into further detail for you.

    I'll craft what to do for the Annual workflow and post another comment.

  • Lila De Vera
    Lila De Vera ✭✭✭✭✭
    Options

    Hi @Genevieve P, this is incredible thank you. I'm going to take a day (or two) to digest this, and add your second solution to my spread sheet!

  • Lila De Vera
    Lila De Vera ✭✭✭✭✭
    Options

    @Genevieve P, thank you very much for the recommendations. I used your helper checkbox column and blew it up so to speak. I want to post some pictures in case other people could benefit from this! I've created multiple checkbox columns for projects that occur over multiple years and multiple quarters.


  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Oh, wow!!! Well-done! This is amazing. Thank you for sharing!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!