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.