Solved

Auto update one field on different tables

Posted on 2008-06-25
6
641 Views
Last Modified: 2011-10-19
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
Comment
Question by:za_pak
  • 3
  • 3
6 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 21866138
>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
 

Accepted Solution

by:
za_pak earned 0 total points
ID: 21917025

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
 

Author Comment

by:za_pak
ID: 22048766
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 22050204
>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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 22050213
{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
 

Author Comment

by:za_pak
ID: 22066860
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now