Automation erroneously triggered

Options

My question is how can the ZZ 10 Day Indicator col (see below) become starred if the Package Complete Date column and the ZZ Completed 10 Days Date is not populated? When this is starred it moves the row t an archive datasheet - and in this case it moved it prematurely, erroneously, and I can't for the life of me figure out how this happened.

I have a datasheet where each row is a job and the column Package Complete allows the user to enter in the date in which the package (job at row) is dated as complete. The ZZ Completed 10 Days Date calculates a date 10 days in the future. If Today is that date, then an automation is triggered (see below) where it moves that row to an archive datasheet.

ZZ Completed 10 Days Date

=IFERROR(WORKDAY([Package Complete]@row, 10), " ")

ZZ 10 Day Indicator

=IFERROR(IF([ZZ Completed 10 Days

Date]@row <= TODAY(), 1, 0), 0)


Here's the automation

Here is what the archive datasheet looks like

But yesterday in the afternoon the automation was triggered and archived jobs that did NOT have a package complete date but yet were starred to archived.

Because there is a formula on the ZZ 10 Day Indicator, the user can’t manually select the star and trigger the automation.

So my question, is how can the ZZ 10 Day Indicator column become starred if the Package Complete date and the ZZ Completed 10 Days Date is not populated?

I've looked at the activity logs and see where the sheets have moved and were starred but I can't find out how they were starred.



Answers

  • Ella
    Ella ✭✭✭✭
    edited 11/02/23
    Options

    @Sticktotheplan Maybe someone added a date in the Package Complete column and then deleted it. To prevent it from triggering in such cases you can add a condition to your workflow that requires for the Package Complete column to not be blank.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Sticktotheplan

    I actually agree about adding the condition in the workflow about not being blank.

    Blank Date cells are seen as a generic "in the past" when it comes to formulas. This means that some of your rows where the formula is looking to see if the date is "less than Today" may create a star if the cell is blank.

    You could either add something to your formulas that doesn't return a blank cell / ignores blank cells, or add in Conditions to your workflows. I would potentially do both, just to be safe.

    =IFERROR(WORKDAY([Package Complete]@row, 10), " - ")

    =IFERROR(IF(AND([ZZ Completed 10 Days Date]@row <= TODAY(), [ZZ Completed 10 Days Date]@row <> ""), 1, 0), 0)

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!