Help

Close a Parent Project Once Multiple Tasks from Multiple Tables Are Complete

Topic Labels: Automations
524 2
cancel
Showing results for 
Search instead for 
Did you mean: 
JLong
4 - Data Explorer
4 - Data Explorer

What I am trying to do is change the Status of a Project once the Status of all six associated tasks (in separate tables) are set to complete. I tried doing a formula approach with calculation fields (count) using this formula below (but the formula in the field gets cut in half once I save and close so its useless). I asked support if there was a character limit but they could not even answer that simple question.

I am not married to the formula approach, in fact I'd rather not do it that way and have 13 extra fields in the Project record. Is there anyone out there that knows how to do this? 

Formula I created:

 IF(

AND({Design Completed Tasks}={Design Total Tasks}),

AND({Email Total Tasks}={Email Total Completed Tasks}),

AND({Organic Social Total Tasks}={Organic Social Total Completed Tasks}),

AND({SMS Total Tasks}={SMS Total Completed Tasks}),

AND({Paid Social Total Tasks}={Paid Social Completed Tasks}),

AND({Content Total Tasks}={Content Completed Tasks}), "1")

2 Replies 2

Hello,

You could try with only one AND in your formula listing the six conditions.

I created the following table to demonstrate:

Pascal_Gallais_0-1721663136567.png

Columns A to F are the six "task status columns", column "Status" is the global project status with this formula:

IF(
AND(A="closed",
B="closed",
C="closed",
D="closed",
E="closed",
E="closed"),
"closed","opened")
 
Regards,
 
Pascal
patrick-F
6 - Interface Innovator
6 - Interface Innovator

Hi @JLong
I do not have the full context to give you the best solution for your specific problem. Nevertheless, I think the formula gets cut in half because the syntax is wrong. You are not correctly using the And() formula and thus you are feeding the IF formula with 7 arguments instead of the maximum of 3.

My best guess for the correct syntax would be the following:

 IF(

AND({Design Completed Tasks}={Design Total Tasks},

{Email Total Tasks}={Email Total Completed Tasks},

{Organic Social Total Tasks}={Organic Social Total Completed Tasks},

{SMS Total Tasks}={SMS Total Completed Tasks},

{Paid Social Total Tasks}={Paid Social Completed Tasks},

{Content Total Tasks}={Content Completed Tasks}), "1")

Let me know if it works.

Best, Patrick @Monino Solutions