Trigger Issue

Michael "Mike" Babin

For one of my sheets, when someone submits a request through a form, I have some rules that assign approvers, and also some rules that assign the number of approvers. These always work fine. Okay, as one example, let’s suppose the number of approvers is just one. I have a Change Status rule like this: When rows are added or changed, when First_Approver changes to <Any Value>, run workflow when triggered, where Number of Approvers is equal to 1, change cell value Status to Complete. The problem is this rule (and the similar rules for up to 5 approvers), does not always trigger and make it Complete. It works most of the time, but not all of the time. I am not having a similar problem with any of my other rules in the sheet. Any suggestions?

🤔

MVP OPS

Hi Michael,

Hope you are doing well. If these are all workflows it could just be a timing thing. My suggestion would be to take your problem workflows out of automations and put them in column formulas.

Fo your status column problem you could use a nested IF statement like this one

 I have a Change Status rule like this: When rows are added or changed, when First_Approver changes to <Any Value>, run workflow when triggered, where Number of Approvers is equal to 1, change cell value Status to Complete.

=If(and(not(isblank([First Approver]@row)), [Number of Approvers]@row=1), "Complete")

then build on this formula for your second approver, third, etc.

=If(and(not(isblank([First Approver]@row)), [Number of Approvers]@row=1), "Complete", if(not(isblank([First Approver]@row)),not(isblank([Second Approver]@row)),[Number of Approvers]@row=2), "Complete")

Hope this helps.

Brad

MVP OPS

Michael "Mike" Babin

I just tried changing my Change Status rules as follows:

One approver: When rows are added or changed, when First_Approver changes to <Any Value>, or when Number of Approvers changes to 1, run workflow when triggered, where Number of Approvers is equal to 1, and where First_Approver is <not blank>, change cell value Status to Complete.

Two approvers: When rows are added or changed, when First_Approver changes to <Any Value>, or when Second_Approver changes to <Any Value>, or when Number of Approvers changes to 2, run workflow when triggered, where Number of Approvers is equal to 2, and where First_Approver is <not blank>, and where Second_Approver is <not blank>, change cell value Status to Complete.

...

I will see what happens... Still open to suggestions though.

Michael "Mike" Babin

By the way, by rules I mean workflows.

MVP OPS

Hi Michael,

Hope you are doing well. If these are all workflows it could just be a timing thing. My suggestion would be to take your problem workflows out of automations and put them in column formulas.

Fo your status column problem you could use a nested IF statement like this one

 I have a Change Status rule like this: When rows are added or changed, when First_Approver changes to <Any Value>, run workflow when triggered, where Number of Approvers is equal to 1, change cell value Status to Complete.

=If(and(not(isblank([First Approver]@row)), [Number of Approvers]@row=1), "Complete")

then build on this formula for your second approver, third, etc.

=If(and(not(isblank([First Approver]@row)), [Number of Approvers]@row=1), "Complete", if(not(isblank([First Approver]@row)),not(isblank([Second Approver]@row)),[Number of Approvers]@row=2), "Complete")

Hope this helps.

Brad

MVP OPS