Auto update one field on different tables

Hi,

I have my master database on SQLExpress and I am using Filemaker Pro Advance 9 for different reporting stuff. I have linked different table from SQL and can do what I want but facing a little problem.

The master database have thousands of records but I want to show only on roll students records. I have created a local student table and created the unique field and imported the current students unique numbers in this field. This work fine but whenever somebody leave or new join I have to update the unique number for all current records in local table. Can be automated?

The second problem, we have 15 subjects and each subject have own local table and they have unique field to show the only current students, is it possible when I update student table the subject table get updated as well?

Please see attached for table relations.(sims, stud_student is ODBC table and all other are local)

Thanks

Z.Ahmad



filemaker.png
za_pakAsked:
Who is Participating?
 
za_pakConnect With a Mentor Author Commented:

I thought abut this when I designed the database that keep the minimum number of tables but unfortunately  the management instruct me to do like this. There is reason to keep each subject separate. At the moment we have only few field in each subject table but we have plan to add more filed in each subject. So if we have 15 subjects and each subject have 30 fields this will be very big table had to trace if you want make some change. That is the reason I am looking something, like i have one field to update that can auto update in all tables. Thanks


0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>is it possible when I update student table the subject table get updated as well?
I highly recommend against this, as it would mean that you have two locations for current/not current students.

A better idea would be to keep the current/not current flag solely in the student table, and then have (1:M) a foreign key in Students to indicate what department they belong to, or (M:M) another table of Students-to-Departments to track that relationship.

Also, based on your image, a strong case can be made that all of your departments should be ONE table, with one primary key to identify the department.
0
 
za_pakAuthor Commented:
Hi,

one other thought, as all tables are linked to unique pupil number. I have tried to to enable "Allow creation of new records in this table via this relation". When we create new record on parent table the record should be created in all linked tables but its not happening. Is this due to external table or I am missing something?

Thanks
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>When we create new record on parent table the record should be created in all linked tables but its not happening.
Actually no.  If you create a new record in the parent 'one' table, this just allows you to enter records in the children 'many' tables.  This does not mean a new record is automatically added in the children 'many' tables.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
{clarification}

>When we create new record on parent table the record should be created in all linked tables but its not happening.
Actually no.  If you create a new record in the parent 'one' table, this just allows you to enter records in the children 'many' tables where the foreign key 'parent ID' is the newly-added parent ID in the parent table.  

This does not mean a new record is automatically added in the children 'many' tables.
0
 
za_pakAuthor Commented:
Hi

Thanks for you good ideas, would you like to explain this a bit further;

"A better idea would be to keep the current/not current flag solely in the student table, and then have (1:M) a foreign key in Students to indicate what department they belong to, or (M:M) another table of Students-to-Departments to track that relationship."

I couldn't understand how to do this?


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.