4 Replies Latest reply on May 13, 2015 9:46 AM by philmodjunk

    New to database work and FileMaker

    JenniferShubert

      Title

      New to database work and FileMaker

      Post

       

       

      Hi everyone, first, thanks in advance for any help or even just putting up with what's probably a silly question to most of you.  

      I'm trying to create a database that links information provided by schools with information provided by students.

      I want to link the entries by matching a few fields (State, Grade, School, First Name, Last Name) however, the problem I'm running into is that the names provided by schools doesn't always match the way that students write in their names.  For instance, schools might list the student name as John Smith Doe  while the student writes his name in as either John Smith or John Doe.  

      I have two tables set up each fields of State, Grade, School, First Name, Last Name 1, Last Name 2

      I'm wondering if there's a way to have FileMaker lookup/match the information if State, Grade, School, and First Name match and if either Last Name 1 matches Last Name 2, or if Last Name 1 matches last name 2

      Any tips/help would be hugely appreciated!

       

      - Jennifer (Research assistant turned new database manager)

       

        • 1. Re: New to database work and FileMaker
          FilmUser

          I’m not an expert, but I think most folks here will tell you that relating records between tables by using data fields (such as names, etc) is problematic. Someone's name can change, or be referenced inconsistently, as you mention.

          You probably want to use serial number fields, in field definitions, of  type "number", auto enter. Filemaker will assign sequential values, as records are created, which do not change during the life of the data base (and are protected from editing in all layouts). If records have been created without ID’s, they can be added as an option within the “replace field contents” in the Records menu.

           These fields, then, are used for relationships. Drop down lists can be created that allow selection of this number from a related table, but also display more useful information in the selection of the related record (like the name).

          There is a lot to this topic, but a good start is to research the terms, "Primary Key and Foreign Key" on this forum. Take the time to do this properly, it will pay off.

          • 2. Re: New to database work and FileMaker
            philmodjunk

            How do you get this info into your database? Is it entered directly by users or are you importing the data from other sources?

            Matching by a serial number is the way to go, but if you are importing the data from two different sources, you'll need to find the records before you can link then in such manner. That can be done, but messy data makes for messy results and a lot of record by record review to make sure that your system doesn't match the wrong records up to each other.

            • 3. Re: New to database work and FileMaker
              JenniferShubert

              Right now I'm pulling info from excel files.  We have an initial log that does have unique IDs assigned to each of our 2500 students.  I'm trying to match the roster data provided by schools with the data that has assigned unique IDs, that's where I'm running into troubles because the only info we have is student names, grades, schools, and state.  I'm just wondering if there's a way to match on the names other than going one by one.

               

              • 4. Re: New to database work and FileMaker
                philmodjunk

                Say you have two last name fields: Last1 and Last2. You can then define a calculation field in the same table with a text result as:

                List ( Last1 ; Last2). We'll name this field cLNameList. (a lower case "c" is how I name calculation fields.)

                Then a relationship like this:

                Table1::FirstName = Table2:FirstName AND
                Table1::School = Table2::School AND
                Table1::Grade = Table2::Grade AND
                Table1:LastName = Table2::cLNameList

                will match a record in Table1 to a record in Table2 if the firstname, school and Grade names match but only if the LastName matches to the Last1 OR Last2 names.

                Once you get a relationship that works, It would be a good idea to copy over the ID value so that you can transition to matching records by ID.