3 Replies Latest reply on Feb 25, 2010 10:31 AM by philmodjunk

    Help request: Scripted import into multiple tables, no ID field in data

    Rillaith

      Title

      Help request: Scripted import into multiple tables, no ID field in data

      Post

      Using: FMP10 Advanced on Windows

      FMP experience: First time user

      DB experience: I dream in SQL - MySQL is my normal system

       

      Task: Converting several standalone FMP6 databases into a relational FMP10 database, normalising some tables into multiple tables in the process. Fair amount through the process - layouts, tables, fields, value lists all done, simple scripts done, complex ones in progress!

       

      Background:Essentially, I have a "people" table containing all people registered with the institution, and I have an "applications" table containing the details of all applications. An application refers to the personal information in the "people" table via a person ID.

       

       

      Issue:


      I need to import a new set of applications from a CSV. Previously (before normalisation), all the fields were in the "applications" table, and successful applicants were copied into the "people" table as a batch, at which point the person ID was created. There is therefore no person ID in the data for importing.

       

      I believe the solution is something akin to the following:

       

      1. Import data into a new table

      2. Update new table with "person ID" where date of birth & name match existing "people" records

      3. Cause all remaining records to have a newly generated "person ID" field (see below)

      4. Import appropriate fields into "applications" from new table

      5. Import remaining fields into "people" from new table

       

      What is the appropriate function or combination of functions for step 2? I'm not sure how to script "update newtable set personID = (select personID from people where dob=dob and name=name)" - the logic of implementing this in FMP script is quite a different way of thinking than pure SQL.

       

      Secondly, what's the best way to implement step 3? I have considered creating new "people" records and then updating "person ID" in the new table again (i.e. a repeat of step 2 as step 3b), or first inserting the new records in "people" if name & dob don't match, essentially reversing the order of the steps, or looking up the maximum value used for "person ID" and creating my own person IDs based on this rather than allowing them to be auto-generated. None of these feel like a particularly natural way to achieve my aim.

       

      Any advice, recommendations, or pointers to other urls that I haven't found would be greatly appreciated.

       

      Thanks,

       

      Sally

       

        • 1. Re: Help request: Scripted import into multiple tables, no ID field in data
          philmodjunk
            

          In your new table, let's call it TempImport so we have a name for it, you can set up a relationship that matches multiple fields to your People table. Using the fields you specify, you do this:

           

          People:: DOB = TempImport:: DOB  AND

          People:: Name = TempImport:: Name

           

          Now you can either loop through your record in TempImport and copy matching ID numbers into TempImport or you can use Replace Field Contents to update the entire found set in this way.

           

          Now you can perform a find on TempImport for empty ID fields to isolate all the "new" people records. Import records can then be used to import this found set of new people into your people table with the auto-enter option enabled so that each is assigned a new ID number.

           

          Repeat the above steps on your "new people" records to update them with the new ID numbers in People.

           

          Now you can import the application data from this table from TempImport with the ID number so that the records are properly related.

           

          Note, while unlikely, there's no absolute guarantee that you can't have two applicants named John Smith who also have the same date of birth....

          • 2. Re: Help request: Scripted import into multiple tables, no ID field in data
            Rillaith
              

            Fab, thank you for your help.

             

            Had another idea which may also work, using Set Next Serial Value to generate the relevant auto-IDs on the import table in the first place. It's been quite a learning curve to retrain my ways of thinking!

             

            Proviso also noted - fortunately, my cleint had the foresight to add a tickybox on their application form which solves it for us :) (i.e Have you previously applied...?)

            • 3. Re: Help request: Scripted import into multiple tables, no ID field in data
              philmodjunk
                

              I can see a way to make that work for your application records, but since you have to import the records into People, it would seem simpler to import the records and generate their serial numbers all in one go. However, if it works for you...

               

              "fortunately, my cleint had the foresight to add a tickybox on their application form which solves it for us "

              But what if two new clients are named "John Smith" and have the same DOB? Unlikely but far from impossible. If you can include an SSN or DR License number, you might be better off.