3 Replies Latest reply on Feb 14, 2011 11:53 AM by Sorbsbuster

    Filtering Text in a Portal with a Partial Match

    CodySteele

      Title

      Filtering Text in a Portal with a Partial Match

      Post

      I've got two tables which I have related ALL records to one another.   I need a filter now to display those records from one table which  partially match the text from a field in the other table.

      I have  one table with words.  I have another table with phrases.  In the layout  view of the words table, I want to see those records from the phrases  table in a portal which are filtered by the criteria that they contain  the word currently being displayed.  Unlike in a find where I would  simply type *"myword", I can't use that in the syntax of a filter.   What function can I use to accomplish this?  My table structures are:

      WORDS

      EnglishWord

      SpanishWord

      PHRASES

      EnglishPhrase

      SpanishPhrase

      Thanks for any help!

        • 1. Re: Filtering Text in a Portal with a Partial Match
          Sorbsbuster

          From memory, you can have matches by multiple values.  For example, the field with values A¶B¶C will show in a portal the related records in another table with A, B, or C in the related field.

          So you could have in your phrases table a calculated field that the complements the PhraseField, with all of the spaces substituted with the ¶ character.  You may have to substitute some other characters, too.

          Then link your WordField to that mirrored, calculated field.

          (I hope my memory is correct - the only way I may have it wrong is that it may only work the other way around: A will match to A¶B¶C, but not vice-versa.)

          • 2. Re: Filtering Text in a Portal with a Partial Match
            philmodjunk

            Given your use of a * wildcard in your example, I think you would want "Apple" to match to all of the following:

            Apple, Apples, AppleSauce, Crabapple.

            If so, your filter expression can be this:

            PatternCount ( PortalTable::PhraseField ; LayoutTable::WordField )

            PatternCount will return 0 if WordField is not found in PhraseField.

            This method only works with FileMaker 11.

            • 3. Re: Filtering Text in a Portal with a Partial Match
              Sorbsbuster

              Cody - did you mean to use the wildcard character?  If you did, you will get the results that PhilModJunk explains - ie: the pattern of letters could be buried inside a longer word.