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?
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")
)
)
)
@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”
I love how far the platform has come.
User | Count |
---|---|
42 | |
25 | |
25 | |
17 | |
12 |