3 Replies Latest reply on Nov 3, 2008 9:09 AM by Kat4

    Creating a tricky calculation



      Creating a tricky calculation


      I want to do something similar to the function on the FMP starter solution in Contact Management where you can find a company by similar Name, Company , or City. I've got all the imformation to re-create that part, but what I want to modify is this:

      I want to find companies with a similar name. For example, we deal with a few companies which have two (or more) parts, one for Admin, one for Marketing (they must be listed separately because all information associated with these separate but associated entities is different), but they have at least one word in each of their names which is the same. I can't figure out which functions to use to collect/list Companies with a word within their name in common. A complication is that since we deal with many Galleries, which include the word "Gallery" in their title, I have to eliminate the possibility that the calculation brings up all companies with the word "gallery" in it.

      Can any one help with this?

      Should I just forget it?

      Thanks in advance for having a think!


        • 1. Re: Creating a tricky calculation



          Thank you for your post.


          I can't think of an easy way to do this.  You may possibly have several words in a field so you would need a way to evaluate every instance of each word.


          The easy part is not evaluating (or omitting) "Gallery" from the search.


          Create a field "DupField" of type Text.  We will use this to mark a record as a multiple.


          Here is one possible script:


          Enter Browse Mode []

          Show All Records

          Replace Field Contents [no dialog; "DupField"; "" ]  

          Go to Record [First]


             Set Variable [$string; Value:Company]

             Set Variable [$recnum; Value:Get (RecordNumber) ]


                If [Position ($string; " "; 1; 1) > 0] 

                   Set Variable [$search; Left ($string; Position ($string; " "; 1; 1) - 1) ]

                   Set Variable [$string; Right ($string; Length ($string) - Position ($string; " "; 1; 1)) ]


                   Set Variable [$search; $string]

                   Set Variable [$string; "" ]

                End If

                If [$search <> "Gallery" ] 

                   Enter Find Mode []

                   Set Field [Company; $search]

                   Perform Find []

                   If [Get (FoundCount) > 1]

                      Replace Field Contents [no dialog; "DupField"; "Yes" ]

                   End If

                End If 

                Exit Loop If [$string = "" )]

             End Loop

             Show All Records 

             Go to Record/Request/Page [No dialog; $recnum]

             Go to Record/Request/Page [Next; Exit after last]

          End Loop

          Enter Find Mode [] 

          Set Field [DupField; "Yes"

          Perform Find []



          The script finds all records and removes any previous information in the DupField.  Starting at the first record, we put the entire contents of the Company field into the variable $string.  We then pulled out each word of $string and searched for it.  If two or more records were found, then we replace DupField with "Yes" for all those found records.  If Gallery is one of the words, we don't search.  We continue with each word in the field until there aren't any more.  Then, we skip to the next record and do the same thing.  Once all the records are checked, we exit the loop and search for all entries in DupField.


          Hopefully, you can follow along with the script step.


          Let me know if you need clarification for any of the above steps.



          FileMaker, Inc. 

          • 2. Re: Creating a tricky calculation

            You could also think about having a calculated field upon which you create a relationship.  If your original field is called Name, set up a field NameMatch that looks like this:


            Substitute ( [" "; "¶"] ; ["Gallery"; ""] ; ["the"; ""]; ["of"; ""] )


            Then create a match from the table to itself off of NameMatch.


            The first part puts each word into its own line, so that it can be used as a match term.  The other parts eliminate common words that would give you false matches.


            You can now create a portal for "possible matches".



            • 3. Re: Creating a tricky calculation

              Thank you for the responses.

              I'm going to keep both these ideas for future reference.

              I'm still working on initial set up of the database, and feel, for the time being a little overwhelmed by the complexity of this.

              When I do get to trying it out, I'll let you know what happened.