Automation not Triggering on Function Checkbox

FPalhano

Hello,

I have an automation that is setup to trigger once a particular column checkbox is checked. The column checkbox is checked by a column wide function. Prior to this I had cell level functions and the automation was working fine, not sure what the issue is now…

Any help would be appreciated! Details below:

when all child phase items are checked there is a function in child items complete that checks the parent.

For whatever reason this automation isn't triggering when that occurs:

when it was set to "Phase Complete" (when I had the function at the parent cell level) is checked it worked fine… any thoughts?

Thanks in Advance

FPalhano

@Paul Newcome So I got it working by basically making a fourth column called "Child Item Complete String" that checks if "Child Item Complete" is checked and then adds "true" to "child item complete string"… which is not elegant at all but I'll take it at this point.

I could probably redo the "Child Item Complete" column and function (make it a string, etc.) so I don't need the fourth column… but that's a future problem

No Idea why checkbox triggers don't seem to be working for me.

kowal

hi @FPalhano,

if the checkbox is filled by formula using Reference of Another sheet it will not work.

Smartsheet on purpose blocked all Workflows that are triggered with Formulas using data from another sheets so there is no issue of everlasting loops.

Paul Newcome

Which column is the automation supposed to be updating?

FPalhano

@kowal it's not referencing another sheet - it's referencing the same sheet… how would you accomplish an automation triggering only when all child rows were "completed" without something like the above?

FPalhano

Hey @Paul Newcome - the automation doesn't update any of these columns, it is supposed to trigger when child item complete is checked. It's not triggering at all, and that's the issue I'm running into. Basically this is supposed to be manager level approval of a phase once all the child rows have been completed. All I need is for the automation to trigger when all the child rows are completed.

The above was my current approach - I had functions at the cell level previously that worked to trigger the automation (basically just had the phase items column and the formula on the parent cells) but wanted to make it easier to use when adding new phases.

I suppose I could just have the automation trigger every hour (or on any change) and use a condition to check if "Child Items Complete" instead of a trigger… but I'd prefer this route if possible.

Paul Newcome

Which column are you trying to update with the automation?

If it is one of the columns with a column formula, you would need to either convert it to a cell formula or update the formula to also check off the parent rows when applicable.

Disregard the above. I had typed that out before I saw your response.

So the trigger is not firing at all. What are the formulas you are using?

FPalhano

@Paul Newcome "Phase Items complete" are checked by another automation when the row is approved. see below for the formulas for the columns:

"Is Parent" Column

Automated "helper" column

Function = "=IF(COUNT(CHILDREN([Task Name]@row)) > 0, true)"

"Child Items Complete"

New column with column level function that uses "Is Parent" and "Phase Items completed" to identify Phases, projects, and years that have been fully completed from a deliverables perspective.

Function = "=IF([Is Parent]@row, IF(COUNTIFS(CHILDREN(), 1) = COUNT(CHILDREN([Phase Items completed]@row)), 1), [Phase Items completed]@row)"

Paul Newcome

Ok. And what about the [PSA Phase Remediation] column?

FPalhano

@Paul Newcome The Phase Remediation Column is just there to be a manual trigger if the manager rejects the approval. The only two options are "In Progress" and "Completed"

Basically to have their folks go back in, make adjustments as needed, and manually mark the "remediation" as complete so the automation can run again to go back to approval.

There's another automation that notifies the employee and asks for an update basically:

To be honest this part could be part of the denial flow instead of a separate automation…

Paul Newcome

And do you have any conditions built into the automation that is not triggering?

FPalhano

I do, see below

For context basically if approval isn't needed for a particular item/phase then N/A is added to the respective "approval" column - see below

Paul Newcome

I believe it has to do with the fact that the [PSA Phase Remediation] field is being updated by a potential action of the same workflow. What happens if you either remove that trigger or remove that action?

FPalhano

Hey @Paul Newcome removed the denial logic completely and did some other testing including running the automation manually. Works fine when run manually so it has something to do with the trigger…

I actually removed the [PSA Phase Remediation] from the trigger and also removed the function to just test on triggering manually by checking [Child Items Complete]… and it's still not triggering so now I'm even more confused

FPalhano

@Paul Newcome Just created a quick new automation just to see if the trigger works at all.

This STILL doesn't work when checking "Child Items Complete" manually so very unclear as to what's going on.

Paul Newcome

You have it set to send to specific people. Do you have it set to send to yourself? If so, have you enabled that feature in your personal settings?

FPalhano

yup, for this sheet (its the testing one I use as a template) all the automations notify me/ask for my approval/etc.

for the quick notification one I setup there's not even a "last ran" in the automation page so it's not triggering at all… seems to be only for check boxes