Workflow Automation Process

Andrea (F&Ops) Sarzosa

Hi Team,

I have the following process:

Monthly form is sent for inspections. A reminder should be sent to the inspectors to complete form, once they complete the form, the reminders should stop until next month. How can I automatize this process? I have think about different way but they are too complex and time consuming.

Genevieve P.

Hi Andrea,

I have an idea of how to do this; it would require some set up at the beginning but then only a brief check each month to update a TODAY formula.

The way I would achieve this would be as follows:

1. Create a sheet with one row per Inspector or form submission needed. This should include a Date column that the inspectors fill out, for the day that they are submitting the form.
2. Create an Update Request workflow (instead of sending a Form). Have this workflow run Monthly, on the 1st of the month, to go to the Contact in that row.
3. Create a Checkbox column with a formula to check and see if the "Last Date of Inspection" was last month. (If it is, check the box)
4. Set up a Reminder workflow that is triggered every day, if that box is checked meaning that the update request has not been completed. (Once an Update Request is filled out, it will also update the Today formula which will read that the month is now Today's Month).
5. Finally, set up a Copy Row workflow for when that "Last Date of Inspection" is changed, copy over to a different sheet. This will be your data sheet to collect each submission as separate rows.
6. In the sheet where the row is copied to, you can add a "Date Created" System Column to confirm the date that the row was copied over, or when the submission was made.

Although the submission coming in through the Update Request will change the Checked Box (with the TODAY formula), I would still suggest opening the sheet at the beginning of the month in case no one fills in the form early enough.

Here are some screen captures of what I've described:

Sheet Set Up with Formula to Check Month (note: the inspectors would be different for each row in this sheet.)

Monthly Update Request (using "When a date is reached" trigger with a Custom Recurrence of on the 1st of the Month)

Daily Reminder to complete Update Request (using the "When date is reached" trigger) if the last update request was completed LAST month, not this month - using the Condition that it will run if the Month checkbox is checked.

Copy Row Automation that when the new submission date is entered, it copies this line over to another sheet.

Finally, the formula used to check if the month is last month:

=IF(MONTH([Last Date of Inspection]@row) = MONTH(TODAY()) - 1, 1, 0)

I know this is a lot of information, so please let me know if there are any points of this you have questions about or would like clarification on. I'll post a second comment with all the relevant Help Center article links.