# Calculation: Return Y in Field1,  If Field2 in any related records contains X

Question asked by ChristineT. on Dec 10, 2010
Latest reply on Dec 10, 2010 by philmodjunk

### Title

Calculation: Return Y in Field1,  If Field2 in any related records contains X

Hello,

I am trying to get a field (Field1) to look in a different field in a related table (Field2), assess the contents of Field2 for all the related records, then return a result (yes or no) based on those contents.

I was trying to do this in one step by making Field1 a calculation.  Currently I have not found a solution using this method (see Failed 1st Attempt below).

My workaround is a 2-step solution: Create a new field (Field3) which just lists the Field2 contents, then have Field1 be a PatternCount of the contents of Field3. It works but I am wondering if there is a better solution—?

Failed 1st Attempt:

Field1 =  If ( PatternCount ( RELATEDTABLE::Field2; "Yes") ; "YES"; "NO")

The problem appears to be that the pattern count is only looking in one of the related records instead of all N.

Successful workaround:

Create new field Field3:

Field3 =  List ( RELATEDTABLE::Field2 )

Then use Field3 in the Field1 calculation:

Field1 = If ( PatternCount ( Field3; "Yes") ; "YES"; "NO")

Anyone know if this can be done in one step instead?

Thanks,

Christine