AnsweredAssumed Answered

Moving data based on other fields in the record

Question asked by ShaneB on Jul 26, 2013
Latest reply on Jul 26, 2013 by philmodjunk

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

Outcomes