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

AppSheet AppSheet Q&A
,
1

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)

image.png
image.png344×586 25.5 KB

image.png
image.png326×578 19.3 KB

2

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?

1 Like
3

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.

2 Likes
4

@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.
1 Like
5

@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”))))

EricDevy_0-1666701752884.png
EricDevy_0-1666701752884.png333×129 5.98 KB

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

6

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” ))

)

1 Like
7

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)

8

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

1 Like
9

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.

1 Like
10

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

EricDevy_0-1666769068051.png
EricDevy_0-1666769068051.png327×130 4.68 KB

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
11

As requested before, please share the following details

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

1 Like
12

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

EricDevy_0-1667198441627.png
EricDevy_0-1667198441627.png337×587 25.3 KB

EricDevy_1-1667198468268.png
EricDevy_1-1667198468268.png732×689 39.9 KB

13

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.

1 Like