search related field contents

[summergirl]

This seems like it should be easy, but I can't seem to get it.

File 1 contains a portal showing the "program" text field from File 2. Entries are from a predefined value list.

I need to create calculation fields in File 1 to be able to identify records that have match my search criteria. Example: PatternCount (<File 1to File 2 relationship::program>, "family literacy") returns a 1 only if Family Literacy is the FIRST entry in the program field. How can I make the calculation return a 1 if ANY entry in the program field matches my search?

Thanks for your help!

Mary Z

[danjacoby]

Don't know if it's possible, and I'm too lazy to test this, but...

Create a global field in File1 which will contain whatever it is you're looking up (this way, you can look up different values at different times).

Create a calc field in File2 with an "If" statement, using the PatternCount command, having it look for the contents of the global field in File1, that returns a "1" if it's true, and a "0" if false. Make it return a number.

Create a calc field in File1 that is the Sum of the related calc field.

Let me know if it works.

[summergirl]

I tried a different approach that seems to have worked. Since I have a predefined value list, it made things easier. I created a marker field (number) for each item in the value list. Then I made a script that will (1) find all the records with the value list item #1, (2) paste a "1" into the appropriate marker field. Then I can use the marker field in other calculations.

This may be a little convoluted, but it does the trick.

Thanks for your suggestion!

[Ugo DI LUCA]

You would need 2 relationship.

1. Your File1ToFile2 relationship that is used for your portal

2. A Keyword relationship based on a global or text field at left side and the text field in File 2for the right side, which you want to search for.

Then Case(Count(::File1ToFile2::Record_ID) = Count(::Keyword:Record_ID), 1,0)