Calculation Field: Text Matching Related Records
Say there is a non-key field called PASSWORD in the current table. The current record contains "BluePotatoes" in the PASSWORD field.
Say that there are five related records in a different table. That table contains a non-key field called PASSWORD2.
How do I create a calculation field in the current table that outputs "TRUE" when one of the five related records in PASSWORD2 is an exact match for "BluePotatoes"?
I tried the following, but it doesn't work:
If ( PatternCount ( List ( RELATED_TABLE::PASSWORD2 ) ; PASSWORD ) ; "TRUE" )
The reason it doesn't work is because "BluePotatoes", "BluePo", "Blu", and "B" all report "TRUE". However, my application requires a match only when the full string matches. Does anyone know an easy way, or will I need to create a custom function for this?