import into table only if keys exist in another?

[kenyee]

I'm trying to figure out how to import specific records into one table only if a matching ID exists in another related table? (e.g, firstname/lastname links both tables, but the tables contain different into).

The table is initially blank. The other table is loaded already. The data I have is a superset of the data I want to import, but I want to import only data that has matching first/lastnames in the table that is loaded.

The only other thing I could think of was to populate the keys into the new table from the old table, but I can't figure out how to get script to do this -P

If the keys are loaded into the new table, I can do an import and check off the setting that only updates records w/ existing keys.

[Fenton]

So, you've got 2 files, one with IDs (Main), one without (Import). And you want to "update" the records in the Main file with the data in the Import. You're saying that you're not needing to worry about overwritting data in Main, 'cause you're bringing data into only certain fields (which you'll set up when you choose the import fields).

You have a relationship between them, on firstname & lastname. The thing is, you would have to use that relationship to transfer the ID; so using the ID is kind of redundant; it won't increase the accuracy really.

What matters is that you do NOT have duplicates of first/lastname. As long as you're sure of that, you should be OK.

You should probably encase that calculation with the Trim() function, so you don't miss because of extra spaces; in fact do it around each part: Trim (firstname) & " " & Trim(lastname)

7 is touchy about extra spaces in relationship keys. Do it in both files.

I don't know how many records you've got in Main. But if it's a lot, then Import with matching can be slow. Because you have to Show All Records before the import (you can only match the found set).

To increase the speed dramatically, you can narrow down the Main records to only those which have a match in Import.

Create a global field (text) in Import.

Create a relationship from it to first/lastname in Main.

Put ONLY the first/lastname field on a layout in Imports

Show All Records

Go to the layout, Copy All Records

Go to a layout with the global field

Paste into the global field

Commit Record

Go To Related Record [show, "global to Main"]

That should be the matching records in Main. Now do the update matching import (into Main).

7 has a 2 GB text field limit, so you should be OK with pasting a lot of names.

[kenyee]

I should have described it as converting a flat file in Excel into a relational file in FM7.

Specifically, each row in the Excel file contains info on a student and the classes the student has taken. In another Excel worksheet is a list of all student contact info: addresses, phone#s, etc. The contact info is a superset of all the students (and also students that will never take the classes).

So, I imported the class info already into a FM7 table and linked the first/lastname to another FM7 relational table. Now, I want to do something like (if it had a real programming language)-

for each contact info row

if student first/last is in the ClassesTaken table

import the student contact info into the ContactInfo table

next row

This should be a fairly typical thing to do, but there's nothing in the online help about this (although FM7 hasn't had real relational tables until now)...

thanks,

ken

[kenyee]

Just to let others know in case they hit this. What I ended up doing was importing both tables into Excel and then using VB to look for matching IDs and deleting the rows that didn't have matching IDs. I then exported from Excel and re-imported into FM.

You can't do this in native Filemaker...