Automation not working as intended

Faaez Kamaal

Hi all,

I have a sheet in which I'm tracking installation dates of equipment (outside vendor) and have automation to fire off to ensure the necessary pieces all fall in line (in house), within a timely manner.

However, something must have changed in the last couple of weeks, because it's fallen on its face and I'm having to manage the automation, thereby making the automation useless.

The columns "Has Install Date" and "Ship Hardware Check" are column formulas, and the key aspects here.

Has Install Date

=IF(ISBLANK([Chair Install Date Actual]@row), 0, 1)

Ship Hardware Check

=IF(AND([Has Install Date]@row = 1, [Chair Install Date Actual]@row < TODAY(+7), [Chair Install Date Actual]@row > TODAY(-14)), 1, 0)

Then, I have a workflow assigned to flip the check box on "OK to ship hardware". This never happens when I am NOT looking at the sheet.

I have to delete the value from "Chair Install Date Actual", save, reenter the value, save, THEN does the workflow fire to flip "OK to ship hardware".

To summarize, the above worked, some time passed, and now it doesn't. How can I fix it so as to be largely hands off again?

2021-10-29_18-00-28.jpg

2021-10-29_18-01-50.jpg

Neil Watson

Hi, it sounds like you need the TODAY() update workaround, since TODAY() only updates when the sheet is opened or accessed. I suspect this is the issue.

This forum has it explained, in short use the record a date automation to update a helper date column each day.

Andrée Starå

Hi @Faaez Kamaal

I hope you're well and safe!

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Neil Watson

Hi, it sounds like you need the TODAY() update workaround, since TODAY() only updates when the sheet is opened or accessed. I suspect this is the issue.

This forum has it explained, in short use the record a date automation to update a helper date column each day.

Andrée Starå

Hi @Faaez Kamaal

I hope you're well and safe!

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Faaez Kamaal

https://community.smartsheet.com/discussion/comment/309454#Comment_309454

Oh, and here I thought I was going crazy. LOL. Thank you!

https://community.smartsheet.com/discussion/comment/309455#Comment_309455

Funnily enough, I was thinking of switching to something like this instead of using formulas in the cells. Thank you!

Andrée Starå

@Faaez Kamaal

Excellent!

You're more than welcome!