Import From Flat File Into Relational Db

[j_holtz]

Hi there,

I am new to FM but have experience with SQL and various program languages. I use FM 11 Advanced.

I have an old FM DB that has 3 tables and the main table, Communication, is for all practical purposes a flat file containing hundreds of fields.

I am creating a new relational DB.The new DB has many related tables. I need to import the data from the old DB's Communication table into multiple tables. I need to import using a script because I have to repeat the process for different countries' DBs.

I hope someone has done this before and can at least give a good method that worked for them.

I thought I could write a script to read each record from the old DB and insert the data into the appropriate tables in the new DB and after each insert get the new primary key from that table and use it to set the appropriate foreign key in a related table. I'm having trouble understanding how to do this in filemaker and would appreciate it if someone could point me in the right direction with the following steps:

1. Read records from a table one at a time (something like a loop that allows you to use the data from each record in subsequent steps)

2. Use fields read from a table and insert them in new table(s)

3. Get the newly created primary key(s) of newly created record(s)

4. Use primary key and update foreign key in another table to link related data

5. Read next record and continue from no.2

I hope this is possible in FM.

As I could not see a way to do this in FM I am looking at the import script step. I may be able to get it done by having numerous import steps to move the data from the single table in the old DB to multiple tables in the new DB with the old primary key used as the link between related data (I'll use the old primary key as the new primary in some tables and as the foreign key in other tables), but I foresee that getting it right will be challenging.

Please, if someone has any advise I'd really appreciate it.

[comment]

the main table, Communication, is for all practical purposes a flat file containing hundreds of fields.

I am not sure what exactly you mean by that. A "flat table" can mean either repeated parent data or multiple fields for multiple child records.

The latter would be usually handled by importing all records into a parent table (importing the primary key along with the parent fields) and multiple imports into the child table (each time importing the primary key along with a set of child fields).

The relationship based on the imported primary key can be used to populate the child's foreign key with the parent's new primary key.

It may be more convenient to do all this in a temp file and do the final imports from there.

[j_holtz]

I am not sure what exactly you mean by that. A "flat table" can mean either repeated parent data or multiple fields for multiple child records.

The latter would be usually handled by importing all records into a parent table (importing the primary key along with the parent fields) and multiple imports into the child table (each time importing the primary key along with a set of child fields).

The relationship based on the imported primary key can be used to populate the child's foreign key with the parent's new primary key.

It may be more convenient to do all this in a temp file and do the final imports from there.

Thanks Comment.

The table contains both times of data you mentioned if I understand them correctly. The flat table is used to store all detail about a person. To give an idea of the extend of the data stored in each record: This person can be a student, lecturer, staff member and/or donor. For students it stores information about courses finished; for lecturers it stores info on up to 20 classes given (number of students started, number of students finished, etc).

I suspect most imports will be straight forward as you said, the old DB primary key will be the primary key in one table and the foreign keys in another table to obtain one to many relationships. At this stage I'm not sure if I'll have to create many to many relationships but I think I might be able to handle those by adding purposeful "primary keys" to the old database for each set of fields. I not sure if this will make any sense to anyone but it is a bit hard to explain and outside the scope of my question for now.

At least now I have a good idea of how to accomplish it with multiple imports and I think this is the route I'll take.

To get back to my original question and as I try to figure out how to do things in filemaker: is there a way to access a set of records in a script, iterate through the records and update other tables with data from the record set?

Thanks

[comment]

is there a way to access a set of records in a script, iterate through the records and update other tables with data from the record set?

Sure - even several ways. But I am not sure what you you are starting with. You cannot do this DURING import. If you already have records in both tables, and a relationship between them, you can:

Show All Records

Replace Field Contents [ Child::ParentID ; Parent::ParentID ]

(this can be done from either table)

or loop through the parent records setting a variable to the ParentID, then go to related records and loop there setting a field to the variable, and come back to the outer loop.

This last method could also be used to create the child records - provided you have imported all their data into the parent table.

[j_holtz]

Thanks again, I think the easiest would be to use imports and import all data. After the import I can use the loop method to fix anything that could not be done during import.