Send Bi-Weekly Notification to Complete Form

aschneiderheinze1025

Program Developers will be required to submit a Smartsheet form every two weeks while a program is in Stage 1, Stage 2, or Stage 3 of the development process. Depending on the length of the development project, they may submit a few or several updates in any one stage.

Sheet 1: Collects form submissions

Sheet 2: Tracks the development process

I am using a notification (in Sheet 2) and form (in Sheet 1) instead of an Update Request because I want to collect keep a historical record of all submissions by the Program Developer across the stages for analysis and reporting. The notification will pull values from the [Status], [Stage], and [Program Code] columns in Sheet 2 to help them complete the form (since Stage and Program Code are required fields in the form). The form also includes two drop-down boxes and a few open-ended questions.

Here is what I want to figure out (and am looking for some "best practice" ideas):

1. I want to send this notification in Sheet 2 (with the column values listed above and the link to the form) every to weeks, starting two weeks on a business day after the development enters Stage 1 (i.e., [Stage] = 1) until [Status] = "Active" or "Archived Idea" or "Archived Program".
2. If after 3 business days, the Program Developer has not submitted the form (no new submission for that Program Code at that stage in Sheet 1), I want to send a reminder to complete the form (from Sheet 2). I can create a column in Sheet 2 that references the last submission date in Sheet 1.
3. If after 5 business days, the Program Developer has not submitted the form, I want to send an alert to the Lead Project Manager and Director of Operations.

What I am not sure how to set up is the date to send the notification over time and the automatic workflow (in Sheet 2) to automate this process.

For example, if a program enters Stage 1 on March 17, I need a column with a date 2 weeks after (or March 31) I assume to trigger the automated workflow. Then I need a column for 3 days later (April 3 since I am only counting business days) and one for 5 days later (April 7, since I only want to count business days) to send out reminder and alter to Lead Project Manager and Director of Operations. I'll probably need a column to reference Sheet 1 of the most current form submission for that Program Code and Stage to determine if there is a need to send a reminder and/or alert.

But then how do I update the first date column to be March 14 (the next date the notification goes out with link to form for Program Developer to submit another update), so long as [Stage] is 1, 2, or 3. The notifications stop if Stage is "Active" or "Archived Idea" or "Archived Program".

Columns I think I need

Sheet 1 (collects form submissions):

• Created (date form is submitted)
• Stage (1, 2, or 3)
• Program Code

Sheet 2 (tracks development)

• Program Code
• Status
• Stage (1, 2, 3, or "–" if [Status] is Active, Archived Idea, or Archived Program)
• Stage 1 (Start Date)
• Active (Start Date)
• Archived Idea (Date)
• Archived Program (Date)
• ?? Next Update (Date)
• ?? Reminder (Date)—-maybe [Next Update (Date)] + 3 business days?
• ?? Alert LPM/DoO (Date)—-maybe [Next Update (Date)] + 5 business days?
• ?? Form Submission Date (Most Recent) —- References column in Sheet 1 with the date of the most recent submission for that Program Code and Stage
• ?? Checkbox if Program Developer hasn't submitted a form on a date on or after [Next Update (Date)], is unchecked if Program Developer has submitted a form with the Program Code and Stage on or after the [Next Update (Date)]

Automated Workflows (in Sheet 2), which can be used for all Program Codes

• Send notification for every program to every Program Developer to complete update (with [Program Code], [Status], and [Stage] and link to form) if [Next Update (Date)] is TODAY
• Send reminder to Program Developer to complete update (submit form) if [Reminder (Date)] is TODAY and Checkbox is checked
• Send alert to LPM/DoO if [Alert (Date)] is TODAY and Checkbox is checked
• ?? Record date somehow of Next Update for that Program Code?, as long as [Stage] is 1, 2, or 3

Thanks for any insights and ideas!

Georgie

Hi @aschneiderheinze1025,

To avoid the circular reference error and ensure that the Next Update Date is based on the latest form submission, we could edit your formula to use the Last Submission Date column. I tested the following formula in my “Next Update” column and found that I didn’t need a “Next Update helper” column:

