Confusing key fields and relationships problem

[sokabs]

I need help figuring out how to setup key fields for a FileMaker database that imports from another (mySQL) system and also allows for the creation of new records. The import brings in several tables (eg. student, address, advisor, courses, etc.) all related by a shared unique key field (student_pidm). I need to be able to take advantage of these relationships, but also need to be able to add new records to the same tables in FileMaker without using the same key field.

I have considered setting the tables up in FileMaker with two key fields each, and two different relationships. For example, the STUDENT table will have the key fields of "ID_Student" (Filemaker auto-serial) and "Student_PIDM" (from the original data source), and so will the ADDRESS, ADVISOR, etc. tables. For those records which I have imported, the relationship will be based on "Student_PIDM" while the records manually entered into FileMaker will use "ID_Student." This seems to break down when I try to view related records in a portal. Any ideas on how to best address this?

Just for further explanation, the records I am importing are coming from an Oracle-based Banner student management system that is administered by non-cooperative administrators. They are providing me with a read-only subset of the data that has been exported into a mySQL system, and which is limited to just those records that their (faulty) query is determining meet my criteria on a semester-by-semester basis. This list is not entirely accurate, and they have no interest in improving the query results, which forces me to have to manually enter records for those students they miss (which is a major pain, but at least they have given me most of the students). I cannot simply use the "Student_PIDM" record for these students, because it is only visible from within the Oracle system, and the records I am given to import will range in values tremendously.

Any ideas or suggestions will be greatly appreciated.

[comment]

If you import the student records first, and give each one your own unique StudentID, you can then have the other tables auto-enter/lookup the parent StudentID, based on an auxiliary relationship matching on Student_PIDM. Once that is done, you can base the "real" relationship on StudentID alone.

Alternatively, you could use calculation fields on both sides =

StudentID &¶& Student_PIDM

If there's danger of number collision, use a prefix e.g.

StudentID &¶& "PIDM" & Student_PIDM

[sokabs]

Thanks so much. I have opted for your first suggestion, which I had thought of earlier, but for some reason didn't try. This seems to be working just fine for me now. I know that this project seems so overwhelming at times that I find myself overlooking the simple solutions. Thanks for helping me see my way around this!