Title

Importing into two related tables from Excel to Filemaker

Post

Hello always helpful Filemaker geniuses! I need your help again...

I have a table of people that has a one-to-many relationship with a table of test incidences. There is only one possible test they can take (like for reference, imagine you are a company that administers ONLY the basic SAT), but they can take it multiple times.

I have an old Excel spreadsheet of people who took the test on various dates. I want to both import these people's names, addresses, and emails into the "People" table as new records (I have checked to make sure none are already in there), and also import their test dates and scores as new records in the related table of test incidences. Is there a way to do this easily? If I assign each new person entry with a unique People ID number (the primary key on the People table that relates to a foreign key on the Test Table), will it automatically transfer all their personal information while I import to the Test table?

The basic mechanics are fairly straight forward. You can do two imports of the data, once into your people table and one into your test data table. The trick here is to correctly link the imported records by ID number after you've imported. You don't describe any column in your spreadsheet that uniquely identifies each person. The name column or columns may serve, but you may also have two or more people with the same name--so you'll need to check on that first.

If name is the only identifier available, you'll need to set up this relationship at least temporarily:

People::NameField = TestData::NameField

Make sure that you enable the auto enter options when importing into people so that each new record get's a new name. Also specify unique values for the name and "validate always" so that multiple entries get entered only once in the name table.

You can also use Replace Field Contents to assign serial number ID's to your newly imported records right after you import them. Sometimes, this works better if you have other auto-enter options you don't want activated during import.

Either way, once your people records have ID numbers, you can use Replace field Contents to copy the ID number from the related People Record into each TestData record to link the two. After doing so, you can delete teh name field from the test data table and can revert back to using an ID number based relationship to link your two tables.

Thank you that helps. I do have a unique identifier in the Person table (KP_Id_person) that is an autogenerated serial number everytime a new record is created and connects to (KF_id_person) in the Test table.

Like So:

People::KP_id_person=Test::KF_id_person

So I import all the people first, take the KP_Id_person from the set of newly imported records and add this number as a new column (KP_Id_Person) to the spreadsheet. Then I import the spreadsheet again, but into the Test Table, and match the field "KP_Id_person" in the excel to the "Kf_id_person" in the Test table.