Is anyone aware of a (free?) gradebook template for fmp?  I've been
struggling off and on for a while trying to make one work, but can't get my
brain to wrap around many to many relationships.

I envision a file for students, a file for courses taught, and a file for
student performance within a specific course.  Thus, many students enroll in
one OR MORE classes, and each class can have many students.  That's fine.
But when it comes to the actual gradebook, I get stuck.  How can I assign
students to a course and have the whole roster show up along with
assignments, exam scores, and totals?

It seems so simple, yet when I try to implement, I have problems.  For
instance, how can I have the info from Students (name, ID) and Courses
(assignments, exams, total points) show up in the same field?  Also, A
student might enroll in Course 1 in the Fall, and then show up in Course 2
in the Spring.  In the Student record, there is only 1 field for
"courseEnrolled".  Should I have more than 1 record for each student?  I've
been reading Using FMP5 by Coulombre and Price, and it seems like it should
be possible, but I'm missing something.

Any pointers appreciated,

Fred Rose

Portals are your friend here-- if you are not familiar with portals, read up
on them and use them to your advantage.

Each entity needs a unique identifier-- usually an auto-entered serial
number that absolutely must be unique (use the auto-enter serial number
feature). You've got your entities right-- student ('Students.fp5'), class
('Classes.fp5'), class assignment ('Assignements.fp5'), which is an
intersection of the other two, and thus requires both the Student_ID and the
Class_ID. It can also have a grade field. You can create records in
Assignements in a variety of ways, eaither with a script and/or through a
portal. You will most likely want a portal to Assignments from Classes,
using a relationship based on the Class_ID, and another in Students, using a
relationship based on the Student_ID. The portal in Classes, then, will show
all the students enrolled in the class; the portal in Students will show all
the classed in which the student is enrolled.

From there you can use the Aggregate functions to gather 'totals', such as
the total number of students enrolled in each course and the number of
courses each student is taking. If you use number fields for grades (a scale
of 60 to 100 for non F grades, or 1 to 4, or whatever scale you like), you
can then get averages, and even perform some simple statistical analysis,
using the relationships.

Make certain to create a new class, with a new Class_ID, for each iteration
of a class by semester; in other words, Advanced English 101 Fall 2003 might
be Class_ID 2456, and Advanced English 101 for Spring 2004 will be Class_ID
3216. Do not try to make the ID's do any other work-- they should be
meaningless, unique, non-modifiable, and non-desctuctible (keep them off
layouts, and define them to 'Prohibit Modification').

-- 
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Associate Member, Filemaker Solutions Alliance


"Fred" <fredr1@cox.net> wrote in message
news:BB87CEE6.B13D%fredr1@cox.net...
> Is anyone aware of a (free?) gradebook template for fmp?  I've been
> struggling off and on for a while trying to make one work, but can't get
my
> brain to wrap around many to many relationships.
>
> I envision a file for students, a file for courses taught, and a file for
> student performance within a specific course.  Thus, many students enroll
in
> one OR MORE classes, and each class can have many students.  That's fine.
> But when it comes to the actual gradebook, I get stuck.  How can I assign
> students to a course and have the whole roster show up along with
> assignments, exam scores, and totals?
>
> It seems so simple, yet when I try to implement, I have problems.  For
> instance, how can I have the info from Students (name, ID) and Courses
> (assignments, exams, total points) show up in the same field?  Also, A
> student might enroll in Course 1 in the Fall, and then show up in Course 2
> in the Spring.  In the Student record, there is only 1 field for
> "courseEnrolled".  Should I have more than 1 record for each student?
I've
> been reading Using FMP5 by Coulombre and Price, and it seems like it
should
> be possible, but I'm missing something.
>
> Any pointers appreciated,
>
> Fred Rose
>

Your description was very helpful and is close to what I need.  However, I
have one additional bit of complexity (at least in my mind!):

I have different classes (e.g., Abnormal Psychology, Introductory
Psychology, Developmental Psychology), each with its own assignments.  Intro
might have 4 exams only, Abnormal 3 exams, and Developmental 4 exams and 1
term paper.  How can I create a Course table that, through a portal, will
bring in not only all the students enrolled, but also just those assignments
unique to that course?  Do I need to create 10 assignment fields in the
Assignment.fp5 table and then have a lot of blank spaces on the course
portal pointing to Assignments? That would look ugly.  I thought about
creating a separate layout for each course, though over time I may change
the number of assignments.  Thus, in the Fall I may have 3 exams in
Abnormal, but in the Spring I may add a term paper.  I don't want to have to
modify the layout for each course on a continual basis.  Is there a way
around this?

