1 Reply Latest reply on Jun 10, 2009 12:59 PM by TSGal

    Importing flat data into relational db without key info in flat file

    terraformer

      Title

      Importing flat data into relational db without key info in flat file

      Post

      So, I have one of those things that is trivial in programming languages but is not so simple in FMPro. I am trying to import flat data into a relational db without any key info in said flat file. That means I may or may not have pieces of the data in the database already

      and thus need to update not just a main table but smaller related tables. The structure is simplified as follows (many more tables and relationships to deal with).

      Table 1 related 1 to many to Table 2 via key. The data contains in one table all of the data for table 1 & 2 but the keys are unknown for any data in table 2 that has not already existed. Table 2 is basically name/value pair lookup. So I have approached it by creating table 3 which is a snapshot of the data being imported. I in turn related the names in T3 to the name fields in T2. I then created a lookup field for the primary key in T2 in the T3 and when the data is imported, it looks up the real key using the name key. 

      I then need to create records in T2 with the name of the value in T3 and then fill in the key for that value in the lookup. I search for all blank entries in T3 lookup for the key in T2. Then my script tries to iterate over the blank records and fill them in. Here is where it blows up in my face. The script creates the record in T2 but fails to fill in the details and it loses the first search so I need to start it again. I am using one iterator script on T3 with a create new record script in T2 while passing script parameters into the create new record script. 

       

      How does one iterate over one found set in one table while creating new records in another table, then taking info back and populating fields in the found set? It should be easy but it is not working out terribly well. The scripts are below. What is the best way of handling this?

      TIA,

      Tom 

       

      Iterator script 

       

      Loop

      Go to Layout [ “T3” (T3) ]

      Perform Find [ Specified Find Requests: Find Records; Criteria: T3::T2-F1: “=” ] [ Restore ]

      Go to Record/Request/Page [ First ]

      Perform Script [ “New System”; Parameter: T3::F2 ]

      Set Field [ T3::T2-F1; $$lastSysID ]

      End Loop

      You will notice I have no exit from that loop. I had originally used get next/last and did the goto layout prior to the loop but the first get next after get first *got* me the next record in the wrong table and I ended up with a paused script waiting for input in the wrong table field. 

       

       New System

      Go to Layout [ “T2” (T2) ]

      Enter Browse Mode

      New Record/Request

      Set Field [ T3::T2-F2; Get ( ScriptParameter ) ]

      Set Variable [ $$lastSysID; Value:T2::T1 ]

       

       

        • 1. Re: Importing flat data into relational db without key info in flat file
          TSGal

          terraformer:

           

          Thank you for your post.

           

          Although I'm a little confused by your explanation, perhaps the following information will help.

           

          I assume you have already set up your lookups from T2 into T3.  If not, set that up first.

           

          Look at the following script, and let me know if this is what you are trying to accomplish.  If not, please provide some examples and we'll work from there.

           

          Go to Layout [ T3 ]

          Go to Record/Request/Page [ First ]

          Loop

             Set Variable [ $ID ; T3::ID ]

             Go to Layout [ T2 ]

             New Record/Request

             Set Field [ T2::ID ; $ID ]

             Go to Layout [ T3 ]

             Go to Record/Request/Page [ Next ; Exit after last ]

          End Loop

          Go to Layout [ T2 ]

          Relookup Field Contents [ No dialog ; T2::ID ]

           

          ----------

           

          In essence, this is copying the ID field from all entries in T3, and putting them into new records in T2.  When finished, I switch to T2 and perform a Relookup, which will lookup all the data in T3.

           

          Let me know if you need clarification for any of the above steps.

           

          TSGal

          FileMaker, Inc.