Jump to content

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

Link to comment
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.

 

  • Like 2
Link to comment
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 ;)

 

 

 

 

 

Link to comment
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!

Link to comment
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.

Link to comment
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. 

 

Link to comment
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!

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.