UPDATE PARENT TABLE STATUS USING RELATED TABLES

[KathB27]

Hi all, 

I just need help with updating the Parent Table Status... 

Here is my very simple formula that i am currently stuck with.....i do not know how to formulate the condition to "COMPLETED"

IFS(
(COUNT([Related PACKING PROGRESSs]))=0,
"NOT STARTED",

(COUNT([Related PACKING PROGRESSs]))>0,
"IN PROGRESS")

My condition for it to be Completed is that all Related Child Record to have a Status of "COMPLETED", "CLC STORAGE" and "SHIPPED"

[Suvrutt_Gurjar]

Maybe

IF( NOT(IN("Not Started", [Related PACKING PROGRESSs][Status])), "Completed", "Not Started")

[Status] is status column name in the child table.

[KathB27]

Hi @Suvrutt_Gurjar 

Yes, the column name in the Child Table is [Status].

I have tried your formula but it stayed in "IN PROGRESS" status.

Below photo should be "COMPLETED" as it meets the conditions that all the child table should either be "Completed", "CLC Storage" & "Shipped"

[KathB27]

@Suvrutt_Gurjar 

The different Status in Child Table are:

1.) "BPS Packing"

2.) "BPS Completed"

3.) "Crating in Progress"

4.) "Completed"

5. ) "CLC Storage"

6.) "Shipped"

On the other hand, the Parent Table status are:
1.) Not Started

2.) In Progress

3.) Completed

I Set the parent table status as: IF the related table = 0 then its "Not Started" 
I need to change the formula for "In Progress" to the criteria that IF any one of the related table has any of the below status then it should change to "In Progress"

1.) "BPS Packing"

2.) "BPS Completed"

3.) "Crating in Progress"

As for the "Completed" Status...the criteria should be that all the related table should have any of below status:

4.) "Completed"

5. ) "CLC Storage"

6.) "Shipped"

[Suvrutt_Gurjar]

Could you take a look at my latest post and respond?

[Suvrutt_Gurjar]

Is the [Status] column  in the parent  a real column? The suggested expression will work for a VC. If you do not wish to capture a transitory status in real column and just for display of app users, you could use a VC for status column in the parent.

If [Status] column in the parent is real column, please use the approach of reference actions. A related sample app below. It changes child table based on parent table values. You can also implement in reverse direction- child to parent.

Reference Actions

[KathB27]

Hi @Suvrutt_Gurjar 

Status in the Parent table is Virtual Column

[Suvrutt_Gurjar]

Please try 

SWITCH([Status]), 

         ISBLANK([Related PACKING PROGRESSs][Status]),   

         "Not started",

     OR( CONTAINS([Related PACKING PROGRESSs][Status], "BPS"),

            CONTAINS([Related PACKING PROGRESSs][Status], "Progress")

              ),

            "In Progress",

        "Completed")

[KathB27]

Hi @Suvrutt_Gurjar ,

it's showing error

[Suvrutt_Gurjar]

Oops, there was an extra parenthesis by oversight. Please try

SWITCH([Status], 

         ISBLANK([Related PACKING PROGRESSs][Status]),   

         "Not started",

     OR( CONTAINS([Related PACKING PROGRESSs][Status], "BPS"),

            CONTAINS([Related PACKING PROGRESSs][Status], "Progress")

              ),

            "In Progress",

        "Completed")

[KathB27]

Everything is now Completed😱 😁

😱

[Suvrutt_Gurjar]

I believe you will need to revise your requirements or else it will always show "Completed" 

If you have child records from all three status combinations , then it will revert to completed.

In the following image for example you have statuses from both 'Not Started" and "Completed"

[KathB27]

@Suvrutt_Gurjar 😞

is there no other way? These items go through all these stages…

[Suvrutt_Gurjar]

The expressions will work based on logic. You could decide what one resulting status you want when various combinations of those child table statuses are there.  

[KathB27]

I need to change the formula for "In Progress"to the criteria that IF any one of the related table has any of the below status then it should change to "In Progress"

1.) "BPS Packing"

2.) "BPS Completed"

3.) "Crating in Progress"

