Auto update on a relation

[damaru]

Hi all,

This question was probalbly asked many time but I couldn't find it trought all the post here so if anyone can point me toward the solution it would be great!

Filemaker pro 7, I have a student table and a course table and a registration table, when I register a student to a course it create a registration entry with info from the student and the course. So far so good.

What I want to do is when I change the e-mail of the student in the student table, then it would automatically update in the registration table. (I started to creat a script for that but then I realize that there should be something with relation and tables that would do the trick)

thanks

[Fenton]

Personal information for the student, especially changeable data such as an email address or phone#, should ONLY be in the Student table. It can be visible in other tables via a relationship to student. "Redundant" data (which is what this is) has this problem of updating, so should be avoided; in which case either it cannot be modifiable, or a foolproof mechanism must be implimented to force the update across tables (non-trivial).

[damaru]

Yes I tryed to keep all the info on the contact table but I came up to trouble when I was trying to create report and mailling list and lots of other usages...

Thanks, I'll keep on working on my script then...

I am amaze that there is nothing in the core of filemaker to automate this type of update in between table....

I'll keep on the search

thanks

[T-Square]

damaru--

Don't work on your script; Fenton was explaining to you that you don't NEED to store this information in your Registration table--all you need is the StudentID, which makes it possible for you to view all the Student information in the related Student table. That's where the power of relational databases comes in.

You're having troubles building the reports: that's a different problem. FM is fully capable of displaying data from related tables, so you need to figure out how to build the report in a relational environment.

The reason that there isn't anything "in the core" of FM for maintaining redundant data is that it's not the best or most efficient approach to take. There is quite a bit in the FM documentation about relational structures--look under Designing and Creating Databases: Working With Related Tables and Files in the Help contents. That will begin to explain what Fenton is directing you toward.

David

[damaru]

Hey T-square,

I see what you mean bu tI can't figure out a way to work out what I am trying to do. I'll give you an example, my database is a contact managment and a sutdent registration. The main form is the contact managment and there is a tab for course registration. In the tab registration theres a portal to the registration table showing related record. So if I want to show the course name and the place of the course and the price of the course I need to have these info 'copy' to the registration table when I create this registration. In the course layout, I have a portal to the registration at the bottom listing all the student in this course, so in order to do that the portal link to the registration table wich needs to have the information of the student in there.

I might not understand the how to of filemaker yet so I'll do the reading you suggest, but if you understand my needs and have a simplyer way to do so let me know.

thanks

[damaru]

Thanks for the reading,

I got around the relookup fields for now wich do the trick, but I got your point and my database structure might not be the best starting with, so I am looking into ways to rebuild it in a simplier way...

thanks

[T-Square]

Well, let's try again.

I will assume the following structure:

• Tables: People, Classes, Registrations
  
• Important Fields (Fields used for relationships): People::PeopleID, Classes::ClassID; Registrations::StudentID, Registrations::CourseID
  
• Table Occurrences (TOs) in the Relationships Graph (The table that the TO is based on is in brackets). I am purposefully using different names for the TOs so that it's clear we're dealing with the TOs and not the underlying tables:
  Students[People]
  Courses[Classes]
  ClassMembers[Registrations]
  
• Relationships on the Graph:
  Students::PeopleID<->ClassMembers::PeopleID
  ClassMembers::CourseID<->Courses::ClassID
  

Now, your Registrations portal will be built on ClassMembers, and on it, you can put fields from the Courses TO (such as the Course Name, etc.). Your ClassMembers table does not store the course name, so if you change the class name, the registration entry will "change". The data is not stored in two places, and you do not need lookups.

Lookups CAN be very useful--and there's a place in this setup where that's probably the case. Presumably, each Course will have the amount the course costs (the tuition) stored in the course record. However, there are many reasons why a given student's tuition might be changed from the base tuition (for example, discounts, early registrations, etc.). In this case, you will probably want to transfer the Course::Tuition value into the ClassMember::Tuition field, where it can then be stored and modified as needed.

HTH,

David