Automation: Email with Set Frequency Until Cell Value Changes, Trigger Date Re: to Form Submit Date
Hi!
I have a form being used as a ticketing solution for marketing requests. When users make a submission, one question is, "Have you provided all information?". Answer options are, "yes, begin with creative" or "no, more information will be entered later in Smartsheet".
So long as cell value is "no, more info will be entered later", I want an email to go out with x frequency (maybe every other day?) beginning the day (or so) after the submission is made, reminding them that creative doesn't begin until they change that cell value to "yes", and the reminder email does not stop until the cell value changes.
how can i accomplish this, please? all of the template automations don't seem to quite meet my need. i don't want to manually set up an automation every time i receive a request. TIA! :)
Best Answers
-
Thank you, @Paul Newcome I don't think this solves my problem, though. I only have a trigger based on a date for the "due date", "event date", and "date completed". I have the date column set up that auto captures when a submission is made, but it doesn't let me select that date column as an option for trigger point. I want the automation to be...an automation, haha, that i don't have to set up each time a new submission is made.
maybe i could make a hidden/dummy column with a formula that equals submission date stamp plus two days? (would you happen to be able to provide that formula?) and that date column would be available to me in the automation as a trigger point? seems like a workaround of sorts and that SS should have something built in as simple as what i'm trying to accomplish without this.
also, which automation template do you suggest? maybe i'm not looking at the right one...
-
I made the dummy date column with formula and used that as a trigger point. Works! Thank you again, @Paul Newcome for taking the time to respond.
Answers
-
You would set up your trigger to be based on a date. You can then click "Run Once" and switch it over to "Custom". From there you can set it to every two days and set your condition for when the cell in the sheet contains the "not yet" answer.
-
Thank you, @Paul Newcome I don't think this solves my problem, though. I only have a trigger based on a date for the "due date", "event date", and "date completed". I have the date column set up that auto captures when a submission is made, but it doesn't let me select that date column as an option for trigger point. I want the automation to be...an automation, haha, that i don't have to set up each time a new submission is made.
maybe i could make a hidden/dummy column with a formula that equals submission date stamp plus two days? (would you happen to be able to provide that formula?) and that date column would be available to me in the automation as a trigger point? seems like a workaround of sorts and that SS should have something built in as simple as what i'm trying to accomplish without this.
also, which automation template do you suggest? maybe i'm not looking at the right one...
-
I made the dummy date column with formula and used that as a trigger point. Works! Thank you again, @Paul Newcome for taking the time to respond.
-
Glad you got it working.
My suggestion was to adjust where it says in the Trigger "Run Once" to custom, setting your frequency (every 1 day for daily), then using the conditions of the Due Date being in the past and the dropdown being the "no" option. This would allow you to avoid the extra column.
-
that's just it, the due date is in the future. so if it's based on that, and i run it every 2 days from the due date, it begins on the due date. i want it to begin two days after the submission date. the scenario is they make an incomplete request (because they don't have/can't get all of the information at once):
example:
marketing request submission: july 19, 2022
marketing material due date: sept 5, 2022
i want them to start getting reminder emails beginning a day or two after they submit, so july 21-ish. and to keep getting them until they change the status of their submission to complete (or "yes"). ideally, it would let me use the auto-captured submission date stamp column, but it's not available as an option to use as a trigger point.
i hope i've explained this better. and ty again for your help.
-
Apologies. Condition would be when the submitted date is in the past. Or it may not be needed at all depending on how/when you capture the submitted date. Just have the condition of the answer being incomplete and set the trigger to run every other day (or whatever other frequency you want).
-
that would mean i have to set up the automation every time someone makes a submission:
-
Not necessarily. Are you capturing the submitter in a contact type column? If so, you can just set it to send to the person in that cell and it will run on your frequency, send each row to whoever is in that contact column, and stop running when the answer changes so that it no longer meets the condition.
-
so if not, "when a date is reached", what is the trigger to choose?
-
It would be just the Custom Recurrence. Then your condition would be where the dropdown is "no, more information will be entered later in Smartsheet".
