Trying to make things easier, but...

[dchuhta]

I have two tables.. one's a staff directory, the other keeps track of absences. The staff happen to be teachers, and some teach at multiple schools in the district, so they may have multiple records in the directory.. one for each school (since school is a field). The two tables are related by ID numbers. On the absence table, I'd like to be able to enter an ID number, and have the rest of the info (name, position, school) automatically pull in from the directory table. The trouble is, when an ID is entered, it will find the first record, which may not have the correct school. Is there a way to select the school on the absence table, or should I create a unique ID for each staff member at each school they teach?

[Søren Dyhr]

should I create a unique ID for each staff member at each school they teach

Yes, but if they teach more than one place should they still have thier own unique ID, the relation is btw a classic many 2 many.

--sd

[Slobey]

You could also create a multiple relationship between ID and school. that way you only have to remember one ID for the teacher and when you enter it you enter the school in a different field to complete the relationship

Mike

[dchuhta]

Ok.. so I've gotten it to work based on ID then school. However, what if I wanted it to work based on Last then First then School.... For some reason when I set up those field relationships it doesn't work. Is there a differen between using names rather than an ID?

Thanks so much!

Edited July 10, 2007 by Guest

[Søren Dyhr]

What is your primary source when reading up on many 2 many structures? The source needs to be aware of filemaker concepts not to confuse unnecessary ...your reply seems to avoid taking the plunge into it??

--sd

[JesseSFR]

I think we definitely have a problem with table structure hear. You have teachers and schools currently. Your realtionship between the two can be described as the following:

Each teacher has many schools.

Each school has many teachers.

As Søren Dyhr posted, you have a classic many to many relationship and when handling that in Filemaker it is pretty tough. The best way to take care of this problem to add another table sometimes called an enrollment table or a join table. This table would hold the Id of a teacher and and id of a school so it would looke like this

Table Name:

Teachership(supid I know but it's all I could thinkg of)

Fields:

id

teacher_id

school_id

You could also include any other information that you would need to know about that teacher at that school. So for instance if the teacher is a full-time employee at one school and a temp at another you would probably want to add fields to this table to store that data.

You now have broken up your relationships to read some thing like this:

A teacher has a teachership

A techership has a school

This is much easier to deal with in filemaker.

Once you have this concept down you can change the way you select your absences so you pick a teacher and then look through the the teachership relationships to all the schools and select which school you would like to add an absent record for.

Or select it the other way... pick a school and then select from a list of teachers at the school who was absent.

This is typical problem in FileMaker and it can definitely be very confusing. I hope this helps you