How Can I trigger an email notification sent on a date along with a contact entered on a form?

g2smsht

I am trying to create an input form that allows an event to be entered, along with a date, if the event is recurring, and the contact to received the event notification. I am trying to do this using automation, but can't figure out if this is supported or not.

Thanks for any help,

g2smsht

Erin Horiuchi Green

• Hello @g2smsht ,
  
  It is very possible to create a form with your below requirements.
  
  1) Created By ⇒ System field not editable
  2) Created Date ⇒ System field not editable
  3) Request ID ⇒ Auto number field not editable w/ format flexibility

• 4) Event Name ⇒ Text/Number field editable but should have naming convention guidelines for reporting ease
  5) Event Date ⇒ Date field
  6) Event Recurring ⇒ Dropdown list field as 1/0 or Y/N or T/F or etc.
  7) Period ⇒ Dropdown list field as Weekly, Monthly, Quarterly, Trimester, Semi Annual, Annual, etc.
  8) Day ⇒ Text/Number field editable. Or, Dropdown list to prevent data entry errors.
  9) Contact ⇒ Contact list field
  
  Only the Owner and assigned Administrators have the ability to add, delete, hide and update Columns and data…if the Requestor does not have access to the backend Smartsheet post submission. I recommend a Dynamic View where the Requestor only sees their own submissions.
  
  Only the Owner and assigned Administrator can create, activate and deactivate Automation. Be cognizant of the Automation schedule and the limits of the number of Automations that can run concurrently before destabilization.
  
  Please note that all forms that I have created continue to this day to innovate as new requirements by Requestors and or Leadership Oversight are submitted. Constantly in change management.
  
  Have you thought about…
  
  1) Documenting the solution in a Demand Specification Form?
  2) Designing Test Plans and selecting appropriate Pilot Testers?
  3) Identifying external data source information that requires Data Shuttle integration?
  4) Designing Change Impact plans and selecting appropriate Pilot Testers?
  
  There has not been a solution that I have created in Smartsheet that has not been overly criticized or working around limitations. 🤣
  
  Below are solution examples that I have built, co-built and are still hosted to this day.
  
  1) Learning Center
  2) Training Roadmap
  3) System Innovation SCRUM Reporting

• 4) System Administrator Ticket Tool & Various Requestor/Leadership Dashboards
  5) Multi Country System Stand Up Project Plans w/ Various Leadership Dashboards
  6) Peer Review Feedback and Various Leadership Dashboards

• 7) Process Segmentation Oversight work in progress

g2smsht

Thanks for sharing this response, Erin. I'm not sure I understand how it applies to my question, which I've likely not communicated clearly enough. I am asking if it's possible to act on data input via forms by smartsheet automation workflow. For example, I want to be able to ask in a form for a milestone date, and if a notification for it is required, and if so, if it is a single or recurring notification, and then through automation (or another mechanism), setup the notification accordingly.

Thank you,

G2smsht

Erin Horiuchi Green

Hello @g2smsht ,

I created something very quick and simple as a proof of concept.

There are 2 hidden system columns "Created By" and Created Date".

There is a hidden Auto# column that the "Request ID" column is referencing via a column formula.

Form Logic
1) If Recurring is checked, then make Frequency drop-down column appear.
2) If Frequency drop-down has Semi-Annual selected, then make "Semi-Annual" appear.
3) If Frequency drop-down has Trimester selected, then make "Trimester2" and "Trimester3" appear. Front End User sees the rebranded label as "Select future 2nd Trimester date" and "Select future 3rd Trimester date".
4) and so on…

Recipient and Date columns are required for Alert Automation. Unless you setup permissions to post on a Teams or Slack channel which require the Admin to initialize.

Please note the Alert Automation fails when the Smartsheet is not shared at all in any way to a registered Recipient.

Below is the Alert Automation setup, failure notification to Smartsheet Owner and Successful Recipient notification.

If the Recipient is always singular, then you can embed the Approval Automation capabilities to electronically capture Accepted and Rejected invitations for reporting purposes.

g2smsht

This is much more clear Erin, thanks!

I understand now how the automation works, but dont understand what the formula for Request ID and #Auto are used for… (i.e. "There is a hidden Auto# column that the "Request ID" column is referencing via a column formula."). Can you share the column formula?

Thanks so much for your help!

Gary

Erin Horiuchi Green

Hello @g2smsht ,

As you gain more experience in Smartsheet solution designing, you will start to determine your framework preferences.

I prefer to have a hidden Auto# column and that have the Primary column reference it.

Then, I would add additional columns such as "Status" drop-down list = 1) Submitted, 2) In Review, 3) Scheduled, 4) Completed, 5) Delayed, 6) Completed

Statuses can be defaulted upon Form submission using "Change a Cell Value Automation".

Reviewer and/or Requestor can receive "Alert Automation" for when key Statuses are updated.

Lastly, I would have an "At Risk" Flag column and "Escalation" Checkbox column. "At Risk" Flag column can be automated based on respective column registration combinations or use "Change a Cell Value Automation". The "Escalation" Checkbox can be leveraged similarly. They are both helper columns to keep progressive action in play, prevent stagnation and identify bottlenecks.