Solved

Auto update one field on different tables

Posted on 2008-06-25
6
648 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 66

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
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
LVL 66

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 66

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…

717 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