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 |
---|---|
32 | |
31 | |
30 | |
19 | |
16 |