Changing a Work Order Status

AppSheet AppSheet Q&A
,
1

I’m creating a bot to update the status column of the parent table to “reconcile” once all the child statuses are all in “reconcile” and I can’t for the life of me figure out what expression to use.

right now I have this:

AND([Work Order Status]=“Working”,IN(“Reconcile”,[Related Order Items][Status]))

This doesn’t seem to work to ensure ALL order items are in “Reconcile” status.

1 Like
2

AND(
[Work Order Status] = “Working”,
count(Unique([Related Order Items][Status])) = 1,
index([Related Order Items][Status], 1) = “Reconcile”
)

The way I would actually do this is as follows:

  1. Create a data subset (slice) of the child records for your “Un-Reconciled” records.

    1. These are the records that HAVEN’T been reconciled yet.

  2. Go to the parent table and copy the REF_ROWS() already there.

    1. Replace the table name (the first part in quotes) with the name of your slice
    2. This will create a subset of the records (for the parent) that are in the “un-reconciled” status.
      1. This is essentially the same result of the following formula:
        Select(Table[ID], AND([Parent_Link] = [_thisrow].[ID], [Status] = “Whatever”))
        - But it doesn’t re-query your database to get the answer, it’s all already kinda there in the reference data.

With this status, now you can do a TON of programming and conditional stuff.

  • IsBlank([Remaining_Records_To_Reconcile])
  • IsNotBlank([Remaining_Records_To_Reconcile])

If you set things up this way, then your trigger would be the IsBlank() status of that column of remaining things.

This is a great way to get your status things to operate in an efficient, automated, and extremely complex way. (^_^)

2 Likes
3

@MultiTech ,

Stand by while I read that again :grinning_face_with_smiling_eyes:

1 Like
4

@MultiTech ,

So I tried this, but at step 2, when I copy the REF_ROWS(“Work Order Form”,“Work_Order_Number”) and add a virtual list column with REF_ROWS(“Not_Reconciled”,“Work_Order_Number”), it shows the column in the view, but it’s empty with 0 entries. What am I doing wrong?