Calculation: Return Y in Field1, If Field2 in any related records contains X
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.
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?