IF FORMULA

[KathB27]

hello again,

i wanted to add the "Not Ready" in the formula but it's not working, am i missing something? 

Please help.

IF(AND(
[Warehouse Status]="READY FOR DELIVERY",
[Fabrication Status]="READY FOR DELIVERY"
),
"READY FOR DELIVERY", "NOT READY"

IF(LIST[Related Related Delivery_Orders][STATUS]="COMPLETED",
"COMPLETED","DELIVERY IN PROGRESS"
))

[Suvrutt_Gurjar · Accepted solution]

You have listed two IF() statements with different alternate results.

May we know if  these two IF() are in the same column? If so what final results you are expecting. You may want to possibly try the following IFS() for multiple conditions, but exact expression will depend on how the statuses sequentially progress. Also there is an error in your current LIST( ) expression as there is no closing parenthesis and list anyway cannot be compared with a single text value. ( the part LIST[Related Related Delivery_Orders][STATUS]="COMPLETED" ) 

You may also wish to verify /mention here,  if the twice [Related Related Delivery_Orders] is a typo or there is a column by that name.

IFS(

AND(
[Warehouse Status]="READY FOR DELIVERY",
[Fabrication Status]="READY FOR DELIVERY"
),
"READY FOR DELIVERY", 

NOT(AND(
[Warehouse Status]="READY FOR DELIVERY",
[Fabrication Status]="READY FOR DELIVERY"
)),

"NOT READY" , 

ISBLANK([Related Related Delivery_Orders][STATUS] -LIST("Completed")),
"COMPLETED",

ISNOTBLANK([Related Related Delivery_Orders][STATUS] -LIST("Completed")),
"DELIVERY IN PROGRESS"
)

[Suvrutt_Gurjar · Accepted solution]

You have listed two IF() statements with different alternate results.

May we know if  these two IF() are in the same column? If so what final results you are expecting. You may want to possibly try the following IFS() for multiple conditions, but exact expression will depend on how the statuses sequentially progress. Also there is an error in your current LIST( ) expression as there is no closing parenthesis and list anyway cannot be compared with a single text value. ( the part LIST[Related Related Delivery_Orders][STATUS]="COMPLETED" ) 

You may also wish to verify /mention here,  if the twice [Related Related Delivery_Orders] is a typo or there is a column by that name.

IFS(

AND(
[Warehouse Status]="READY FOR DELIVERY",
[Fabrication Status]="READY FOR DELIVERY"
),
"READY FOR DELIVERY", 

NOT(AND(
[Warehouse Status]="READY FOR DELIVERY",
[Fabrication Status]="READY FOR DELIVERY"
)),

"NOT READY" , 

ISBLANK([Related Related Delivery_Orders][STATUS] -LIST("Completed")),
"COMPLETED",

ISNOTBLANK([Related Related Delivery_Orders][STATUS] -LIST("Completed")),
"DELIVERY IN PROGRESS"
)

[KathB27]

Hi @Suvrutt_Gurjar 

Thank you soo much! The formula is works perfect!

Yes, there was typo error on my side 🙈

[KathB27]

Hi @Suvrutt_Gurjar 

It seems the one highlighted in red is not working.

Would you be able to help me again?

Warehouse Status and Fabrication Status are from the parent table --- once both Warehouse and Fabrication are "Ready for Delivery" the overall Status will change to "Ready for delivery"... the formula works fine for this.

However, I wanted it to change to "Delivery in Progress" when child table/s are created.

Also, when the status of the all the child table/s are all "Completed" i wanted it to be Completed.

Thanks in advance! :))

IFS(

AND(
[Warehouse Status]="COMPLETED",
[Fabrication Status]="COMPLETED"
),
"COMPLETED",

AND(
[Warehouse Status]="READY FOR DELIVERY",
[Fabrication Status]="READY FOR DELIVERY"
),
"READY FOR DELIVERY",

NOT(AND(
[Warehouse Status]="READY FOR DELIVERY",
[Fabrication Status]="READY FOR DELIVERY"
)),

"NOT READY" ,

ISNOTBLANK([Related Delivery_Orders][STATUS] -LIST("Completed")),
"DELIVERY IN PROGRESS",

ISBLANK([Related Delivery_Orders][STATUS] -LIST("Completed")),
"COMPLETED",

)

[Suvrutt_Gurjar]

It sounds that you are combining statuses from both parent and child tables to arrive at one final status. So you may need to decide the final status sequence considering respective statuses from parent as well as child table.

Currently, it sounds that one of the parent table status could be meeting the condition so that the IFS() statement is not evaluated further. The child table based statuses are at the last in current expression.

So in summary, I think the community could assist with an expression, if you could make a joint table of sequence of parent table as well as child table statuses. For example, there is "Completed" status for Warehouse as well as related delivery orders on the child table. This could be clashing.

[WillowMobileSys · Accepted solution]

I would use an IFS() function.  Also, the second IF() is not correct.  I assume you only want the status set to "COMPLETED" if ALL status' are complete?

IFS(
    AND(
        [Warehouse Status]="READY FOR DELIVERY",
        [Fabrication Status]="READY FOR DELIVERY"
    ),
    "READY FOR DELIVERY",

    AND(COUNT(UNIQUE([Related Related Delivery_Orders][STATUS])) = 1,
        ANY(UNIQUE([Related Related Delivery_Orders][STATUS])) = "COMPLETED",
    "COMPLETED",

    COUNT(UNIQUE([Related Related Delivery_Orders][STATUS])) > 1,
    "DELIVERY IN PROGRESS",
           
    TRUE, "NOT READY"
)

I hope this helps!

[KathB27]

@WillowMobileSys 

Yes it helped a loooottt! 😁

Thank you very much!!!!🤗