7 Replies Latest reply on Feb 12, 2011 10:41 AM by CodySteele

    Relationship based on partially matching text



      Relationship based on partially matching text


      I am wondering if it possible to establish a relationship between fields where the exact text of field A matches a portion of the text in field B.  What I'm ultimately trying to get to is the ability to quickly filter my contact list based on industry specialty.  My field, Contacts::Coverage is a checkbox set of 10 or so predetermined industries.  In another layout dedicated to Firms (with a Contacts portal), I would like to let users select an industry from a global field that filters to the relevant matching contacts.  However, if a user selects "Technology" in the global filter field, I want to make sure that a contact whose coverage is "Materials, Technology" or "Technology, Financials" shows up.


      Any ideas are greatly appreciated. 

        • 1. Re: Relationship based on partially matching text



          The only thing that comes to mind on this one is a sort of round about way...I will watch other posts to learn a better way myself.


          After the global field is set, run a script that goes to the related table, performs a find for the pertinent word "Technology" and Replaces Field Contents in a marker field.  {Replace field contents in the marker field across the found set with "X"}.  Then go to back to your layout with the portal.


          Have your relationship be between a static global field set to "X" and the marker field.


          In this way, the entry of "Technology" would create a found set of records containing that value and mark them as related.


          Keep in mind to clear all marker fields before performing the find...


          This will, of course, break down if the initial global field is set to "ology"...it would only link up records that are findable based on the value entered.

          • 2. Re: Relationship based on partially matching text

            IF you are matching against whole words (Technology--not Tech), you can turn your text in Coverage into a list of individual words separated by carriage returns. You can then use that field in a relationship linking to your global field for either a portal or a conditional value list of matching values.


            Substitute ( TrimAll( Contacts::Coverage ; 1 ; 0 ) ; " " ; "¶" )


            will turn Apple Orange     Pear







            to function as your multi-value key.


            Here's a thread on setting up conditional value lists:

            Custom Value List?

            • 3. Re: Relationship based on partially matching text
                 That did it.  My coverage lists are actually already separated by carriage returns; I failed to mention this in my original post as I didn't realize what a difference it made.  I was surprised to learn that if I establish a relationship where a global filter field = reference field, and the global is set to "Technology" and the reference field was "Technology ¶ Business Services" the relationship stands.
              • 4. Re: Relationship based on partially matching text

                Return separated lists of values are an "OR" type of matching in Filemaker when used as a key in a relationship.


                If you stored each value in a different repetition of the same field--used as a key--will also match values in this same fashion.

                • 5. Re: Relationship based on partially matching text

                  Hi Phil, I think FMI is very unclear on TrimAll() trimspaces.  It says 0 for false and 1 for true and says:


                  Set trimSpaces to True (1) if you want to include the removal of full-width spaces between non-Roman and Roman characters. Set trimSpaces to False (0) if you do not. 


                  It sounds like you must set trimSpaces to 1 or it won't remove extra spaces.  But full-width spaces between Roman characters are word separators.  Thing is ... TrimAll ( field ; 0 ; 0 ) is all that is needed unless you use Characters within the non-Roman range (belonging to the CJK symbols/punctuations area, Hiragana, Katakana, Bopomofo, Hangul compatibility Jamo, Kanbun, CJK unified ideographs, and so on. :smileyhappy:

                  • 6. Re: Relationship based on partially matching text

                    Thanks LaRetta,


                    TrimAll is not a function I've had to use very often. I was attempting to figure out a less than clear help entry (I believe you mentioned in another post that it was less than clear also), to use the right parameters. The ones I tested, worked for me so I posted that option. If parameters of 0, 0 also work, all the better to know...

                    • 7. Re: Relationship based on partially matching text

                      I'd like to piggy back on this request.  My situation is a bit different though in that 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 in the syntax of a filter.  What function can I use to accomplish this?  My table structures are: