1 Reply Latest reply on Dec 10, 2010 3:45 PM by philmodjunk

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

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

• 1. Re: Calculation: Return Y in Field1,  If Field2 in any related records contains X

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

As you have discovered, PatternCount ( RELATEDTABLE::Field2; "Yes") only refers to the "first" related record. You can also specify a sorted relationship to control which record is "first".

Given only values of "yes" and "no" are present in RelatedTable::Field2, you could also test for Max ( relatedTable::field2 ) = "yes"