It seems that each course should have its own set of assignments, but if I
set the db structure up in that manner, I can't figure out how to get the
students and assignments all in one portal row (since course and assignments
would have to have their own relationship and students and courses would
have to have a different relationship) where I can track performance in one
neat line.  Any insight is much appreciated!

Trying hard,

Fred Rose

On 9/12/03 9:29 PM, in article vm579ma0n1hj2b@news.supernews.com, "John
Weinshel" <john@datagrace.biz> wrote:

> Portals are your friend here-- if you are not familiar with portals, read up
> on them and use them to your advantage.
> 
> Each entity needs a unique identifier-- usually an auto-entered serial
> number that absolutely must be unique (use the auto-enter serial number
> feature). You've got your entities right-- student ('Students.fp5'), class
> ('Classes.fp5'), class assignment ('Assignements.fp5'), which is an
> intersection of the other two, and thus requires both the Student_ID and the
> Class_ID. It can also have a grade field. You can create records in
> Assignements in a variety of ways, eaither with a script and/or through a
> portal. You will most likely want a portal to Assignments from Classes,
> using a relationship based on the Class_ID, and another in Students, using a
> relationship based on the Student_ID. The portal in Classes, then, will show
> all the students enrolled in the class; the portal in Students will show all
> the classed in which the student is enrolled.
> 
> From there you can use the Aggregate functions to gather 'totals', such as
> the total number of students enrolled in each course and the number of
> courses each student is taking. If you use number fields for grades (a scale
> of 60 to 100 for non F grades, or 1 to 4, or whatever scale you like), you
> can then get averages, and even perform some simple statistical analysis,
> using the relationships.
> 
> Make certain to create a new class, with a new Class_ID, for each iteration
> of a class by semester; in other words, Advanced English 101 Fall 2003 might
> be Class_ID 2456, and Advanced English 101 for Spring 2004 will be Class_ID
> 3216. Do not try to make the ID's do any other work-- they should be
> meaningless, unique, non-modifiable, and non-desctuctible (keep them off
> layouts, and define them to 'Prohibit Modification').

What I called 'Assignments.fp5' ought to be called something else, like
'Enrollment.fp5'; 'Assignments.fp5' should be a new file dealing with
assignments; you may want yet another file for exams, but you could also
handle exams within Assignments with the addition of an extra field to
distinguish the types (assignment or exam). Other than that, the file would
work much the same way Enrollment works-- fields for both Class_ID and
Student_ID. Now you build filtered relationships, which usually means
creating concatenated keys that combine the two ID's into one calculated
text field (I prefer to add a space between them, both for clarity and for
ease in deconstruction on the other end). These calcs can live in all the
files, depending on what you want to see.

In Enrollment, for instance, where there is a record for Maybelle Hays,
Student_ID 47, taking Developmental Psychology F '03, Class_ID 9245. The
concatenated key:

Student_ID & " " & Class_ID

....would return '47 9245'

If you keep both exams and assignments in Assignements, you might use a
similar calc but add the 'Type' text field. An assignement for Maybelle in
the same class, then, would use a calc ('ID_ID_Type'):

Student_ID & " " & Class_ID & " " & Type

....returning '47 9245 Assignment'. An exam for her would return '47 9245
Exam'.

In, say, Enrollment, you can hard-code two calcs, returning text, to use as
left hand keys:

Student_ID & " " & Class_ID & " Assignment"
Student_ID & " " & Class_ID & " Exam"

....(notice the space before the strings at the end). A relationship built
from the first calc to ID_ID_Type in Assignments would 'find' all
assignments for that student in that course; a relationship built from the
second calc to the same foreign key would show all the Exams for that
student in that course. Similar calcs that remove the Student_ID will show
all the assignments or exams for a given course; keeping the Student_ID but
removing the Class_ID will show all the assignments or exams for a given
student. Removing the Type would show all the activity.

The main 'weight' added with all these additional fields and relationships
is some plumping up of the file size as the index is increased. Left side
keys need not be indexed, but, in a setup like this, many of the left hand
keys could also serve as right hand keys when the file is viewed from
another viewpoint. Relationships are almost free, in terms of overhead, as
are unstored fields; unstored calcs, however, displayed on a layout, can
begin to take a performance toll as their values are re-calculated as
needed.

