Conditional Notification / Alerts

Hi Smartsheet Community,

I recently posted a reply on the smartsheet blog (//www.santa-greenland.com/comment/46032#comment-46032)and Diana Ramos kindly replied letting me know that Conditional Notification might soon be available. Diana asked me to add a thread here to capture and direct the programmers to ensure our requirements were covered off.

So let me begin this thread and explain what I am hoping for.

I manage a sheet which we use to log issues for our external agencies to pick up and resolve. The columns (fields) we have include:

Reference number

Agency Status (Agency 1 / Agency 2 etc., one column for each agency with the choice of labels: N/A / Draft / New / Ongoing / Completed TBC / Complete)

Level of urgnecy (Normal / Urgent)

Category (eg Biscuits / Snacks / Cakes)

Issue Type (Data / Coding)

Title (summary of issue)

Description (more detail)

Barcodes / Tags (info specific to the agency's data)

Proposed Deadline (to help give some guidance on when we need this completed by -I normally add 10 days to today's date but this can vary)

Originator (normally me!)

If I refer you to the Status field we can see a simple process flow where by if goes from New through to Completed.

It's possible to have a 'Modified' field which details who last edited it. This is a system field which updates automatically. What I would like is to combine this with the alert functionality based on these rules.

Step 1) When I (Rod) add an issue to the sheet (ie a change is made) if the issue relates to Agency 1 only (Ie Agency 2 is set to N/A in it's field) then send a notification to Agnecy 1 but not Agency 2.

Step 2) When Agency 1 reviews the issue and replies / adds extra details (adding an attachment or adding a comment to the row), an alert should be sent to the Originator (Rod) only.

Step 3) When I (Rod) replies to Agency 1's comments, again an alert should be sent to Agency 1 (but not Agency 2 as that is set to N/A)

Step 4) The agency may reply and change their Agency Status field from ongoing to Complete TBC (to be confirmed). Again Rod should recieve an alert only.

Step 5) Rod will then review the final comment and change to Complete for the speciifc Agency OR change back to ongoing and add a comment and we'll jump back to step 2 with the corresponding alerts to follow.

I hope this make sense. Please let me know if you want me to clarify anything. The email address(es) for the Agnecy could be either a group email address or a specific list of email addresses (perhaps there are multiple people from the agency who manager the sheet from their end).

FYI: I have worked in the past with a number of Smartsheet support staff (Darren Brady and Kara Lumley to name but a few) and we've developed a manual solution for the time being but it highly depends on the end user ticking a box for the specifc agency on the far RHS. This has it's problems as often people forget to tick the box! We've also investigated 3rd party but this brings in complexity, sharing of data issue and also additional cost implications.

Thanks all inadvance for any input to this thread.

Rod Pestell