Back to developing my next data management setup, but having trouble.
My database is two tables. Courses and Instructors. Each have the usual IDs - Course ID, instructorID_fk, Instructor ID, etc.
What I need to do is VERY simple in the telling, but I'm failing to make it happen, even with all the years of experience I have with FM! Since I use FM infrequently of late, I am obviously missing some significant, but small detail.
Rather than rehash what I have tried - (frustration always morphs it to being way more complicated than it has to be) - I'd like help designing the database structure so that I can enjoy a good dope-slap when I realize what a simplistic mistake I made.
So here's what I am after:
- Courses table with Course ID, InstructorID_fk, Course Name, and other details.
- Instructor's table with Instructor_ID, etc...
- On the instructor's layout I want a portal where I can pick courses using a drop-down list from a value list created using either the Course ID field in the Courses table or the Course name field value list.
- I want to be able to add multiple courses to the instructor's record and remove them without affecting the Courses table. (I know the relationship setup rules to prevent this)
- I want to keep it as simple as it can be.
What I know - I need a relationship, and tried to use InstructorID_fk and InstructorID to make that relationship. Something doesn't make sense with that choice, but I can't figure out what makes more sense - which is why I need help.
What has kept happening in previous attempts is that adding a course to the instructor's page in the portal, which had the Course ID and Course Name fields from the Courses table in it, does not work. I can pick a course ID, but the name does not fill in (the ID remains), but when I try to add a second course the same way, it replaces the first as if the background data is not created or it reverts back to the first id I entered when I hit enter.
Thanks for your help!