Jump to content
Sign in to follow this  
sokabs

Confusing key fields and relationships problem

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Who Viewed the Topic

    2 members have viewed this topic:
    Veselko  nexgen 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.