COUNT SELECT Formula

AppSheet AppSheet Q&A
1

Here is the basic requirement
I want the COUNT in a formula to only do a COUNT when the Bundle Status = “Ready To Ship”
COUNT(SELECT (Order Bundle Detail**[Bundle Status]**, [Job ID] = [_THISROW].[Job ID]))

What I want to achieve is to set the PARENT Record Status to “Ready To Ship” if ALL the CHILDREN Record Statuses are equal to “Ready To Ship”

So I have already got the COUNT of how many CHILDREN Records exist in another VC. All I want to do now is to do the same COUNT of these CHILDREN Records where Status is equal to “Ready To Ship”. If the COUNT of Number of CHILDREN Records = COUNT of the Number of CHILDREN Records where the Status is “Ready To Ship”, then I know that I can set the PARENT Record Status also to “Ready To Ship”

How would I write a formula for this?

2

This expression should give you the child rows with a Bundle Status of Ready To Ship:

SELECT(
  [Related Order Bundle Details][RowKey],
  AND(
    ISNOTBLANK([Bundle Status]),
    ([Bundle Status] = "Ready To Ship")
  )
)

Replace RowKey with the name of the table’s key column.

You could compare a count of that list with the count of all children:

(
  COUNT([Related Order Bundle Details][RowKey])
  = COUNT(
    SELECT(
      [Related Order Bundle Details][RowKey],
      AND(
        ISNOTBLANK([Bundle Status]),
        ([Bundle Status] = "Ready To Ship")
      )
    )
  )
)

Alternatively, you could just check whether there are any children that are not ready:

ISBLANK(
  SELECT(
    [Related Order Bundle Details][RowKey],
    OR(
      ISBLANK([Bundle Status]),
      ([Bundle Status] <> "Ready To Ship")
    )
  )
)
5 Likes
3

@Henry_Scott
Basically; if you have a VC with an AppFormula like this in your parent table:

COUNT([Related Order Details][KeyColumn])

this will produce a list of number of child records for each corresponding parent record. And if you set another VC with an AppFormula like this in your parent table:

COUNT(SELECT([Related Order Details][KeyColumn],[Bundle Status]="Ready To Ship"))

this will produce a list of number of child records for each corresponding parent record where [Bundle Status]=“Ready To Ship”

5 Likes
4

I love how far the platform has come.

5

Thank you @Steve and @LeventK. I will try it out over the weekend

1 Like
6

@Steve and @LeventK - working like a charm. I tried both solutions. Thanks

2 Likes