Scheduling automatic reminders for Proof reviewers

Neil Egsgard

Here is a way to create reminders for Proof reviewers. This solution will:

• Send a reminder 5 days after the initial invite, if no one has responded to a Proof review request.
• Send a reminder 5 days after the last reminder, if no one has responded to a Proof review.
• Reset when a new Proof version is created.

Some useful modifications include:

• Reminding any one who has not responded to a Proof review request.
• Changing the reminder frequency for urgent Proofs or conditions.

To create this solution, follow these steps:

1. Create the Proof Info columns. These will reveal information such as who has responded, whose response is pending, and status of the proof.

1. Create these columns

• Invite sent date.
  • Date column.
    • Will be filled with automation when invites are sent.
• Workdays since invite sent.
  • Formula
  • =IF([Invite sent date]@row = "", "", NETWORKDAY([Invite sent date]@row, TODAY()) - 1)
• Send proof review reminder.
  • Trigger to send reminder notification. In this case the reminded is sent after 5 days if no one has responded.
  • Formula
  • =IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR([Workdays since reminder sent]@row = "", [Workdays since reminder sent]@row >= 5)), "Yes", "No")
• Last reminder sent.
  • Date column.
  • Will be filled with automation when a reminder is sent.
• Workdays since reminder sent.
  • Formula
  • =IF([Last reminder sent]@row = "", "", NETWORKDAY([Last reminder sent]@row, TODAY()) - 1)

1. Create two automations

I hope this helps.

Neil Egsgard

Business Solutions Architect

Southern Alberta Institute of Technology

PDunn

Hey can we just get the IT coding staff at Smartsheet to build this functionality into the Proofing system. Crazy that we have to create all these work arounds, and formulas and automations for something, a due date to review a proof, that should be a standard function of any proofing system.

PGilbert

Hello, I am getting a unparsable error for this code string:

• Send proof review reminder.Trigger to send reminder notification. In this case the reminded is sent after 5 days if no one has responded.
• Formula
• =IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR([Workdays since reminder sent]@row = "", [Workdays since reminder sent]@row >= 5)), "Yes", "No")

I have all the other columns added with any applicable formulas. Any edits I can make to get this to work?

Thank you for your help!

Neil Egsgard

@PGilbert , I setup a new sheet and started getting the same error that you did. I replaced [Workdays since reminder sent]@row = "" with ISBLANK([Workdays since reminder sent]@row) and it worked. Interestingly, I undid the changes and the original formula works. I tried both formulas a column formulas and cell formulas and both work.

The two formulas and the results are:

[Workdays since reminder sent]@row = ""

=IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR([Workdays since reminder sent]@row = "", [Workdays since reminder sent]@row >= 5)), "Yes", "No")

ISBLANK([Workdays since reminder sent]@row)

=IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR(ISBLANK([Workdays since reminder sent]@row), [Workdays since reminder sent]@row >= 5)), "Yes", "No")

Neil Egsgard

PGilbert

Hi Neil. So interesting! I had asked ChatGPT to fix the code before reaching out here, and ISBLANK was its first suggestion. However, that didn't work for me either. Neither does the original. Would you happen to have any other ideas?

Original, unparseable: =IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR([Workdays since reminder sent]@row = "", [Workdays since reminder sent]@row >= 5)), "Yes", "No")

Modified, unparseable: =IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR([ISBLANK([Workdays since reminder sent]@row), [Workdays since reminder sent]@row >= 5)), "Yes", "No")

Neil Egsgard

@PGilbert , could you share a copy of the sheet with me and remove any sensitive information?

PGilbert

@Neil Egsgard Sure thing. I have created a copy and removed most of the content (rows). I'm unsure how to share it with you though (help?).

This is the link: https://app.smartsheet.com/sheets/VVrHcXQCJh4Q9v39R5QQfWFcxH5fmGqFvvC2WQV1

I have no idea how to add you to the sheet without having your contact info.

I have only shared sheets within my organization. (I'm new to Smartsheet!) Thank you again.

PGilbert

@Neil Egsgard I noticed my column titles didn't exactly match the code, so I fixed that by changing "contract draft status" to "proof status." I still can't get it to work.

=IF(AND([Workdays since invite sent]@row >= 5, [Proof Status]@row = "In Review", OR([ISBLANK([Workdays since reminder sent]@row), [Workdays since reminder sent]@row >= 5)), "Yes", "No")

PGilbert

@Neil Egsgard I'm so sorry. It looks like my company doesn't allow me to share my sheet outside of my domain.

Neil Egsgard

@PGilbert , will try to share from my end.

Neil Egsgard

@PGilbert , remove the "[" in front of ISBLANK.

When troubleshooting formulas, If the Smartsheet formula AI or Microsoft Copilot or another AI does not help solve the problem, remove each part of the statement and add them back in one at a time until the formula breaks. This allows you to identify where the problem is located. In this example:

1. =IF(AND([Workdays since invite sent]@row >= 5, [Proof Status]@row = "In Review", OR([ISBLANK([Workdays since reminder sent]@row), [Workdays since reminder sent]@row >= 5)), "Yes", "No")
   1. If I remove the crossed out expression, the formula works so I know the problem is in the crossed out expression.
2. The correct replacement is
   1. ISBLANK([Workdays since reminder sent]@row)
3. The correct entire formula is
   1. =IF(AND([Workdays since invite sent]@row >= 5, [Proof status]@row = "In Review", OR(ISBLANK([Workdays since reminder sent]@row), [Workdays since reminder sent]@row >= 5)), "Yes", "No")

Neil

PGilbert

@Neil Egsgard Thank you so much. I actually copied/pasted in what you had in 3. and it worked like a charm!!! Great tip for gen AI too. Have a great day.