Do formulas work in Workflows?

ChrisWolfeENG · 05/07/21

Do formulas function in Automated Workflows by any chance?

I have an AV Maintenance Sheet that is populated by a Form. Each row is a new record of a Room test. If a Room is checked and Fails, and the same room is checked again at a later date and passes; I am trying to ultimately move the older row with a Failed status to an archive sheet.

I was thinking of using a Workflow where:

When Rows are added
When the Room is Any Value

...and adding the Condition (if possible)

Where Room contains @ cell <> "" (or something that might work? Basically the same value as the added row)
and where Status is Fail
and where Date is in the past

Move the Row

Is anything like that possible or achievable within a Workflow?

Huge thanks in advance to everyone in the group, the community is great!

James Keuning · 05/08/21

What you are going to want to do is use a helper column. Or as I am trying to imagine this, a few helper columns. I usually use yes/no fields for this, and basically test the record, and as soon as the record passes the test, the automation takes action.

I can imagine a couple of ways of doing this, but the thing that comes to mind is using COUNTIFS to identify that there are more than one of the Room in the sheet. And if there are, test that this record is not the MAX of Date. (You can do this any number of ways, like use COLLECT to identify the MAX and test to see if this DATE is < the MAX Date.) And test if Status is Fail. And test if Date is in the past. If you test all of those conditions and all are TRUE (this is another helper column) then this collector helper column is TRUE and that fires the automation.

James Keuning · 05/28/21

Yers! And if you take that MAX(COLLECT(Date:Date, Room:Room, Room@row)) and paste it over the [Latest Date]@row, you can delete the helper!

But keeping them separate is definitely an easier way to make sure things are working.

I don't know if there is a benefit to collapsing the formulas into one, as opposed to keeping them spread out and referring to each other, but here are two reasons that I have encountered.

There is a max # of cells in a sheet. So those helper columns take up count.

Also, when you move or copy cells from one sheet to another, if the columns are not in the destination, Smartsheet will create the column. This rapidly compounds the cell count, and see previous point.

James Keuning · 05/08/21

What you are going to want to do is use a helper column. Or as I am trying to imagine this, a few helper columns. I usually use yes/no fields for this, and basically test the record, and as soon as the record passes the test, the automation takes action.

I can imagine a couple of ways of doing this, but the thing that comes to mind is using COUNTIFS to identify that there are more than one of the Room in the sheet. And if there are, test that this record is not the MAX of Date. (You can do this any number of ways, like use COLLECT to identify the MAX and test to see if this DATE is < the MAX Date.) And test if Status is Fail. And test if Date is in the past. If you test all of those conditions and all are TRUE (this is another helper column) then this collector helper column is TRUE and that fires the automation.

ChrisWolfeENG · 05/10/21

Hello @James Keuning ,

Thank you very much for the response! I believe I understand.

There is a question I posed in another discussion and Mark Kronk suggested using and =INDEX(Collect...) and MAX(COLLECT...) to possibly achieve this. However, I believe I understand the idea of using COUNTIFS, then use the COLLECT and MAX functions, then trigger the Automation off of a True / False helper column.

I am very much a beginner when it comes to Formulas / Functions, but I will give it a shot.

Thank you again for the explanation! Your time and expertise is greatly appreciated.

Best Regards,

Chris

ChrisWolfeENG · 05/27/21

Hey @James Keuning ,

It has been a while, but I wanted to drop an update after I finally got it working.

I have a Latest Date helper column with the formula:

=MAX(COLLECT(Date:Date, Room:Room, Room@row))

...which takes all of the Dates, compares that to the same Room Names, and enters the maximum Date in the cell.

Then I have another helper column with the test:

=IF([Latest Date]@row = Date@row, "True", "False")

...which will compare the Latest Date to the Date the record was entered, if they are the same, indicate "True", if not then indicate "False".

Then I built a workflow around anything with a "False" to move to an archive.

As a complete novice with formulas and functions there probably is a better, cooler, cleaner way of doing this....but it works!

Thank you again for your time and expertise assisting with this. By building my own formulas from scratch, I have a much better understanding now. +10 Insightfuls to you!

Health and happiness to you and yours.