4 Replies Latest reply on Sep 7, 2016 4:23 PM by philmodjunk

    Script for match field


      I'm new to the FM platform and need help with a script that inserts a matched ID field based on a non-unique field...My primary table has a list of names that have a unique ID...I get data from my clients that have the names but does not have the unique ID matched to it.  Is there an easy script to generate the ID into a match field on the secondary table if the name field matches up.  Thank you.

        • 1. Re: Script for match field
          David Moyer


          Not highly recommended, but you can create relationships based on text fields.  You should be able to create a relationship based on the names (or a concatenation of first-middle-last, etc.).  You would then use this relationship to assign a numerical key value.

          Here's a demo of using three text keys in combination ... (it's FM14 - and the demo is mostly a "data entry" demo.)

          • 2. Re: Script for match field

            Thank you David...I think I'll just try to set it up using the name field as the matched field for now...I was hoping to use the ID that is already associated with the name in the main table...If I'm looking at it correctly it appears that your solution creates a new ID situation.

            • 3. Re: Script for match field
              David Moyer

              eesh - just to be clear, my solution relies on unique names ... no two John Does.

              In the past, I've created "hopefully unique ID's", also known as "hash codes" ... a concatenation.  A simple example would be

              = first initial & middle initial & last name & GetAsNumber(Address) & Zip code

              You can use this as long as there is exactly one match - if not, a human needs to review it, and/or you need to fine tune your hashing algorithm.

              • 4. Re: Script for match field

                It's not terribly difficult to include code that identifies multiple matching and presents the user with a list of the matching names--with additional data supplied--where the user then selects the specific name that they want.


                The same code can, if desired, detect that no name matches and present the user with an option to create a new record with the new name.


                The text field used in the matching can even be a global field that is used solely for the purpose of looking up the corresponding ID number.