• =IF(TODAY() = [Stage 1 (start date)]@row, WORKDAY([Stage 1 (start date)]@row, 10), IF([PDU (Last Submission Date)]@row <> "", IF(AND(TODAY() <= [PDU (Last Submission Date)]@row, OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3")), WORKDAY([PDU (Last Submission Date)]@row, 10), IF(OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3"), WORKDAY([Stage 1 (start date)]@row, 10), "")), ""))

Since the last submission date column is returning the latest submission date for the row’s program code and stage, this formula will always return the date of 2 working weeks after the latest submission date in the row, which I believe is what you’re looking for.

I also updated the formula in my checkbox column to match yours, and my test sheet now looks like this:

Does that work for you, or am I still not quite on the right track?

Georgie

Georgie

Hi @aschneiderheinze1025,

Your ideas for the columns and workflows make sense to me. I set up some test sheets with the columns and workflows you suggested, and believe this would work well. Here’s what I did:

1. Set up sheet 1 with a Created date system column, Stage column and Program code column, as you suggested, then created a form and made some submissions - my sheet looks like this:
   
2. Added all your suggested columns in sheet 2, and used the following formulas to pull the required dates:
   1. Next update: =WORKDAY([Stage 1 (start date)]@row, 10)
   2. Reminder: =WORKDAY([Next Update]@row, 3)
   3. Alert: =WORKDAY([Next Update]@row, 5)
   4. Form Submission Date: =MAX(COLLECT({Created}, {Program Code}, [Program Code]@row, {Stage}, Stage@row))
      1. {Created} is the entire ‘Created’ column in sheet 1
      2. {Program Code} is the entire ‘Program Code’ column in sheet 1
      3. {Stage} is the entire ‘Stage’ column in sheet 1
   5. Checkbox for not submitted*: =IF(AND([Next Update]@row >= TODAY(), [Form Submission Date]@row = ""), 1, 0)
   6. *Note that the TODAY Function requires the sheet to be opened/saved every day for the function to update, but you can automate this with a workflow - see here: Automatically update the TODAY function in formulas. 
   7. I converted all of these to column formulas, and my sheet looks like this:
      
3. Set up 3 “Record a date” workflows on sheet 2, to record a date in the relevant column when the status is changed - see the example for the ‘Active’ status and column below:
   
   1. You can then duplicate the above for the “Archived Idea” and “Archived Program” statuses, changing the title, the status in the trigger box, and the column in the action box.
4. Set up 3 alert workflows using the date columns and the “When a date is reached trigger”. For example, to Notify Program developers to complete the form, my workflow looks like this:
   
   1. You can see that this runs using the Next Update date field and checks if that date is today. In the “Alert someone” box, you can either choose “Send to specific people” and list all the relevant email addresses, or choose to send to everyone shared to the sheet, or to all contacts in a contact column for each row that triggers the alert.
   2. You can then duplicate this workflow, changing the title, Date field in the action box, and Date field in the Condition box (and message if required), for your other 2 alerts.
   3. The curly braces {{ }} in the message are placeholders which pull the values from the columns in your sheet.

Check out the following help articles for more information on the functions used in the formulas in sheet 2.

• WORKDAY Function
• MAX Function
• COLLECT Function

Hope that helps with your setup - let me know if you have further questions!

Georgie

aschneiderheinze1025

Thanks @Georgie for all your time on this! I am not sure if the columns you came up with address the issue I'm having. Here is a screenshot:

I added the TEST columns so those columns in the sheet (but would require a lot of left scrolling) would be in the screenshot. (Note: I don't have actually have a Stage column, which is why I use the MID function to determine the current stage based on the Status column.)

So, PDU (Last Submission Date) is your Form Submission Date, the date on the other sheet on which the Program Developer submitted that last update for that Program Code at that Stage.

PDU (Next Update Date)-Helper is I think your Next Update column. This is the formula I have in that column:

=IF(TODAY() = [H-Start (Stage 1)]@row, WORKDAY([H-Start (Stage 1)]@row, 10), IF([PDU (Next Update Date)]@row <> "", IF(AND(TODAY() <= [PDU (Next Update Date)]@row, OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3")), [PDU (Next Update Date)]@row, IF(OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3"), WORKDAY([PDU (Next Update Date)]@row, 10), "")), ""))

