Solved

Auto update one field on different tables

Posted on 2008-06-25
6
645 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 97
T-SQL Default value in Select? 5 37
MS SQL Server time between records 14 46
query question 12 32
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

840 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