Conditions for sending reminders about update requests

LizTo

I have a use case where we have a roster of people (~300) and every six months we send them an update request to review and update their information as needed. I have an automation set up to send reminders to complete the update request but am having a hard time finding suitable conditions to filter out people who have already completed the update request so that they don't keep receiving reminders if they've already completed it.

I can't use if a certain cell is blank or not, since not all of the cells are required and most of the cells are starting off NOT empty - just in need of reviewing and potentially updating.

Using the Modified Date column as a condition isn't foolproof either since we sometimes make changes to the roster from the back end, which will update the modified date for those rows even if the update request has not been completed.

I really need there to be a foolproof condition that is a binary yes/no the update request has been completed or not. This information is available when I click on the Update Requests for that row, but it's not in any cell that I can use as a condition for an automation.

Looking for some possible solutions or workarounds for this...

I suppose I could create a new, blank column every 6 months that is a required question and the cell will become not blank once the update request is submitted, but are there any cleaner or more automated options? And on second thought, I don't think I can make certain columns required in an update request, only in a form.

Thanks in advance for your ideas!

Dale Murphy

@LizTo My approach to such situations is to include a "Update Complete" column in the Update Request. The person completing the request checks that box to indicate they have completed the update.

A related workflow checks that column and writes a date to a "Date Updated" column in the sheet when it changes to checked. No reminders are sent if that Date Updated is within a certain timeframe.

Another workflow checks that date and when it is too far in the past, it clears the check from the Update Complete column. Then when your six month Update Request workflow runs, it sends the request to everyone without Update Complete checked. (I would recommend running that workflow monthly. You will start to get updates every month instead of every six months BUT there will be fewer reminders going out)

People will check the box when they realize it means fewer emails!

dm

LizTo

Hi @Dale Murphy, thanks for sharing your approach in such detail. That sounds like a simple enough and workable solution. I'll try to implement that and go from there. Thanks again!

Dale Murphy

@LizTo I trust your exploration is ... fruitful ... Reach out anytime.

Cheers,

dm