# related table matching Filed?

This topic is 1553 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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.

• 2
##### Share on other sites

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

##### Share on other sites

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!

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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!

##### Share on other sites

This topic is 1553 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account