3 Replies Latest reply on Nov 8, 2013 10:25 AM by philmodjunk

    A University Conundrum



      A University Conundrum


                I have the following setup:
                a table with contacts
                ::contact name
                a table with universities
                ::university name
      contacts::company is related to universities::university name
                I make connections with people at universities and when I add a contact to my database, I want to associate it with the appropriate school.
                This way, when I go to look at my universities table layout, I see all the contacts I associated with a school through a portal.
                The universities in the database have long-hand standardized names, but different universities have different standards, some use "the" before university, some use dashes to indicate a secondary campus, while others use 'at', etc. 
                For example, I want to make it so that if I type in UC Irvine, the database will start to auto-suggest names, i.e. I want to see "University of California-Irvine" pop up as I type. 
                I am looking for advise on the best way to get the database to recognize alternative university names.


        • 1. Re: A University Conundrum

               Linking two tables by a name field can be problematic. Imagine what you have to do if a school changes it's name, for example. Using auto-entered ID's for the primary key avoids those issues. And it is possible to set up an auto-complete enabled drop down list with school names and still link records by ID. See this demo for an example:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

               And a calculation field can be set up that omits specified words such as "The" from the beginning of the school name and such a field could be used as the value source for the drop down list demo'd in the above demo file.

               You may also find the search portals used in this demo file of interest: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               If you are using FileMaker 12, Use Open from the File menu to open this file and get a copy converted to the .fmp12 file format.


          • 2. Re: A University Conundrum
                 I obtained a list of standardized school names along with unique IDs from the Dept of Education. So now I have a unique identifier to work with.
                 Now I have a bunch of Contacts::Company Name entries that would roughly correspond to the new standardized list of schools.
                 My goal is to get the Contacts::Company Name to match up to Schools::School Name via the newly acquired unique ID.
                 I could export the Contacts::Company Name back to excel and do something like VLOOKUP to do a partial match, but I wonder if there's a more efficient way of going about matching and reconciling the two lists within FileMaker.
            • 3. Re: A University Conundrum
                        I obtained a list of standardized school names along with unique IDs from the Dept of Education. So now I have a unique identifier to work with.
                   That is still not the optimum value to use as a primary key. Any data imported from an outsides source might be changed on you in the future. By all means import such data into your database as it can be useful for searches and sorts, but use an internally generated ID such as a serial number field or get ( UUID ) to uniquely identify each record.