Join Table doesn't auto-update

[dnewman3139]

Right now I am just messing around with the trial version of FM7 to see if it is suitable for a project I will be working on. I have made a little Student, Teacher, Course database to play with.

I have five tables right now. The Student table which contains a Student ID, first name, last name, etc. The Teacher table which contains a Teacher ID, first name, last name, etc. The Course table which contains a Course ID, course title, course description, etc. There are also two join tables. The first is StudentToCourse which joins the Student table and Course table by ID, the second is the TeacherToCourse which joins the Teach table and Course table by ID.

I have setup the forms so that in the Student form there is a portal showing a list of courses that they are taking. This work well, but if I make a change to a course it is not reflected in the Portal.

For example, right now the portal only displays the Course IDs of the courses being taken. If I have two courses (say 1 and 2) and I change the course ID of one of them (change 2 to 5 lets say) the portal and the join table are not being updated to reflect the change (i.e. there is still a course 2 listed in the table when there is no longer a record with the course number 2).

How can I link this all together so that changes are reflected between these tables and portals?

Sorry for the uber long post : )

[Ender]

It sounds like your join tables have fields for Course ID, Course Title, Course Description, etc. If so, how are these fields defined? Are they using Lookups? If so, based on what?

>>I have setup the forms so that in the Student form there is a portal showing a list of courses that they are taking. This work well, but if I make a change to a course it is not reflected in the Portal.

Are you saying when you change the Course ID in the portal the other course information in the portal is not updating or are you saying when you change the course info in the Course table, the join table records are not reflecting the changes?

[QuinTech]

Newman! (said with invective... you may reply with an equally vicious "Jerry!' if you want)

Welcome to FM Forums!

My first comment would be that you probably shouldn't be changing the course ID number at all (and i'm guessing you're not, but just to be sure). If your goal is to change the name of the course the student is taking, make sure you are changing the ID number in the join table rather than in the Course table.

Does this help?

Jerry

[dnewman3139]

Jerry! (thanks, I will )

Well, The join tables only have fields for the two IDs.

For example in the StudentToCourse table I have a field for StudentID and a field for CourseID.

Yes, I was changing the CourseID in the Course table and was expecting that this change would be reflected in both the join table AND the portal.

I've just checked and the Student view does update the portal when I change the StudentToCourse join table. I suppose it makes sense that changing the CourseID in the Course table wouldn't change the StudentToCourse table.

Thanks for the help guys. So far FM seems to be one heck of a products, and I plan on using it for this upcomming project. I haven't been able to find anything else that can handle all the different media types and also provide access over the web.

[QuinTech]

CourseID in the Course table should never be changed. Once you have a unique index number, it's there for life. For example, you have student 1 and student 2 joined to course 1 (Psych 101). If you change the ID for Psych 101 from 1 to 2, now students 1 and 2 are joined to a non-existent course. If you change the ID for Bio 101 to 1, now students 1 and 2 are registered for Bio.

It sounds like you're on the right track at this point.

J