Help Please with Valid_If Statement for checking status of linked Order Items of Main Order

[EricDevy]

Hi All, I need some urgent help with a Valid_If statement please.

I have a Parent Table for Orders. And I have a Linked Child Table for Order_Items.
The Parent Orders Table has a "Order_Status" Ref dropdown to select the Status of the Parent Order and the Child Order_Items table has a "Order_Item_Status" Ref dropdown to select the status of each of the Individual Order_Items.

I don't want the User to be able to select the Parent Order_Status - "Closed - Complete" or "Closed - Cancelled" If there is an existing Child Order_Items record with an Order_Item_Status "At Bending" or "At Welding" etc..

Only if all the Child Order_Items linked to the Parent Order have a status of "Packed - Complete" or "Cancelled" must the User be allowed to select the Parent Order_Status "Closed - Complete" or "Closed - Cancelled".

The Parent Table (Orders) has a Key [SO_Number] (Text)
and the Child Table (Order_Items) has a Key [SO_Item_Id] (Unique ID)
and the Child Table (Order_Items) has a [SO_Number] Ref Column to the Parent Table (Is Part Of)

[Suvrutt_Gurjar]

You may want to mention 

1. Are the two tables referenced to each other?

2. The column types of [order Status] and [Ordered Item Status] ( Enum or Enumlist) and

3. how those enum options are populated in those columns - by entering those options in the column settings or derived through valid_if option?

[EricDevy]

@Suvrutt_Gurjar Thank you for your response.

1. Yes both tables reference each other (Part Of).
2. Both Column Types [Order Status] and [Order_Item_Status] Are REF Dropdowns.

[Suvrutt_Gurjar]

You have somewhat different descriptions for those two statuses in the parent and child table, or else @TeeSee1 's suggestion is very good to try as a compact one.

Since you have somewhat different descriptions, your valid_if in the parent table can be something like below. It is somewhat longish. Will try to see if it can be reduced.

IF(

COUNT( [Related Ordered Items][Ordered Item Status])= COUNT( SELECT([Related Ordered Items][Ordered Item Status], [Ordered Item Status]="Cancelled") )+ COUNT( SELECT([Related Ordered Items][Ordered Item Status], [Ordered Item Status]="Packed - Complete")) , 

LIST( Order Status Ref Dropdown) ,

(LIST( Order Status Ref Dropdown) - LIST( ""Closed - Complete"  ,  "Closed - Cancelled" ))

)

[EricDevy]

Thanks I tried that but then there is only 1 Status available to choose from on the Parent (Orders) dropdown. I have reworded my original post to try and make more sense of what I need. See below.

Hi All, I need some urgent help with a Valid_If statement please.

I have a Parent Table for Orders. And I have a Linked Child Table for Order_Items.
The Parent Orders Table has a "Order_Status" Ref dropdown to select the Status of the Parent Order and the Child Order_Items table has a "Order_Item_Status" Ref dropdown to select the status of each of the Individual Order_Items.

I don't want the User to be able to select the Parent Order_Status - "Closed - Complete" or "Closed - Cancelled" If there is an existing Child Order_Items record with an Order_Item_Status "At Bending" or "At Welding" etc..

Only if all the Child Order_Items linked to the Parent Order have a status of "Packed - Complete" or "Cancelled" must the User be allowed to select the Parent Order_Status "Closed - Complete" or "Closed - Cancelled".

The Parent Table (Orders) has a Key [SO_Number] (Text)
and the Child Table (Order_Items) has a Key [SO_Item_Id] (Unique ID)
and the Child Table (Order_Items) has a [SO_Number] Ref Column to the Parent Table (Is Part Of)

[Suvrutt_Gurjar]

Thank you. You may also want to share your current expressions for [Status] columns of both the parent and child tables.

[EricDevy]

I Can Only Choose One Order Status on the Order when the related Order Item Status is "Packed - Complete".

IF(
COUNT([Related Order_Items][SU_Order_Item_Status])= COUNT(SELECT([Related Order_Items][SU_Order_Item_Status], [SU_Order_Item_Status]="Cancelled"))+ COUNT( SELECT([Related Order_Items][SU_Order_Item_Status], [SU_Order_Item_Status]="Packed - Complete")) ,
LIST([SU_Order_Status]) ,
LIST([SU_Order_Status]) - LIST("Closed - Complete" , "Closed - Cancelled" ))

The Rest Of The Order Status Options Are Not Available:

SU_Order_Status

Open - Placed

Open - Received Processing

Open - In Progress

Open - Delayed

Awaiting WH Collection - At PMB Plant

Awaiting Dispatch - At PMB WH

Awaiting Dispatch - At PNT WH

Awaiting Dispatch - At FW WH

Closed - Complete

Closed - Cancelled

[Suvrutt_Gurjar]

As requested before, please share the following details

---

@Suvrutt_Gurjar wrote:

Thank you. You may also want to share your current expressions for [Status] columns of both the parent and child tables.

---

It sounds that the use of LIST([SU_Order_Status]) is incorrect. My recommendation was to create a list of  "Order Status Ref Dropdown". 

[EricDevy]

I do have a list of " Order Status Ref Dropdown".

[TeeSee1]

It might be easier (meaning a simpler expression) to check that none of the order items are in status other than those two rather than checking if they all are.

ISBLANK(
 INTERSECT(
  LIST(a list of all possible status) - LIST("Packed - Complete", "Cancelled),
  [related Ordered Items][Status]
 )
)

Here if you maintain all possible status in a table, say Order Item Status, (a list of all possible states) can be Order Item Status[state] or just write them out one by one.

[EricDevy]

@TeeSee1 Thank you for your input. Not sure what you mean.

I have this Valid_If Statement at the moment, but no matter which status I choose I get an error.

ISNOTBLANK(FILTER("Order_Items",AND([SO_Number]=[_THISROW].[SO_Number],
[SU_Order_Item_Status]="Packed - Complete",AND([SO_Number]=[_THISROW].[SO_Number],[SU_Order_Item_Status]="Cancelled"))))

Supposed to only show error if "Closed - Complete" or "Cancelled" is chosen.

[TeeSee1]

In Valid-If, you have to test not only the status of the related ordered items but also what you choose in the parent's status it self. So the expression should be something like

IF(
 "are all related ordered items completed or cancelled", LIST("Closed - Complete", "Cancelled",
 LIST(here is all the rest of allowed status)
)

 to return a list of allowed values depending on the status of the related ordered items.

The expression I proposed was for the "are all related ordered items completed or cancelled" part.

Hope this clarifies it a bit.

[Suvrutt_Gurjar]

Thank you for additional details. No, that is not a list expression. It is a reference column and the list displays due to the nature of the ref column. But your additional information does help to suggest an expression. You may want to try the below expression

IF(

COUNT( [Related Ordered Items])= COUNT( SELECT([Related Ordered Items][Ordered Item Status], OR([Ordered Item Status]="Cancelled", [Ordered Item Status]="Packed - Complete") )) , 

Order_Status[Key Column] ,

Order_Status[Key Column]- LIST( "Key column value of Closed - Complete status record in the Order_Status table"  ,  "Key column value of Closed - Cancelled status record in the Order_Status table" ))

)

Edit: Made some changes to the suggested expression above.