I have a FMP8 database designed to match assessment records with a separate student database. I generate a unique key using student name and birthdate, and do a lookup in the student database for a student ID number. This works fine. However, I have a school with a "dirty" student database, where one student has multiple records in the file (the duplicate records have the same student ID). As a result, when I do an export from my assessment database, I'm getting a valid record (assessment results) matched to the first student DB record, and then a blank record (no assessment results) for the second student record in the DB. I know in other databases, I can force a specific type of join, to only export data if it appears in the assessment table. There seems to be no way to force my version of Filemaker to use a specific join criteria, or at least I'm not finding it.

The "correct" solution would be to make sure the student databases are clean, and remove the duplicate records. However, I receive them from the schools periodically, and I'd have to clean up the file every time I receive an update. I'd like to be able to tell FMP to only match the first occurrence and avoid the blank records in the export.

Any suggestions? Thanks, in advance.

--> John

Import/Matching always processes all matching records one at a time in sequential order.

The only solution is to remove your duplicates from the current found set prior to attempting a match.

You can do this using a scripted process that marks all duplicate records and then omits them from the current found set.

Use ! to find all duplicate records. If you are using a complex key, you will need to combine them into 1 calc field to locate dups that way.

Sort the records making sure duplicates are sorted together.

Then use a loop to process the records one at a time so that it will mark the duplicates and leave the first record for each student unmarked.

So something like:

Enter Find Mode

Set Field[ Table::StudentID ; "!"]

Perform Find

Sort[ Table::StudentID ; Ascending ]

Go To record Request [First]

Loop

If ( $StuID ≠ Table::StudentID )

Set Variable[$StuID ; Table::StudentID ]

Else

Set Field [ Table::Mark "1" ]

End If

Go to Record Request [Next ; Exit After Last]

End Loop

Enter Find Mode

Set Field[ Table::Mark ; "1"]

Omit Record/Request

Perform Find

This should get you the found set you want to run your import/match on.

Thank you for the thorough response Brian, I should be able to make that work. I am already generating a quasi-complex key field for matching, so it should be no problem to identify duplicate records, and this will make a nice entry project to Filemaker scripting. Again, many thanks.

--> John