• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

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
0
za_pak
Asked:
za_pak
  • 3
  • 3
4 Solutions
 
Jim HornMicrosoft 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:

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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim HornMicrosoft 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 HornMicrosoft 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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now