3 Replies Latest reply on Oct 21, 2009 8:28 AM by philmodjunk

    Normalizing Data on Import?

    mrichman

      Title

      Normalizing Data on Import?

      Post

      I am creating my first FileMaker 10 solution. My source data is a single Excel worksheet. My target FM schema is normalized into several tables. How do I map the denormalized data from the Excel worksheet into the normalized schema on import?

        • 1. Re: Normalizing Data on Import?
          philmodjunk
            

          That depends on one very important detail:

           

          Does the spreadsheet data include the necessary data to populate each key field used in your relationships?

           

          If so, you can make repeated imports into each table, specifying just the columns that correspond to the fields of that particular table. There are field validation options to enforce uniqueness (use the "always" option) on primary key fields that will filter out duplicate entries during import.

           

          If not, then life gets difficult and the solutions depend greatly on the design details of your database and the data in your spreadsheet. Some people import data into a temporary table and then write a script that steps through the records analyzing the data in order to create the needed relational links while moving data from the temp table into the related tables.

          • 2. Re: Normalizing Data on Import?
            mrichman
              

            Yes (unless primary keys count). I have a worksheet called "Patients" which I need to import into two tables: Patients and Encounters. This is a one-to-many (Patient has many Encounters). In the Excel worksheet, the Encounter-specfic data would be overwritten for each encounter. In the FileMaker solution, this is now normalized with the ability to keep a history (hence the 1:N). So I can easily import all the Patient-specifc data into the Patients table, but how do I go about importing the other fields into the Encounters table, maintaining the association to PatientID? Just import twice - once for Patients and again for Encounters (mapping PatientID in Excel to Encounters.PatientID in FileMaker)?

             

            Thanks for your help! I'm really excited to get off of Excel/Access :smileyhappy:

            • 3. Re: Normalizing Data on Import?
              philmodjunk
                

              Just import twice - once for Patients and again for Encounters (mapping PatientID in Excel to Encounters.PatientID in FileMaker)?

               

              If I understand you correctly, yes.

               

              When you impoirt into patients, you can apply a "unique, validate always" rule to PatientID to filter out multiple instances of the same patient during import.