4 Replies Latest reply on Aug 8, 2011 4:04 AM by DgWater

    Complex Importing Data issue

    DgWater

      Title

      Complex Importing Data issue

      Post

      Hi 

      I am trying to do something with import that I have never done before, and I am stumped, I hope someone can help me. 

      I am trying to import data into a filemaker database. I am using filemaker 11.  The challenge I have is to "reorganize the data". The current datafile comes from a mysql database. The database tracks  stream water quality sampling - keyed by a "survey" event.  The current file is organized using the following fields- each piece data was given a seperate id. There are 682 events. that I am trying to reorganize the data into. 

      Data File (stores all of the sampling data)

      - ID,  Parameter ID, Protocol ID, Event ID, num value, text value, notes, timestamp

       I want to import that data into a filemaker database/ table  that is organized by event ids with each event table containing fields for each of the different types of data collected. There are approximatley 60 different fields.

      New Database Struture

      Event ID, Parameter ID, Protocol ID, numvalue, text value, notes, timestamp ; Parameter Id, Protocol ID, numvalue, text value, notes, timestamp - and etc (so it all 60 different fields are imported for each event. 

      --

      So I am stuck on how to do this.

      Thank you in advance for any suggestions. 

        • 1. Re: Complex Importing Data issue
          philmodjunk

          I don't understand your new structure--which lists the same fields by name twice in the same record--something that is not possible in a FileMaker table.

          • 2. Re: Complex Importing Data issue
            DgWater

            Unfortantely I think I did not do a good job of explaining the new table structure. - and I am not sure I can explain it properly - but it here it goes.

            The new table structure has a different  field for each parameter. For example  there is a field for water temperature, dissolved oxg, width of stream, etc. 

            The datafile  I want to import form is organized in the following method with  examples - for each record it has the following fields

            - ID,  Parameter ID, Protocol ID, Event ID, num value, text value, notes, timestamp;

             - ID, Parameter ID (Water Temp), Event ID (1), 30, null, notes, timestamp;

             - ID, Paremeter ID (Dissovled Oxy), Event ID (1), 12, null, notes, timestamp ;

             - ID, Parameter ID (Water Temp), Event ID (2), 30, null, notes, timestamp;

             - ID, Paremeter ID (Dissovled Oxy), Event ID (2), 12, null, notes, timestamp ;

            In Filemaker I have a database table called Events - with fields for each type of parameter so a record would look like this

            - Event ID (1),  Water Temp (30), Dissolved Oxy (12); and so on for each parameter

            - Event ID (2) , Water Temp  (25), Dissolved Oxy (15); 

            Here is an Autofill linked/portal  field, when data entered in other field. As you can see from the post, You previously helped me  think how to convert the old data files into a filemaker data file, now that's done I am trying to figure out how to import that data. 

            as always thanks for the assistance.

            Don

            • 3. Re: Complex Importing Data issue
              mgores

              I would think that in the target file you could set up a self join relationship on the EventID field then display related records to see the water temp, dissolved O2, stream width, etc for that EventID.

              The other way would be to have all those fields per record in the target file, then do several imports, using EventID as a match field, to get all the parameter fields populated.

              • 4. Re: Complex Importing Data issue
                DgWater

                Thank you for your help. Your 2nd solution worked!  It is a little more ore time consuming having to export each parameter out, then into the new file, but it solves my issue.  The 1st solution, the self join worked in displaying records but I was unable to export the related records.

                Don