The formula first checks to see if TODAY is the same as the date of when Stage 1 started (TEST Stage 1 in the screenshot). If it is, then the Next Update Date would be 10 workdays from today. If there is already a date in PDU (Next Update Date) AND TODAY is before PDU (Next Update Date), then just use the same date in PDU (Next Update Date). Otherwise, so long as the Stage is 1, 2, or 3 , the Next Update would be 10 workdays from PDU (Next Update Date). In other words, if PDU (Next Update Date) is 3/15/2025, and today is 3/11/2025 and the program is in Stage 2, return 3/15/2025 since the next update notification hasn't been sent yet. However, if today is 3/16/2025, then return 10 workdays after 3/15/2025.

For Program Code 5010 in the screenshot, the Next Update was 3/10/2025 (which I had entered manually), but since today is 3/11/2025, the notification should have been sent yesterday, then PDU (Next Update Date) would change to PDU (Next Update Date)-Helper, which would be 3/24/25, or 10 workdays after 3/10/2025.

On the other hand, for Program Code 5011, since the PDU (Next Update Date) is in the future on 3/19/2025, PDU (Next Update Date)-Helper is the same because its still in the future.

PDU (Submission Status) is like your Not Submitted by Next Update checkbox. Here is the formula I am using right now:

=IF(AND(OR(ISBLANK([PDU (Last Submission Date)]@row), AND(ISDATE([PDU (Last Submission Date)]@row), ISDATE([PDU (Next Update Date)]@row), [PDU (Last Submission Date)]@row < [PDU (Next Update Date)]@row)), OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3")), 1, 0)

Essentially, I want the box to be checked so I can trigger the automated workflow to send the notification to complete the update form. It should be checked if the Program Developer hasn't submitted an update form after the date in the Next Update Date and the Stage is still 1, 2, or 3. If TODAY is 3/15/25, and the Next Update Date is 3/20/25, and the Program Developer last submitted an update on 3/11/25 (and its stage 1, 2, or 3), the box should be checked so on 3/20/250, the automated workflow will run and a notification will be sent to complete update form.

PDU (Reminder Date) and PDU (Alert Date) are then based on PDU (Next Update).

This all appears to work on my end —- however, the problem is I currently manually entered the PDU (Next Update Date). It should be updated based on the date in PDU (Next Update) Helper; however, since I use PDU (Next Update Date) in the formula for PDU (Next Update) Helper, I get the #CIRCULAR REFERENCE error.

I need PDU (Next Update) to update every two weeks, based on when the Program Developer submitted the last update form and so long as the Stage is 1, 2, or 3. (If "Archived" or "Active", the Program Developer doesn't have to submit anymore update forms. And just using the date in PDU (Next Update Date)-Helper just causes the error, but if I don't have that PDU (Next Update), I can't use the formula I have in PDU (Next Update Date)-Helper.

And since I can only record the current date using the Record a Date automated workflow, rather changing the cell value based on another column (since I am working with DATE columns), I can't figure out a way to make this work via formulas.

Maybe I'm making this too complicated?

.

Georgie

Hi @aschneiderheinze1025,

To avoid the circular reference error and ensure that the Next Update Date is based on the latest form submission, we could edit your formula to use the Last Submission Date column. I tested the following formula in my “Next Update” column and found that I didn’t need a “Next Update helper” column:

• =IF(TODAY() = [Stage 1 (start date)]@row, WORKDAY([Stage 1 (start date)]@row, 10), IF([PDU (Last Submission Date)]@row <> "", IF(AND(TODAY() <= [PDU (Last Submission Date)]@row, OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3")), WORKDAY([PDU (Last Submission Date)]@row, 10), IF(OR(MID(Status@row, 7, 1) = "1", MID(Status@row, 7, 1) = "2", MID(Status@row, 7, 1) = "3"), WORKDAY([Stage 1 (start date)]@row, 10), "")), ""))

Since the last submission date column is returning the latest submission date for the row’s program code and stage, this formula will always return the date of 2 working weeks after the latest submission date in the row, which I believe is what you’re looking for.

I also updated the formula in my checkbox column to match yours, and my test sheet now looks like this:

Does that work for you, or am I still not quite on the right track?

Georgie