AnsweredAssumed Answered

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

Question asked by terraformer on Jun 9, 2009
Latest reply on Jun 10, 2009 by TSGal


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


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?




Iterator script 



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 ]