Multiple Trigger Points Based on Date

Lila De Vera

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.

Capture.PNG

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?

Genevieve P.

Here's the Annual formula:

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

This would only check the box if it's set to "Annually" and if the Month and Day are the same as the End Date (not taking into account the year).

Then you can clone the Month workflow and create the Annual version:

Let me know if this makes sense!

Genevieve

Genevieve P.

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

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

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.

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.

@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.

Genevieve P.

Here's the Annual formula:

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

This would only check the box if it's set to "Annually" and if the Month and Day are the same as the End Date (not taking into account the year).

Then you can clone the Month workflow and create the Annual version:

Let me know if this makes sense!

Genevieve

Lila De Vera

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

@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.

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