Notification Sent to Assigned Contact 4 Weeks Out...

Bob Lee

Hi there,

We run about 200 events per year and need to inform our trainers that they're assigned 4 weeks before the event start date. I know I can use the Reminder feature to send a reminder up to 2 weeks out, but that's not enough lead time.

 

The master sheet we use is initially populated as an "Event Request" using a webform. So my idea of inserting an equation into a column (event start date - 28 days) won't occur automatically, because the info is all populated via webform. 

 

Long-winded way of saying: I'm looking for an automated way of alerting folks 4 weeks out if they've been assigned to an event. Any ideas?

Dave B

This one is easy.   SmartSheet treats dates like Excel - as numbers.  So we can add and subtract from dates using integers to get a new date.

 

Let's assume your Event Date column is called Event Date.

 

Create a column called Dummy Date and format it as a date.  Then use the formula

 

=[EventDate]X - 30 where "X" is the row number.  This will create a date 30 days prior to the event date.  Then trigger your reminder to send ON the dummy date.

 

Bonus: If the Event Date changes so will the Dummy Date and therefore so too will your reminder.

Bob Lee

Yeah, good idea, but the sheet info is populated using a webform. So that equation isn't in the row when the info is populated in the sheet. I'm trying to make this function run in the background without needing to get in the sheet and manually add the equation each time a new event request comes in. 

 

I tried making the equation show up as the default value of the date in the webform, but that doesn't work. 

 

Any other ideas?

And thanks for the reply.

Melissa Ormsby

Not sure if this will work in your sheet, but here's what I do in mine...

 

Long story short, I also have a sheet with entries from a webform.  I have the new entries set to add to the bottom of the sheet.  I have a "Locked" row & an empty row below it.  Both have a column with a "Request Date" (which is entered from the webform) and a "Delivery Date" column which has the formula of fx=[Request Date]X + 7 (our delivery turnaround time is 1 week).

 

As new entries come in for the webform, they show up under the empty row (so we have two rows at the top of the sheet that are blank under the headings) and the Delivery Date automatically generates.

 

Now, for me our clients request a confirmation email at the bottom of the webform when they submit and they are the ones that I want to advise about the Delivery Date, so it includes the auto generated Delivery Date in the confirmation email - which is great!

 

However, I'm not sure that's going to work for you & you also need to include the field in the webform (I have just added "Help Text" to say, don't edit this date field).  I suppose you could set up a notification to your trainers and at least your Event Date would auto generate...

 

Hope that helps!

Dave B

Bob,

 

Sorry, I didn't read closely enough.  But, do the same I suggested above and,

 

1) Make Event Date a required field.

 

2) Include the Dummy Date in your webform but check "Hidden Field" for it.

 

This way it will auto-populate upon submission.

 

Dave

 

Dave B

Bob,

 

One other comment, like Melissa above, make sure your entries are added to the bottom of the sheet and that at least one row above the first webform entry contains a date in the Event Date cell and the formula exists in the Dummy Date field.  This way when the row is added via webform the formula will be inherited "into" the new row's Dummy Date cell from the existing row above it.

 

Sorry to be so fragmented in this solution - my kids have my brain scrambled!

 

Dave

 

Bob Andrews

Smartsheet will autofill formulas on new rows if the formula exists in the two rows above or below the new row. So add the formula to all your existing rows and when a new web form is submitted, the formula will autofill down.

 

http://help.smartsheet.com/customer/en/portal/articles/1641473-auto-filling-formulas-and-formatting