I work on a student database at an elementary school. I'm in the
process of re-designing it to require less maintenance at the
beginning of each school year. The original designer had lots of
dated fields to reflect the current school year. For example,
"Reading00_01" contained the Reading Score for the students in the
school year 2000/2001. At the beginning of each year, I had to create
new fields to reflect the new school year, and change all of the
layouts. Not good.

To alleviate this problem, I created a separate file that contains all
of the test fields. At the end of the year, I will rename the file to
reflect the school year, then create an empty clone file to start the
new school year. I now have 4 dated test score files for that can be
referenced to view a student's previous test scores.

Before this change, the previous test scores were displayed on one
layout in the student database using dated test score "fields". This
gave a nice progressive history of a student.

Now, I've created buttons to go to the desired test year file for a
student, but that doesn't show all of the previous years' scores on
one layout. You have to go to each separate file to see them.

Is there a way to create a layout that would show ALL previous years'
test scores for one student without manually editing the layout at the
beginning of each year?

Thanks for any help.

Jackie

Jackie <jnmusil (AT) yahoo (DOT) com> wrote:

Welcome to "Improper Data Structure 101" in which we explore the many
ways one can structure files to impede the use of and derivation of
value from the data contained within.

Absolutely no slam here on you or the previous designer, because
Filemaker doesn't do a lot to educate users in how to structure data.
It's easy to throw fields on a layout, or make another file and change
the name,so that's the way many people choose to "organize" their data
when they start out.

A logical data structure starts out on paper. Everyone got paper &
pencil ready? Then let's begin.

First, you have a file of pupils. Dandy. You'll add new students as they
enter the school, and remove them once they leave, or once their records
age past the point where you want to include them in reports. Each
student record contains a unique StudentID field, which is NOT a name,
but rather a unique string which doesn't mean anything.

So draw a box on the paper and label it "Students." A student is what
we call an "entity." An entity is something about which we keep data,
such as name, birthdate, address, etc. Be sure to write "StudentID" as
a field name inside this box.

Now, what other entities are we tracking? Tests! Every year there are a
battery of tests which each student must take, though students in
different grades probably take different tests. So let's draw a box
called "Tests" and decide what data belongs with this entity. We'll need
a unique TestID, of course, (write this field name inside the box) then
test name, date, year of test, level of student to which it is
administered, range of scores possible, passing or average score, etc.
You'll have one record per test, and as the years pass, you'll have
"Reading00 2001", "Reading00 2002" etc, accumulating. New tests added
mean creating new test records.

But, you say, this doesn't match students with tests or give me any way
to record or display scores! Patience, grasshopper!

The means of matching students with tests is a third file. Draw a third
box on your paper. Call it "Scores." Technically this is called a "join
file." In the Scores file, there is a StudentID field, AND a TestID
field, so write both field names inside the box. There is one record for
each test each student takes. So, if thirty third graders take 6 tests
in the year 2003, there will be 180 records in Scores for that year,
each containing a StudentID, a TestID, and a score field.

Draw a line from the Students file to the Scores file, connecting the
StudentID fields in both files. Draw a line from the Tests file to the
Scores file, connecting the TestID fields in both files.

Now, using relationships based on the Student and Test ID fields which
are represented by the lines on the paper, you can see and enter the
scores for students. I suggest you study in your manual and any good
third-party reference about creating relationships. Using this data
structure, you will accomplish your goal of being able to see and report
on a student's scores across years, upon scores for a particular test
for a class or a range of classes, actually any way you slice and dice
it.

You do all your reports out of the Scores file, which keeps a record
from year to year. Minimal maintenance is necessary, and you'll be able
to derive the value you're seeking from your data.

Class dismissed. Hope this helps a bit.



--
Lynn Allen Allen & Allen Semiotics
FSA Associate Filemaker Consulting & Training
lynn (AT) semiotics (DOT) com http://www.semiotics.com

Thank you Lynn for taking time to tutor me in join files, although
I'm not sure if it would be practical in this case.

The test scores that I am referring to in my original post, are
derived from a state-generated database from which I extract the
desired data for our school. In this database, each record represents
a student and the different scores are fields.

For example, in the state db:

student1 has fields test1, test2, test3

I would need to convert it to 3 records:

student 1 test1
student 1 test2
student 1 test3

Is there a script to convert fields to records? This would have to
be done once a year.

Thanks,
Jackie

Thank you once again Lynn, for taking the time to type up such a
thorough response. I will definitely use your advice.

Jackie