Hi all
Congratulations for this great group, and first of all, sorry for my
bad english...

I'm trying to do a database for a music school.
The problem is that I'm really not understanding how the table
relationships work.
I read the "Filemaker 10 bible" book and the Filemaker help, but that
really didn't help much, so I must be really stupid (hehehe).

The problem is this:
I have several tables (students, teachers, employees, etc) and I want
the following feature:
when I write a class name ("piano", "flute", etc.) in a student record
(table "students" and I choose a teacher name for that class, the
student's name, number and grade (in that class) must show up in a
list (portal?) at the corresponding teacher's record.
Each student may be enrolled in several different classes ("piano",
"choir", "music theory", etc) with different teachers (or not) and
grades.
This would be really perfect if the information at the teacher's
record was grouped by class, because each teacher can teach different
classes (for example "piano" and "music theory"). Like: "Piano":
Student No.1, Student No.2, Student No.3, etc.
Music Theory: Student No.1, Student No.4, Student No.5, etc

Many thanks in advance
Please explain it easily, because I'm soooo confused with this
subject...

Pianoman
(Portugal)

On 2010-02-25 18:03:51 -0800, Pianoman <pianoman.pt@gmail.com> said:

> Hi all
> Congratulations for this great group, and first of all, sorry for my
> bad english...

Your English is perfectly fine, and certainly better than my 
non-existant Portugese.
> 
> I'm trying to do a database for a music school.
> The problem is that I'm really not understanding how the table
> relationships work.
> I read the "Filemaker 10 bible" book and the Filemaker help, but that
> really didn't help much, so I must be really stupid (hehehe).
> 
> The problem is this:
> I have several tables (students, teachers, employees, etc) and I want
> the following feature:
> when I write a class name ("piano", "flute", etc.) in a student record
> (table "students" and I choose a teacher name for that class, the
> student's name, number and grade (in that class) must show up in a
> list (portal?) at the corresponding teacher's record.
> Each student may be enrolled in several different classes ("piano",
> "choir", "music theory", etc) with different teachers (or not) and
> grades.
> This would be really perfect if the information at the teacher's
> record was grouped by class, because each teacher can teach different
> classes (for example "piano" and "music theory"). Like: "Piano":
> Student No.1, Student No.2, Student No.3, etc.
> Music Theory: Student No.1, Student No.4, Student No.5, etc
> 
> Many thanks in advance
> Please explain it easily, because I'm soooo confused with this
> subject...

When you read about relationships in FM and the relationship graph, you 
will often come across the word "Context."  When I first started 
learning this in FM7, I had to simplify it for myself because I too was 
terribly confused.  I used a visual metaphor for myself since this is 
the way I think.

I began to think of context as "where I am standing to see the data I 
want to see."  Then I would list the data I wanted to see, and HOW I 
wanted to see it. That method, that HOW was the relationship.  And a 
relationship can only function through a key field or multiple key 
fields. Those key fields are the window from one table to another.

So if I were in a student record, and I wanted to see the classes a 
student was taking, I would know that somewhere out there was a table 
of classes. Each student record has a StudentID key field. Each class 
record has a ClassID key field. Looking at that, I realize that many 
students can be in each class, and there is no room in the basic Class 
record for many students.  So a direct link between these two tables 
won't work. I can't see directly from Students to Classes.

This can only mean one thing. There is what is called a Join Table 
between them. This is a table, perhaps called "Enrollment" where each 
record contains a StudentID field, and a ClassID field. So if a student 
is enrolled in 5 classes, they will have 5 enrollment records. Each 
will have the same StudentID value, but they will each have a different 
ClassID.

When I make the relationship from Student to Enrollment to Classes, I 
can see the data from both Enrollment AND Classes from where I am 
"standing" in the Student record.  The Enrollment join table feeds the 
data from Classes to Students, through this two-jump relationship.  So 
I can put a portal using this relationship on the Student layout, and 
see the 5 join table records, each with the name of class that is kept 
in the Class table.

Similarly, I know there must be a table for Teachers. Teachers can 
teach more than one class (unless they can't in your system) so there 
is probably a join table between Teachers and Classes, perhaps called 
Sections, where each record has a TeacherID, and a ClassID. Or you 
could have multiple teachers teaching the same class at different 
times, so that also requires a join table to exist. From a Teacher 
record you will be able to show the classes they teach. From a Class 
record you will be able to show the teachers who teach it, and the 
students enrolled, all in portals.

When I select a class for a student, I will want to bring across the 
correct TeacherID as well, so I know *which* class they're enrolling 
for. Perhaps there is a section number you can include in the value 
list to help you choose. The TeacherID would have to live in the 
Enrollments join table, and from that same record, you can link 
Students to Enrollment to Sections to Teachers, and pull that Teacher 
name from that 3-jump relationship. Remember that the Section table 
will also have a SectionID.

In this case, the key fields used on each side would be:

Student::Enrollment using StudentID
Enrollment::Sections using SectionID
Sections::Teachers using TeacherID

Get this arrangement of tables and relationships set up on your graph, 
as described. Make up some student, teacher, and class records and make 
some manual join records for Enrollment and Sections by copying and 
pasting your IDs into new records. Don't worry for now as to HOW you 
will select the classes or teachers for a student. Then show the data 
in portals on your Student, Teacher, and Class layouts.

Once you get this working and start to understand it, come back to us 
and we'll work on how to make selections for your system.

-- 
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On 26 Fev, 02:33, Lynn Allen <l...@NOT-semiotics.com> wrote:
> On 2010-02-25 18:03:51 -0800, Pianoman <pianoman...@gmail.com> said:
>
> > Hi all
> > Congratulations for this great group, and first of all, sorry for my
> > bad english...
>
> Your English is perfectly fine, and certainly better than my
> non-existant Portugese.
>
>
>
>
>
> > I'm trying to do a database for a music school.
> > The problem is that I'm really not understanding how the table
> > relationships work.
> > I read the "Filemaker 10 bible" book and the Filemaker help, but that
> > really didn't help much, so I must be really stupid (hehehe).
>
> > The problem is this:
> > I have several tables (students, teachers, employees, etc) and I want
> > the following feature:
> > when I write a class name ("piano", "flute", etc.) in a student record
> > (table "students" and I choose a teacher name for that class, the
> > student's name, number and grade (in that class) must show up in a
> > list (portal?) at the corresponding teacher's record.
> > Each student may be enrolled in several different classes ("piano",
> > "choir", "music theory", etc) with different teachers (or not) and
> > grades.
> > This would be really perfect if the information at the teacher's
> > record was grouped by class, because each teacher can teach different
> > classes (for example "piano" and "music theory"). Like: "Piano":
> > Student No.1, Student No.2, Student No.3, etc.
> > Music Theory: Student No.1, Student No.4, Student No.5, etc
>
> > Many thanks in advance
> > Please explain it easily, because I'm soooo confused with this
> > subject...
>
> When you read about relationships in FM and the relationship graph, you
> will often come across the word "Context." =A0When I first started
> learning this in FM7, I had to simplify it for myself because I too was
> terribly confused. =A0I used a visual metaphor for myself since this is
> the way I think.
>
> I began to think of context as "where I am standing to see the data I
> want to see." =A0Then I would list the data I wanted to see, and HOW I
> wanted to see it. That method, that HOW was the relationship. =A0And a
> relationship can only function through a key field or multiple key
> fields. Those key fields are the window from one table to another.
>
> So if I were in a student record, and I wanted to see the classes a
> student was taking, I would know that somewhere out there was a table
> of classes. Each student record has a StudentID key field. Each class
> record has a ClassID key field. Looking at that, I realize that many
> students can be in each class, and there is no room in the basic Class
> record for many students. =A0So a direct link between these two tables
> won't work. I can't see directly from Students to Classes.
>
> This can only mean one thing. There is what is called a Join Table
> between them. This is a table, perhaps called "Enrollment" where each
> record contains a StudentID field, and a ClassID field. So if a student
> is enrolled in 5 classes, they will have 5 enrollment records. Each
> will have the same StudentID value, but they will each have a different
> ClassID.
>
> When I make the relationship from Student to Enrollment to Classes, I
> can see the data from both Enrollment AND Classes from where I am
> "standing" in the Student record. =A0The Enrollment join table feeds the
> data from Classes to Students, through this two-jump relationship. =A0So
> I can put a portal using this relationship on the Student layout, and
> see the 5 join table records, each with the name of class that is kept
> in the Class table.
>
> Similarly, I know there must be a table for Teachers. Teachers can
> teach more than one class (unless they can't in your system) so there
> is probably a join table between Teachers and Classes, perhaps called
> Sections, where each record has a TeacherID, and a ClassID. Or you
> could have multiple teachers teaching the same class at different
> times, so that also requires a join table to exist. From a Teacher
> record you will be able to show the classes they teach. From a Class
> record you will be able to show the teachers who teach it, and the
> students enrolled, all in portals.
>
> When I select a class for a student, I will want to bring across the
> correct TeacherID as well, so I know *which* class they're enrolling
> for. Perhaps there is a section number you can include in the value
> list to help you choose. The TeacherID would have to live in the
> Enrollments join table, and from that same record, you can link
> Students to Enrollment to Sections to Teachers, and pull that Teacher
> name from that 3-jump relationship. Remember that the Section table
> will also have a SectionID.
>
> In this case, the key fields used on each side would be:
>
> Student::Enrollment using StudentID
> Enrollment::Sections using SectionID
> Sections::Teachers using TeacherID
>
> Get this arrangement of tables and relationships set up on your graph,
> as described. Make up some student, teacher, and class records and make
> some manual join records for Enrollment and Sections by copying and
> pasting your IDs into new records. Don't worry for now as to HOW you
> will select the classes or teachers for a student. Then show the data
> in portals on your Student, Teacher, and Class layouts.
>
> Once you get this working and start to understand it, come back to us
> and we'll work on how to make selections for your system.
>
> --
> Lynn Allen
> --www.semiotics.com
> Member FBA
> FM 10 Certified Developer

Thank you SO much for your reply.
I really can't believe that there is someone so nice who spends time
replying to a newbie like me.
Many thanks again. I'm going to read your answer carefully and do some
testing in the next few days.
I will report back with some (good) news soon (hopefully).

Pianoman
(Portugal)

you want to look into many-to-many relationships.
each student might follow many classes
each class might have many students
to accomplish this you need a many-to-many relationship (mtm)
to manage a mtm you need an extra table, called the join-table
in this case you could call it the student_class table
It would hold 2 fields
you already have pk_studentID and pk_classID which are auto-generated 
primary keys (pk) for each table
the join table would have the fiels sk_studentID and sk_classID (sk = 
secondary key)
these need to be the same type as the pk fields
inside the relationgraph join student::pk_studentID to 
student_class::sk_studentID & class::pk_ck_classID to 
student_class::sk_classID
When you want to assign a student to a class you create a new record in the 
join table (student_class)
then assign pk_studentID to sk_studenID and pk_classID to sk_classID
All this is best scripted to counter any errors
with the record created you now will be able to create a layout for the 
Student table
create a portal for student_class and put inside this a field from class 
(like name or something similar)
the relationship starting at student will look 'through' student_class and 
find all connected classes (also true the other way around)

Hope this will get you going

-- 
Keep well / Hou je goed

Ursus

"Pianoman" <pianoman.pt@gmail.com> schreef in bericht 
news:267f8f43-fa70-446d-8560-1fb37cba19be@t23g2000yqt.googlegroups.com...
> Hi all
> Congratulations for this great group, and first of all, sorry for my
> bad english...
>
> I'm trying to do a database for a music school.
> The problem is that I'm really not understanding how the table
> relationships work.
> I read the "Filemaker 10 bible" book and the Filemaker help, but that
> really didn't help much, so I must be really stupid (hehehe).
>
> The problem is this:
> I have several tables (students, teachers, employees, etc) and I want
> the following feature:
> when I write a class name ("piano", "flute", etc.) in a student record
> (table "students" and I choose a teacher name for that class, the
> student's name, number and grade (in that class) must show up in a
> list (portal?) at the corresponding teacher's record.
> Each student may be enrolled in several different classes ("piano",
> "choir", "music theory", etc) with different teachers (or not) and
> grades.
> This would be really perfect if the information at the teacher's
> record was grouped by class, because each teacher can teach different
> classes (for example "piano" and "music theory"). Like: "Piano":
> Student No.1, Student No.2, Student No.3, etc.
> Music Theory: Student No.1, Student No.4, Student No.5, etc
>
> Many thanks in advance
> Please explain it easily, because I'm soooo confused with this
> subject...
>
> Pianoman
> (Portugal) 

On 26 Fev, 22:35, "Ursus" <ursus.k...@ziggo.nl> wrote:
> you want to look into many-to-many relationships.
> each student might follow many classes
> each class might have many students
> to accomplish this you need a many-to-many relationship (mtm)
> to manage a mtm you need an extra table, called the join-table
> in this case you could call it the student_class table
> It would hold 2 fields
> you already have pk_studentID and pk_classID which are auto-generated
> primary keys (pk) for each table
> the join table would have the fiels sk_studentID and sk_classID (sk =
> secondary key)
> these need to be the same type as the pk fields
> inside the relationgraph join student::pk_studentID to
> student_class::sk_studentID & class::pk_ck_classID to
> student_class::sk_classID
> When you want to assign a student to a class you create a new record in the
> join table (student_class)
> then assign pk_studentID to sk_studenID and pk_classID to sk_classID
> All this is best scripted to counter any errors
> with the record created you now will be able to create a layout for the
> Student table
> create a portal for student_class and put inside this a field from class
> (like name or something similar)
> the relationship starting at student will look 'through' student_class and
> find all connected classes (also true the other way around)
>
> Hope this will get you going
>
> --
> Keep well / Hou je goed
>
> Ursus
>
> "Pianoman" <pianoman...@gmail.com> schreef in berichtnews:267f8f43-fa70-446d-8560-1fb37cba19be@t23g2000yqt.googlegroups.com...
>
> > Hi all
> > Congratulations for this great group, and first of all, sorry for my
> > bad english...
>
> > I'm trying to do a database for a music school.
> > The problem is that I'm really not understanding how the table
> > relationships work.
> > I read the "Filemaker 10 bible" book and the Filemaker help, but that
> > really didn't help much, so I must be really stupid (hehehe).
>
> > The problem is this:
> > I have several tables (students, teachers, employees, etc) and I want
> > the following feature:
> > when I write a class name ("piano", "flute", etc.) in a student record
> > (table "students" and I choose a teacher name for that class, the
> > student's name, number and grade (in that class) must show up in a
> > list (portal?) at the corresponding teacher's record.
> > Each student may be enrolled in several different classes ("piano",
> > "choir", "music theory", etc) with different teachers (or not) and
> > grades.
> > This would be really perfect if the information at the teacher's
> > record was grouped by class, because each teacher can teach different
> > classes (for example "piano" and "music theory"). Like: "Piano":
> > Student No.1, Student No.2, Student No.3, etc.
> > Music Theory: Student No.1, Student No.4, Student No.5, etc
>
> > Many thanks in advance
> > Please explain it easily, because I'm soooo confused with this
> > subject...
>
> > Pianoman
> > (Portugal)

Thanks Ursus. I've been doing some tests after reading both answers.
I think I'm going somewhere with classes and students.

Now I'm off to teachers. The process is similar, right?

Just one question: why do I need to have auto-generated pks and sks
for the ClassID? couldn't they be text (the class name like "piano",
"flute", etc.)

Thanks again
Pianoman
(Portugal)

In article 
<849fa01e-4d41-4770-b586-a5a19b717dde@v13g2000yqv.googlegroups.com>,
 Pianoman <pianoman.pt@gmail.com> wrote:

> On 26 Fev, 22:35, "Ursus" <ursus.k...@ziggo.nl> wrote:
> > you want to look into many-to-many relationships.
> > each student might follow many classes
> > each class might have many students
> > to accomplish this you need a many-to-many relationship (mtm)
> > to manage a mtm you need an extra table, called the join-table
> > in this case you could call it the student_class table
> > It would hold 2 fields
> > you already have pk_studentID and pk_classID which are auto-generated
> > primary keys (pk) for each table
> > the join table would have the fiels sk_studentID and sk_classID (sk =
> > secondary key)
> > these need to be the same type as the pk fields
> > inside the relationgraph join student::pk_studentID to
> > student_class::sk_studentID & class::pk_ck_classID to
> > student_class::sk_classID
> > When you want to assign a student to a class you create a new record in the
> > join table (student_class)
> > then assign pk_studentID to sk_studenID and pk_classID to sk_classID
> > All this is best scripted to counter any errors
> > with the record created you now will be able to create a layout for the
> > Student table
> > create a portal for student_class and put inside this a field from class
> > (like name or something similar)
> > the relationship starting at student will look 'through' student_class and
> > find all connected classes (also true the other way around)
> >
> > Hope this will get you going
> >
> > --
> > Keep well / Hou je goed
> >
> > Ursus
> >
> > "Pianoman" <pianoman...@gmail.com> schreef in 
> > berichtnews:267f8f43-fa70-446d-8560-1fb37cba19be@t23g2000yqt.googlegroups.co
> > m...
> >
> > > Hi all
> > > Congratulations for this great group, and first of all, sorry for my
> > > bad english...
> >
> > > I'm trying to do a database for a music school.
> > > The problem is that I'm really not understanding how the table
> > > relationships work.
> > > I read the "Filemaker 10 bible" book and the Filemaker help, but that
> > > really didn't help much, so I must be really stupid (hehehe).
> >
> > > The problem is this:
> > > I have several tables (students, teachers, employees, etc) and I want
> > > the following feature:
> > > when I write a class name ("piano", "flute", etc.) in a student record
> > > (table "students" and I choose a teacher name for that class, the
> > > student's name, number and grade (in that class) must show up in a
> > > list (portal?) at the corresponding teacher's record.
> > > Each student may be enrolled in several different classes ("piano",
> > > "choir", "music theory", etc) with different teachers (or not) and
> > > grades.
> > > This would be really perfect if the information at the teacher's
> > > record was grouped by class, because each teacher can teach different
> > > classes (for example "piano" and "music theory"). Like: "Piano":
> > > Student No.1, Student No.2, Student No.3, etc.
> > > Music Theory: Student No.1, Student No.4, Student No.5, etc
> >
> > > Many thanks in advance
> > > Please explain it easily, because I'm soooo confused with this
> > > subject...
> >
> > > Pianoman
> > > (Portugal)
> 
> Thanks Ursus. I've been doing some tests after reading both answers.
> I think I'm going somewhere with classes and students.
> 
> Now I'm off to teachers. The process is similar, right?
> 
> Just one question: why do I need to have auto-generated pks and sks
> for the ClassID? couldn't they be text (the class name like "piano",
> "flute", etc.)
> 
> Thanks again
> Pianoman
> (Portugal)

You can certainly have a field for the name of the Class, just as you 
have fields for the name of the student. However, relationships should 
be based on auto-generated numbers that cannot be edited by the user. If 
you base a relationship on a field that can be edited, then any change 
in the content of the field will break the relationship.

Also, I would add to the previous suggestions that the logical place to 
record a student's grade for the course would be in the Join table. That 
is also the place to record the registration date, and any other 
information pertinent to the enrollment itself. So the Join table 
("Enrollment" or "Registration" or whatever you call it) would need 
fields to record those kinds of information.

I think I have the Student part working OK already. Thanks again for
all your help.

Now I need to do the Teacher part, but I'm not still able to figure
out which relations I need.


This is the situation I want:
Each Teacher might teach several classes (Piano, Music Theory, Choir,
etc)
Each Student might (and probably) have many different teachers (piano
teacher, Choir practice teacher, etc)
Each teacher might (surely) have many students

Some Classes are individual (any of the instruments)
Some Classes are collective (Music Theory, Choir Practice)

I want to list the students that each teacher has, grouped by class
subject and sorted by weekday and time with some extra informations
like grade, number, classroom, etc.
for example:

Teacher: Pianoman

Class: Piano
"Student no.1" grade 2, classroom 3, Sunday, 10:00
"Student no.2" grade 4, classroom 3, Sunday, 11:00
"Student no.3" grade 5, classroom 3, Sunday, 12:00

Class: Choir practice
"Student no.4" grade 1, classroom 1, Monday, 15.00
"Student no.2" grade 1, classroom 1, Monday, 15.00
"Student no.5" grade 1, classroom 1, Monday, 15.00
"Student no.3" grade 1, classroom 1, Monday, 15.00

Any help?


Many thanks in advance
Pianoman
(Portugal)

>
> Teacher: Pianoman
>
> Class: Piano
> "Student no.1" grade 2, classroom 3, Sunday, 10:00
> "Student no.2" grade 4, classroom 3, Sunday, 11:00
> "Student no.3" grade 5, classroom 3, Sunday, 12:00
>
> Class: Choir practice
> "Student no.4" grade 1, classroom 1, Monday, 15.00
> "Student no.2" grade 1, classroom 1, Monday, 15.00
> "Student no.5" grade 1, classroom 1, Monday, 15.00
> "Student no.3" grade 1, classroom 1, Monday, 15.00
>
> Any help?
>
>

you work your way down from left to right. Create a layout based on the most 
left item, being the teacher. Then create joine-tables or relatations as 
needed.

Teacher::teacher_class::class::class_student::student::student_classroom::classroom::classroom_day::day

But before you go and create massive amounts of relationships a bit of 
warning is in place. If you just go ahead you will end up with a 
relationship graph looking much like a spider (therefore called a spider 
graph, sometimes also chaotic-graph).  These a very difficult to work with 
as they get larger and larger. But there a a couple of other ways to go 
about that you need to learn first. And I would like to urge you to take the 
trouble before your product is finished. Once you have made a choice it will 
be very difficult to change your ways.  So please read the approach on graph 
modelling. found at: 
http://developer.filemaker.com/content/technet/pdf/approaches_to_graph_modeling_en.pdf
Try them out and make a better choice (probably a mix between squid and 
chaotic in your case)

It will take some time and trouble but imho it is really worth the trouble.

-- 
Keep well / Hou je goed

Ursus 

P.S. also I could recomend the following

http://www.filemaker.com/downloads/pdf/FMDev_ConvNov05.pdf

-- 
Keep well / Hou je goed

Ursus

"Pianoman" <pianoman.pt@gmail.com> schreef in bericht 
news:0abde89b-650b-469a-beb4-658716ffc2d3@g28g2000yqh.googlegroups.com...
>I think I have the Student part working OK already. Thanks again for
> all your help.
>
> Now I need to do the Teacher part, but I'm not still able to figure
> out which relations I need.
>
>
> This is the situation I want:
> Each Teacher might teach several classes (Piano, Music Theory, Choir,
> etc)
> Each Student might (and probably) have many different teachers (piano
> teacher, Choir practice teacher, etc)
> Each teacher might (surely) have many students
>
> Some Classes are individual (any of the instruments)
> Some Classes are collective (Music Theory, Choir Practice)
>
> I want to list the students that each teacher has, grouped by class
> subject and sorted by weekday and time with some extra informations
> like grade, number, classroom, etc.
> for example:
>
> Teacher: Pianoman
>
> Class: Piano
> "Student no.1" grade 2, classroom 3, Sunday, 10:00
> "Student no.2" grade 4, classroom 3, Sunday, 11:00
> "Student no.3" grade 5, classroom 3, Sunday, 12:00
>
> Class: Choir practice
> "Student no.4" grade 1, classroom 1, Monday, 15.00
> "Student no.2" grade 1, classroom 1, Monday, 15.00
> "Student no.5" grade 1, classroom 1, Monday, 15.00
> "Student no.3" grade 1, classroom 1, Monday, 15.00
>
> Any help?
>
>
> Many thanks in advance
> Pianoman
> (Portugal) 

In article <76f01$4b8a4e92$535399fa$17600@cache3.tilbu1.nb.home.nl>,
 "Ursus" <ursus.kirk@ziggo.nl> wrote:

> >
> > Teacher: Pianoman
> >
> > Class: Piano
> > "Student no.1" grade 2, classroom 3, Sunday, 10:00
> > "Student no.2" grade 4, classroom 3, Sunday, 11:00
> > "Student no.3" grade 5, classroom 3, Sunday, 12:00
> >
> > Class: Choir practice
> > "Student no.4" grade 1, classroom 1, Monday, 15.00
> > "Student no.2" grade 1, classroom 1, Monday, 15.00
> > "Student no.5" grade 1, classroom 1, Monday, 15.00
> > "Student no.3" grade 1, classroom 1, Monday, 15.00
> >
> > Any help?
> >
> >
> 
> you work your way down from left to right. Create a layout based on the most 
> left item, being the teacher. Then create joine-tables or relatations as 
> needed.
> 
> Teacher::teacher_class::class::class_student::student::student_classroom::clas
> sroom::classroom_day::day
> 
> But before you go and create massive amounts of relationships a bit of 
> warning is in place. If you just go ahead you will end up with a 
> relationship graph looking much like a spider (therefore called a spider 
> graph, sometimes also chaotic-graph).  These a very difficult to work with 
> as they get larger and larger. But there a a couple of other ways to go 
> about that you need to learn first. And I would like to urge you to take the 
> trouble before your product is finished. Once you have made a choice it will 
> be very difficult to change your ways.  So please read the approach on graph 
> modelling. found at: 
> http://developer.filemaker.com/content/technet/pdf/approaches_to_graph_modelin
> g_en.pdf
> Try them out and make a better choice (probably a mix between squid and 
> chaotic in your case)
> 
> It will take some time and trouble but imho it is really worth the trouble.

I agree very strongly with Ursus. 

I started out doing relationships in FIlemaker 7 and later, just adding 
on tables and table occurrences as needed to get the results I needed, 
without regard to the orderliness of the relationship graph. Filemaker 
allows you to do that, and the solution works. However, as my solutions 
got more complex, the relationship graph got more confusing, and it got 
more difficult to know what I was doing when creating portals, value 
lists, calculations and scripts.

I finally understood the value of two basic things, that seem mundane, 
but are very important for a solution of any complexity:
-Use of the "anchor-buoy" or "squid" model for relationship diagramming;
-Use of a standardized naming convention for Table Occurrences.

I now build all new database solutions using these methods, and I have 
even gone through the very laborious process of converting some of my 
earlier solutions to follow these conventions.

These methods are described in some papers on the FileMaker web site, 
that Ursus already referred to. There are other useful papers as well.

To summarize the basic concepts:

Each table of the database should have a base Table Occurrence (TO), 
named the same as the table. These base table occurrences should be 
lined up one below the other on the left-hand side of the relationship 
diagram. I usually put them in alphabetical order by name. These base 
TOs should NOT be connected to each other by relationships.

Each of these base TOs then becomes the "anchor" for an "anchor-buoy" 
array of TOs, arranged to the right in a fanning-out pattern. This kind 
of array is also called a "squid." The arrangement and content of each 
array is based on the information needed to support layouts and 
operations connected to the "anchor" TO.

All the layouts in your solution should be based on one of these base or 
anchor TOs. No layouts should be based on any of the related "buoy" or 
"tentacle" TOs.

For each need you have in the anchor TO for related data, you create a 
new TO of another table, and connect it to the anchor TO in the 
appropriate way to get the related info you need. The connection can be 
through an intermediate TO, depending on the logic of your solution.

All calculations and script steps, which are all tied to layouts, will 
then be based on one of these anchor TOs, taking related information as 
needed from one of the other TOs in the array of TOs that are related to 
the anchor TO.

The TOs in each array are generally related to the Anchor TO in a simple 
way, as their sole purpose is to feed information to portals, 
calculations, value lists and script steps that are based on one or 
another layout of the anchor TO.

Within each anchor-buoy or squid array, the TOs should be named by a 
method that identifies the anchor TO and the TOs in the chain between 
the anchor and the TO you are naming.

For example, in you solution for Teacher, Student, Class:

You need 5 tables:

Teacher
Student
Class
TeacherClass
StudentClass

TeacherClass and StudentClass are Join tables that assign teachers to 
classes in a many-to-many relationship, and students to classes in a 
many-to-many relationship.

Each of these 5 tables would have an anchor TO in the relationship 
diagram, and each would have at least one layout. These 5 anchor TOs 
should be arranged down the left-hand side of the relationship diagram.

Then create other TOs and connect them to the anchor TO as needed to 
produce the information you want for portals, calculation, value lists 
and script steps that are based on the anchor TO.

Each TO in the array except for the anchor TO is a new, uniquely-named 
TO of one of the five tables. It must NOT simply be the anchor TO for 
that table moved to the array, but a new TO; the anchor TOs must be left 
alone on the left side of the diagram, to form the basis for their own 
arrays or relationship groups, NOT connected to any to the other anchor 
TOs.

The papers on the FileMaker web site suggest some naming conventions for 
the various TOs in an anchor-buoy array. I use one of the suggested 
naming conventions. Whatever naming convention you use should indicate 
the anchor TO of the array, and the TOs between the anchor TO and the TO 
you are naming, and of course should indicate the name of the Table on 
which the TO is based. That way you have an easy time of figuring out 
what you are doing with information from related tables when you create 
a portal, calculation, script step or value list. Whatever naming 
convention you use, stick with it, so you don't have to figure out what 
you did when you look back at one of your solutions.

Thank you all for your advice.

I have almost everything working the way I want except one thing:
I can't show a list of each teacher students with the information I
want.

In a teacher record I have a portal that shows information from
another table (Classes) with the field teacherID
In the Class table I have records with several fields like studentID,
teacherID, class name, grade, class name, etc.

when the teacherID is the same in both Classes table and Teachers
table, the portal I have in the teacher's record (pointing to the
Students table) shows me studentID, grade, but no class name ! why?

I tried to "point" the portal to the Classes table instead of the
Students table, but what I get is a lot of data and not only the ones
that have the same teacher.

I'm missing something, for sure. Well...I know I'm missing a lot...

Thanks
Pianoman
(Portugal)

"Pianoman" <pianoman.pt@gmail.com> schreef in bericht 
news:d48c4323-0cd8-424e-86ae-3f680f4361b5@e7g2000yqf.googlegroups.com...
> Thank you all for your advice.
>
> I have almost everything working the way I want except one thing:
> I can't show a list of each teacher students with the information I
> want.
>
> In a teacher record I have a portal that shows information from
> another table (Classes) with the field teacherID
> In the Class table I have records with several fields like studentID,
> teacherID, class name, grade, class name, etc.
>
> when the teacherID is the same in both Classes table and Teachers
> table, the portal I have in the teacher's record (pointing to the
> Students table) shows me studentID, grade, but no class name ! why?
>
> I tried to "point" the portal to the Classes table instead of the
> Students table, but what I get is a lot of data and not only the ones
> that have the same teacher.
>
> I'm missing something, for sure. Well...I know I'm missing a lot...
>
> Thanks
> Pianoman
> (Portugal)

Do you have join tables?
Are all your links correct?
Are all your linking fields of corresponding types? (text to text, number to 
number)

be aware that you have to get your naming correctly. You are talking of 
tables, where in fact you are talking about Table Occurrance (TO). It might 
seem trivial to, you but there's a world of difference between them. One 
table might have many TO's. What you see in the graph with all those lines 
etc are TO's So when you are explaining about a portal from a TO, this TO 
should have a name. If it is not clear to what table this TO belongs, or 
what function it has, you will have to explain. (Or give the TO a better 
name)

From TeacherTO you should be able to create a portal from ClassTO and 
showing in this could be fields from StudentTO.
can you see the class name from the layout from teachers?

Solution:
Break it down by working backwards.
First what data is the most links away? Go there and check the data. Show 
all your fields on the layout and make sure all data are correctly entered. 
Actually look at them en remember them (or make a screen dump)
Go one TO down the flow, create a lyout from it, show all fields, create a 
portal from where you came from, is it correct?
Work your way down all layouts with all TO's
Create as many temporary layouts as you need. Which is also a good idea in 
actually understanding what you are doing and why something is working (or 
not).

Be aware you are trying to create something pretty involved here without any 
previous knowledge. Filemaker might seem easy, but with what you are trying 
to do it is a mighty beast. You might try something easier first (a school 
with class) and when that works as you need expand it step by step. You are 
now trying to grasp multiple techniques at one time, which might actualy 
work for you, but breaking them down in smaller steps might help.

-- 
Keep well / Hou je goed

Ursus 

On 1 Mar, 11:12, "Ursus" <ursus.k...@ziggo.nl> wrote:
> "Pianoman" <pianoman...@gmail.com> schreef in berichtnews:d48c4323-0cd8-424e-86ae-3f680f4361b5@e7g2000yqf.googlegroups.com...
>
>
>
> > Thank you all for your advice.
>
> > I have almost everything working the way I want except one thing:
> > I can't show a list of each teacher students with the information I
> > want.
>
> > In a teacher record I have a portal that shows information from
> > another table (Classes) with the field teacherID
> > In the Class table I have records with several fields like studentID,
> > teacherID, class name, grade, class name, etc.
>
> > when the teacherID is the same in both Classes table and Teachers
> > table, the portal I have in the teacher's record (pointing to the
> > Students table) shows me studentID, grade, but no class name ! why?
>
> > I tried to "point" the portal to the Classes table instead of the
> > Students table, but what I get is a lot of data and not only the ones
> > that have the same teacher.
>
> > I'm missing something, for sure. Well...I know I'm missing a lot...
>
> > Thanks
> > Pianoman
> > (Portugal)
>
> Do you have join tables?
> Are all your links correct?
> Are all your linking fields of corresponding types? (text to text, number to
> number)
>
> be aware that you have to get your naming correctly. You are talking of
> tables, where in fact you are talking about Table Occurrance (TO). It might
> seem trivial to, you but there's a world of difference between them. One
> table might have many TO's. What you see in the graph with all those lines
> etc are TO's So when you are explaining about a portal from a TO, this TO
> should have a name. If it is not clear to what table this TO belongs, or
> what function it has, you will have to explain. (Or give the TO a better
> name)
>
> From TeacherTO you should be able to create a portal from ClassTO and
> showing in this could be fields from StudentTO.
> can you see the class name from the layout from teachers?
>
> Solution:
> Break it down by working backwards.
> First what data is the most links away? Go there and check the data. Show
> all your fields on the layout and make sure all data are correctly entered.
> Actually look at them en remember them (or make a screen dump)
> Go one TO down the flow, create a lyout from it, show all fields, create a
> portal from where you came from, is it correct?
> Work your way down all layouts with all TO's
> Create as many temporary layouts as you need. Which is also a good idea in
> actually understanding what you are doing and why something is working (or
> not).
>
> Be aware you are trying to create something pretty involved here without any
> previous knowledge. Filemaker might seem easy, but with what you are trying
> to do it is a mighty beast. You might try something easier first (a school
> with class) and when that works as you need expand it step by step. You are
> now trying to grasp multiple techniques at one time, which might actualy
> work for you, but breaking them down in smaller steps might help.
>
> --
> Keep well / Hou je goed
>
> Ursus

Thanks a lot Ursus. I just got it right using your technique for
process verification.
Now I'm going to "attack" the report generation part.
Any basic advice I need to know?

I have one big doubt at this point: I think in the future I'll need to
separate data from this scholl year (2009/10) from other years (to
come). This data include mostly grades (evaluation) that students have
every 3 months or so.
My ideia was simply to use a diferent file for each year, but with
that (stupid) solution I will probably loose all the student history,
right?

Do you guys have any ideas about this?

Pianoman
(Portugal)

On 2010-03-01 15:22:20 -0800, Pianoman <pianoman.pt@gmail.com> said:

> I have one big doubt at this point: I think in the future I'll need to
> separate data from this scholl year (2009/10) from other years (to
> come). This data include mostly grades (evaluation) that students have
> every 3 months or so.
> My ideia was simply to use a diferent file for each year, but with
> that (stupid) solution I will probably loose all the student history,
> right?
> 
> Do you guys have any ideas about this?

The answer to "how do I track information from separate 
years/programs/offices/etc" is NEVER, NEVER "I'll just make separate 
files for each."

That is data structure madness.

Just put a Year field in each join table, and one in the table for 
classes.  Then you can track student enrollments and the classes 
teachers teach each year. You can enter the year in a global field in 
your Preferences or Settings table (read about 1-record tables for this 
purpose) and have the data auto-entered on creation of records in each 
of those tables.  Then change that single field when 2010 becomes 2011. 
Or Fall 2010 becomes Spring 2010. Whatever your divisions of time or 
semesters or terms are.
-- 
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

> Thanks a lot Ursus. I just got it right using your technique for
> process verification.
> Now I'm going to "attack" the report generation part.
> Any basic advice I need to know?
>
> I have one big doubt at this point: I think in the future I'll need to
> separate data from this scholl year (2009/10) from other years (to
> come). This data include mostly grades (evaluation) that students have
> every 3 months or so.
> My ideia was simply to use a diferent file for each year, but with
> that (stupid) solution I will probably loose all the student history,
> right?
>
> Do you guys have any ideas about this?
>
> Pianoman
> (Portugal)

Try to work out the answer before I give it. Why have you put things 
together in one file? Because they belong in one file. Why should another 
year be any different?  In the event you could always choose to anonimise 
your data when doing statistics. BTW filemaker is not a very good number 
cruncher. You can do some basic statistics on your data, but not complex. 
Graphs etc can be done, but are a pain in the sit-upon. You will then have 
to export (part) of your data to crunch them in an other application.

Answer: create a table for the years.

-- 
Keep well / Hou je goed

Ursus 

After a short break, I'm back to my task.
The question for today is: how can I email several students at the
same time?

I have a portal that lists the students of a particular teacher. It
shows some basic information like: number, name, class name and also
has a "email" button.
This is fine, but sometimes I need to email ALL the students of a
teacher and also ALL the students of a particular teacher that attend
a particular class.

Is that possible? well...I know it's possible, but is it relatively
simple to do?

Thanks
Pianoman
(Portugal)

Pianoman,

You can better start a new thread in the group when asking a new question.
FMP is not a mass-mailer you might need a plug-in, but this is not my 
strong-point.
-- 
Keep well / Hou je goed

Ursus


"Pianoman" <pianoman.pt@gmail.com> schreef in bericht 
news:9944aa46-4bc4-40fd-b549-fed7e048693d@g28g2000yqh.googlegroups.com...
> After a short break, I'm back to my task.
> The question for today is: how can I email several students at the
> same time?
>
> I have a portal that lists the students of a particular teacher. It
> shows some basic information like: number, name, class name and also
> has a "email" button.
> This is fine, but sometimes I need to email ALL the students of a
> teacher and also ALL the students of a particular teacher that attend
> a particular class.
>
> Is that possible? well...I know it's possible, but is it relatively
> simple to do?
>
> Thanks
> Pianoman
> (Portugal)