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

    ChristineT.

      Title

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

      Your post

      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