Questions: Sheet Automation with Multiple Due Dates and Departments

Hello,

Any advice in how to best approach this needed automation would be welcomed!


Goal: Alert colleagues of different departments that plans are due to be reviewed by their respective departments soon. Departments include Engineering, Addressing, Cable, Gas, Electric, W/WW. Alerts would be triggered when the "Reviewed By" column is empty.

Sheet Set-Up: There are different phases of the plans being reviewed: Plat, Sketch, Dev. Site Plan, Grading, Building Permit and As Built.

Within the various phases of the plan, each department is to review the plan and indicate that they have done so by selecting their name in the "Reviewed By" column. With each phase of the plan, a new due date will be added. Only when the "Reviewed By" column is blank, the respective department should be alerted. (See sheet below)


Automation: I plan to make "Due Date Approaching" and "Due Today!" alerts. I need to figure out how to create an automated workflow to alert users for each phase of the plan without users being alerted to phases that have not yet received a due date.

So far, I've created the following automation:

Trigger: Run once 2 days before "Due Date" @ 8:00 AM

Condition: Where "Task" is one of Engineering - Plat and where "Due Date" is in the next 2 days and where "Reviewed By" is blank.

Action: Send to Specific Colleague


Question 1: Should the condition for where "Task" is one of "Department - Phase", be added as separate conditions (conditional path) or can all the "Department - Phase" combinations be added in the same condition? (example: Engineering - Plat, Engineering Sketch, Engineering - DSP, etc.)

Single Condition - multiple phases

OR Conditional Path - multiple phases


Question 2: When automating a workflow, does Smartsheet consider the data in a row or the entire sheet? For instance, would multiple due dates trip up an automation? See below.

Question 3: In order for this automation to work, would I need to reference the due date in each row that a department is listed? Or will Smartsheet know to reference the date where the Phase is listed?

Phase Due Date

Department Due Date - (this looks and feels right but not sure if necessary)


Best Answer

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    Question 1- you should be able to list all in a single condition

    Question 2- the automation workflows look at the row level so different dates in other rows won't trip it up

    Question 3- you will need department due date.

    [if it will always be the same as the phase due date you can use the formula =PARENT() in the rows for the department and it will pull the date for the phase row it is under.]

Answers

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    Question 1- you should be able to list all in a single condition

    Question 2- the automation workflows look at the row level so different dates in other rows won't trip it up

    Question 3- you will need department due date.

    [if it will always be the same as the phase due date you can use the formula =PARENT() in the rows for the department and it will pull the date for the phase row it is under.]

  • Kia Gibbs
    Kia Gibbs ✭✭✭

    @Kimberly Loveless Thank you! I may have been overthinking the entire process because I am still pretty new to Smartsheet! This gives me a reassurance!