3 Replies Latest reply on Jul 26, 2013 10:44 PM by philmodjunk

    Moving data based on other fields in the record

    ShaneB

      Title

      Moving data based on other fields in the record

      Post

      Essentially this is my scenario: I am importing flat data from a spreadsheet into a relational database. There are "datapoints" that are raw pieces of data and "observations" based off of those datapoints. I have attached a screengrab of the database structure.
            
           In the spreadsheet, datapoints are occasionally repeated and observations are unique. I have already imported the datapoints and observations and removed duplicate datapoints, and each datapoint and each observation has a unique serial. Now I want to match the datapoints and observations together in a parent/child relationship table. Because the datapoints are not unique I can't just use each original record and move the data around like that. I want to use the original spreadsheet data and do something like this (this syntax is entirely made up, but I hope it makes sense).
            
      where temp_import::orig_datapoint = DataPoints::datapoint AND temp_import::orig_observation = Observations::observation_text PUT DataPoints::__datapoint_id_pk into ObservationsDatapoints::__datapoint_id_fk AND Observations::__observation_id_pk into ObservationsDatapoints::__observation_id_fk
            
           I would also like to populate the ObservationTheme table in a similar way, but I imagine once I understand this I can just use the same tactic.
            
           Thanks.

      database3.PNG

        • 1. Re: Moving data based on other fields in the record
          philmodjunk

               It would seem that you have a many to many relationship between Observations and DataPoints with a table named ObservationsDatapoints set up as the join table.

               I think you need to create records in that join table that correclty link observations and datapoints to each other.

          • 2. Re: Moving data based on other fields in the record
            ShaneB

                 That's what I thought I explained in my post. Maybe I didn't. I need to populate those fields but match datapoints and observations together based on specific matching conditions from the original data. I typed out what those conditions are, but I don't have any idea how to express this in FileMaker.

            • 3. Re: Moving data based on other fields in the record
              philmodjunk

                   Your original post made no mention of a Join table so I was checking to make sure you understood that was what was needed. Especially since you are NOT matching observations to Datapoints in a parent child relaitonship.

                   And I found your "where" statement confusing. Can you post an example of the actual data? Just a few rows of data with the columns identified should be sufficient to enable someone to suggest a script that can loop through your imported data and populate the needed records.