Automation question

JenMcD

I have a spread sheet I was hoping to use to automate a ton of menial task to onboard staff. Currently it's a lot of "If a nurse starts Monday, email the manager" "If a person with one of these titles starts Monday, record a checkbox in another column." Most of the starting triggers are the same. If the "start date" cell is not blank, run this workflow. The problem is that the first time I set it up, it worked fine, but none of the others will run despite having the exact same conditions. Can I only use that start date is not blank once? If so, any suggestions to make it work? Right now everyone spends a ton of time copying things from one spreadsheet to another and it's ripe for errors and a waste of time.

jmyzk_cloudsmart_jp

Hi @JenMcD

I can not pinpoint what causes workflow automation not to work, but I find that many workflow automation methods can be replaced with formulas.

For example, those can be performed by the formula;

•  Record a checkbox in another column
• Set the Status values

It will become more reliable by making your system less dependent on workflow automation.

I added several helper columns in the demo sheet below to simplify the workflow trigger and formulas.

https://app.smartsheet.com/b/publish?EQBCT=3f85fdf59c104c659d04eaa198e79421

[Start Date is not blank] =IF(ISDATE([Start Date]@row), 1)
[Monday] =IF(ISDATE([Start Date]@row), IF(WEEKDAY([Start Date]@row) = 2, 1))
[Clinical Titles] =IF(CONTAINS(Title@row, [Clinical Titles]#), 1)
[Send Email] =IF(AND([Clinical Titles]@row, [Start Date is not blank]@row), 1)
[Checkbox (Recorded)] =IF(AND([Clinical Titles]@row, Monday@row), 1)
[Task Status] =IF([Email Sent]@row, "Email Sent", IF(AND([Send Email]@row, NOT([Email Sent]@row)), "Pending", "Not Started"))

Formula Explanation

• Start Date is not blank
  Checks if the "Start Date" column has a valid date. If it does, it marks the row with 1, otherwise 0.

• Monday
  Checks if the "Start Date" is a Monday. If true, it marks the row with 1, otherwise 0.

• Clinical Titles
  Identifies if the job title in the "Title" column belongs to a specific list of clinical roles (e.g., Nurse, Nurse Practitioner, Technician). If true, it marks the row with 1, otherwise 0.

• Send Email
  Checks if both the "Clinical Titles" column is marked and the "Start Date is not blank" column is marked. If true, it sets the value to 1, meaning an email should be sent.

• Checkbox (Recorded)
  Marks the row if the person has a clinical role and their start date is on a Monday.

• Task Status
  Updates the task status based on whether the email has been sent:
  • If the email is sent, the status is "Email Sent."
  • If an email needs to be sent but hasn’t been sent yet, the status is "Pending."
  • Otherwise, the status is "Not Started."

Simplified workflow automation

Using those helper columns and formulas, workflow automation becomes simple and thus more dependable.

An example of an email a manager received