related table matching Filed?

[Devin]

I've got this trouble that I hope someone can lead me down the right row of thinking on this..

I've got a simple sales order document that has a related table for line items.. For each line item I've got a Filed being used as a Boolean.. (0 or 1).

For this document I need to know per order if a mixtures of 0 or 1 line items are in the related table. .. They don't care if they are 0's or all 1's..

Thanks

[Ocean West]

from the parent table you can do a Count ( child::boolean ) and a Sum ( child::boolean )  the count would give you say 6 child records the Sum would tell you 4 of them are true or 1.

 

[siroos12]

As Ocean West suggested, you could compare count of child records with sum of the boolean field. 

Below would be possible scenarios :

 

A) count = 0. Then no child records exist. 

B) count > 0 and sum = 0 Then there are some child records but the boolean field is set to 0 on all child records. 

C) count > 0 and sum > 0 and count = sum Then all child records have value of 1 in boolean field. 

D) count > 0 and sum > 0 and count <> sum Then there are child records with either 0 or 1 in boolean field. 

 

Hope that make sense,  a Friday midnight answer

 

 

 

 

 

[Devin]

Yes. this make perfect sense.. I just got done doing a quick test file and it does exactly what I need..

I ended up doing a calc field to review the count and sum fields doing a "If ( Sum = 0 ; "True" ; If ( Count > Sum ; "False" ; "True" ) )"

Seems to work exactly like I hoped. 

Thanks Guys!

[siroos12]

I think that won't work, the first "If" would return true if sum is equal to zero, this means all child records have 0 in boolean field. I think you should modify it this way :

 

If(count > sum;true ;false)

 

Note: if there is only one child record, the calculation return false as one child record is equal to all of child records. Therefore having a 1 in boolean field means all child records have a 1 and not a mix of 0s and 1s.

[Devin]

If all the related records have a 0 for Boolean then I would want True.. If all of them had 1 for the Boolean I wound want true.. I only want false if I had a mix on 1 and 0 for the related records..

I tested this with one record and it's working correctly if its a 0 or a 1. 

 

[siroos12]

On 5/12/2018 at 12:06 AM, Devin said:

If all the related records have a 0 for Boolean then I would want True.. If all of them had 1 for the Boolean I wound want true.. I only want false if I had a mix on 1 and 0 for the related records..

I tested this with one record and it's working correctly if its a 0 or a 1. 

 

Oh, sorry, you are right. I misunderstood it. Good luck!