If you add a Grade field to Assignments, and it is a number field, you can
use the Aggregate functions to gather summary information, such as average
assignment grade for each student, for all students, for all students for
one class, for all students for all classes for a given year (if, as seems
likely, you include a date field) or semester (if you use a numbering system
for semesters; if you use both a numbering system and a text or number for
the quarter/semester, you can build comparisons between the same
quarter/semester in different years, classes, student, etc.).

The left hand keys can also be built from global fields populated with
pulldown lists of the appropiate values (say 'Assignment' and 'Exam' for a
global Type field). That can give you the ability to quickly see a different
set of child records in a portal by merely selecting a different value (or
values from calcs using more than one global) from the pulldown list.

Remember Filemaker's 20/60 rule for relational keys: only 60 characters, in
strings no longer than 20 characters, are evaluated for text keys.

-- 
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Associate Member, Filemaker Solutions Alliance


"Fred" <fredr1@cox.net> wrote in message
news:BB8953BC.B1D4%fredr1@cox.net...
> Your description was very helpful and is close to what I need.  However, I
> have one additional bit of complexity (at least in my mind!):
>
> I have different classes (e.g., Abnormal Psychology, Introductory
> Psychology, Developmental Psychology), each with its own assignments.
Intro
> might have 4 exams only, Abnormal 3 exams, and Developmental 4 exams and 1
> term paper.  How can I create a Course table that, through a portal, will
> bring in not only all the students enrolled, but also just those
assignments
> unique to that course?  Do I need to create 10 assignment fields in the
> Assignment.fp5 table and then have a lot of blank spaces on the course
> portal pointing to Assignments? That would look ugly.  I thought about
> creating a separate layout for each course, though over time I may change
> the number of assignments.  Thus, in the Fall I may have 3 exams in
> Abnormal, but in the Spring I may add a term paper.  I don't want to have
to
> modify the layout for each course on a continual basis.  Is there a way
> around this?
>
> It seems that each course should have its own set of assignments, but if I
> set the db structure up in that manner, I can't figure out how to get the
> students and assignments all in one portal row (since course and
assignments
> would have to have their own relationship and students and courses would
> have to have a different relationship) where I can track performance in
one
> neat line.  Any insight is much appreciated!
>
> Trying hard,
>
> Fred Rose
>
> On 9/12/03 9:29 PM, in article vm579ma0n1hj2b@news.supernews.com, "John
> Weinshel" <john@datagrace.biz> wrote:
>
> > Portals are your friend here-- if you are not familiar with portals,
read up
> > on them and use them to your advantage.
> >
> > Each entity needs a unique identifier-- usually an auto-entered serial
> > number that absolutely must be unique (use the auto-enter serial number
> > feature). You've got your entities right-- student ('Students.fp5'),
class
> > ('Classes.fp5'), class assignment ('Assignements.fp5'), which is an
> > intersection of the other two, and thus requires both the Student_ID and
the
> > Class_ID. It can also have a grade field. You can create records in
> > Assignements in a variety of ways, eaither with a script and/or through
a
> > portal. You will most likely want a portal to Assignments from Classes,
> > using a relationship based on the Class_ID, and another in Students,
using a
> > relationship based on the Student_ID. The portal in Classes, then, will
show
> > all the students enrolled in the class; the portal in Students will show
all
> > the classed in which the student is enrolled.
> >
> > From there you can use the Aggregate functions to gather 'totals', such
as
> > the total number of students enrolled in each course and the number of
> > courses each student is taking. If you use number fields for grades (a
scale
> > of 60 to 100 for non F grades, or 1 to 4, or whatever scale you like),
you
> > can then get averages, and even perform some simple statistical
analysis,
> > using the relationships.
> >
> > Make certain to create a new class, with a new Class_ID, for each
iteration
> > of a class by semester; in other words, Advanced English 101 Fall 2003
might
> > be Class_ID 2456, and Advanced English 101 for Spring 2004 will be
Class_ID
> > 3216. Do not try to make the ID's do any other work-- they should be
> > meaningless, unique, non-modifiable, and non-desctuctible (keep them off
> > layouts, and define them to 'Prohibit Modification').
>