Why won't my automation to record dates work?

Thomas Griess

I am trying to record a date for each of our statuses listed below.

Approval Pending, Approved, Denied, In progress, Completed, and Removed

Most of these statuses are updated by a formula when dependencies are met on my sheet.

The formula for the statuses is working correctly, but for whatever reason the In progress, Completed, and Removed statuses are not triggering this workflow and recording a date when I test it.

Prior to using a formula to update the status field, it was manually updated, and this workflow was functional. This has me thinking that the new formula that updates the status is somehow breaking this workflow.

Let me know your thoughts. Any help would be appreciated!

Nick Korna

Hi @Thomas Griess,

From your screenshot, your In Progress, Completed and Removed condition paths don't have a specified column to record the date in - where are you expecting the date to be recorded?

You could combine the 3 into one (and if necessary also have the date recorded in the same with the other options as an extra step) in a "Last Access Status Change" Date column.

Thomas Griess

Hi @Nick Korna,

Thanks for your response.

Good catch, I took that screenshot after I was doing some additional testing. I have updated the screenshot in the main post. You'll also see that I tried making the triggers more broad to see if it was a trigger issue.

That's a good idea and I think there could be value in having a Last Status Change field, if not just for more testing. We are hoping to do additional analytics and would like to capture differences in the specific status dates to see how well the team is performing or see at which step there was a delay.

Here is what those fields look like after running through the workflow. It seems to mainly take issue with those three fields.

Nick Korna

Hmm, I have made a workflow the same as your (now edited) screenshot and it works. The only difference I can think of is that mine is having the Access Status manually entered rather than being generated by formula. Is your formula calculated from something?

Thomas Griess

@Nick Korna - Yes, the column formula for Access Status has several dependencies on the sheet that I want the overall Access Status to take into consideration. In our previous version of this sheet the Access Status was manually maintained, and the date fields were able to record off of the statuses as you experienced above, but we are trying to improve the accuracy and reduce the human error on the sheet so I made this field a column formula. Here is a generic version of the formula below for your reference.

Once it gets to the check for the In progress status, it is checking the statuses for specific systems to see if they are needed for a user. Completed status checks for all of these statuses to be either Granted or N/A, and if that is not true it is marked as Removed.

=IF(
[Bulk Request]@row = 1,
"Bulk Request",
IF(
[Duplicate Check]@row = 1,
"Exists in Log",
IF(
AND(
OR([Send for Approval]@row = 0,
[Access Approval]@row = "Approved"),
[System1 Status]@row = "",
[System2 Status]@row = "",
[System3 Status]@row = "",
[System4 Status]@row = "",
[System5 Status]@row = "",
[System6 Status]@row = "",
[System7 Status]@row = "",
[System8 Status]@row = ""),
"New Request",
IF(
AND([Send for Approval]@row = 1, [Access Approval]@row = ""),
"Approval Pending",
IF([Access Approval]@row = "Denied",
"Denied",
IF(
SUM(
IF([System1 Status]@row <> "Needed", 0, 1),
IF([System2 Status]@row <> "Needed", 0, 1),
IF([System3 Status]@row <> "Needed", 0, 1),
IF([System4 Status]@row <> "Needed", 0, 1) ,
IF([System5 Status]@row <> "Needed", 0, 1),
IF([System6 Status]@row <> "Needed", 0, 1),
IF([System7 Status]@row <> "Needed", 0, 1),
IF([System8 Status]@row <> "Needed", 0, 1)
) > 0,
"In progress",
IF(
SUM(
IF(OR([System1 Status]@row = "Granted", [System1 Status]@row = "N/A"), 1, 0),
IF(OR([System2 Status]@row = "Granted", [System2 Status]@row = "N/A"), 1, 0),
IF(OR([System3 Status]@row = "Granted", [System3 Status]@row = "N/A"), 1, 0),
IF(OR([System4 Status]@row = "Granted", [System4 Status]@row = "N/A"), 1, 0),
IF(OR([System5 Status]@row = "Granted", [System5 Status]@row = "N/A"), 1, 0),
IF(OR([System6 Status]@row = "Granted", [System6 Status]@row = "N/A"), 1, 0),
IF(OR([System7 Status]@row = "Granted", [System7 Status]@row = "N/A"), 1, 0),
IF(OR([System8 Status]@row = "Granted", [System8 Status]@row = "N/A"), 1, 0)
) > 0,
"Completed",
"Removed"
)
)
)
)
)
)
)

Thomas Griess

Not sure why this worked, but I broke this one automation that updates all 6 status dates into 2 (3 dates recorded in each), and now it works.

If someone can explain why this is I would be interested in the answer. The same triggers for these two, but it just wasn't working when it was all in the same automation.