As for the "Completed" Status...the criteria should be that all the related table should have any of below status:

4.) "Completed"

5. ) "CLC Storage"

6.) "Shipped"

[Suvrutt_Gurjar]

I believe you are again sharing the same requirement. Will there be a parent record wherein some children are with Completed status and some with "Crating In Progress" for example? In that case what status you would need?

Anyway, could you give a try to modify the expression based on your latest requirement.

[KathB27]

I just need the child status table to be any of the 3 below status…then my parent table to be updated as COMPLETED but if below list is mixed with other status then it should still remain IN PROGRESS

4.) "Completed"

5. ) "CLC Storage"

6.) "Shipped"

[Suvrutt_Gurjar]

Please try 

SWITCH([Status], 

ISBLANK([Related PACKING PROGRESSs][Status]- LIST("Completed",  "CLC Storage",  "Shipped")) ,

     "Completed",

     "In Progress"

)

    or the below expression   

IF ( ISBLANK([Related PACKING PROGRESSs][Status]- LIST("Completed",  "CLC Storage",  "Shipped")) ,

     "Completed",

     "In Progress"

)

[KathB27]

I have tried both and below are the results:

For the Switch Formula -- it doesn't change to Completed even when all the child record status is completed

For the IF formula, the Parent table status is Completed even when there is no Child record yet. 

[Suvrutt_Gurjar]

Please try 

IF (

AND(ISNOTBLANK([Related PACKING PROGRESSs][Status]),  ISBLANK([Related PACKING PROGRESSs][Status]- LIST("Completed",  "CLC Storage",  "Shipped"))) ,

     "Completed",

     "In Progress"

)

[KathB27]

It Worked!! 😁 Thank you!!!😻

I changed the formula from IN PROGRESS to NOT STARTED...because the formula is correct that if the [Related Packing Progresss] ISBLANK then the status should be "Not Started"....But now i do not have the condition for IN PROGRESS 😔...can i insert the formula that if [Related Packing Progresss]<>0  then it's IN PROGRESS?

Here's the formula you gave that i used

IF (
AND(ISNOTBLANK([Related PACKING PROGRESSs][Status]), ISBLANK([Related PACKING PROGRESSs][Status]- LIST("Completed", "CLC Storage", "Shipped"))) ,
"Completed",
"Not Started"
)

[Suvrutt_Gurjar · Accepted solution]

Please try

IFS (
AND(ISNOTBLANK([Related PACKING PROGRESSs][Status]), ISBLANK([Related PACKING PROGRESSs][Status]- LIST("Completed", "CLC Storage", "Shipped"))) ,
"Completed",

AND(ISNOTBLANK([Related PACKING PROGRESSs][Status]), ISNOTBLANK([Related PACKING PROGRESSs][Status]- LIST("Completed", "CLC Storage", "Shipped"))) ,
"In Progress",

ISBLANK([Related PACKING PROGRESSs]),

"Not Started"
)

[KathB27]

Thank you very much @Suvrutt_Gurjar for all your help!!!!!💕😻

[KathB27]

Hi @Suvrutt_Gurjar & Appsheet Support Team,

I wanted to integrate the appsheet with Data studio and i wanted it to appear as a chart however my [Status] column is a Virtual column and now i need it to be in the real column...i have tried the formula but I guess that only works for VC

The criteria is slightly the same... i just need  additional status "SHIPPED" ....where all the related child table status are "Shipped"...if it's mixed with "Completed" & "CLC Storage" then it should stay as "Completed"

I hope you could advise me again.

[Suvrutt_Gurjar]

@KathB27  May I request you to start a new thread for this new topic? You may reference the previous topic in the new topic just in case you feel it necessary.

Also you have addressed this to me and AppSheet Support Team.

You may want to address a new question to the entire community so you will get the best possible solution. I am also a community member and not  from AppSheet support team just in case you think so -since you have addressed us together.  

For reaching out to AppSheet support team for any specific issue, there is another channel at Login - AppSheet 

Hope this helps. 

[KathB27]

Hi @Suvrutt_Gurjar ,

Ok sure, noted.

Thanks!😀