I need help displaying data. Each record in the database contains, among others, fields for

teacher number,

period taught,

course taught and

period iteration (i.e., whether the course is the first among those taught that period, the second, etc.)

I want to display in a table format the courses taught by teachers by period. The teachers should appear in rows and the periods in columns, with the names of courses in the intersections. (To complicate it, sometimes a teacher will teacher more than one class in a period, but that's not really a problem.)

I've come to the conclusion that I need a portal for each period, but I don't know how to develop a relationship that will display just the courses for a given teacher-period combination.

Please forgive if the description of what I would like to do is clumsy as I am new at this. Any help will be greatly appreciated.

You're going to need two files for this. One for Teachers and one for Classes.

The Classes file will have fields for Class_Name, Period, and Teacher_ID.

Let's say that there are six possible periods. Then you're going to want to create six calculation fields in the Teachers file that combine the Teacher_ID with the numbers 1 through 6.

Teacher_Period_1 = Teacher_ID & "1"

Teacher_Period_2 = Teacher_ID & "2"

etc.

In the Classes file you'll want to create one calculation field that is a combination of the Teacher_ID and the Period fields

Teacher_Period = Teacher_ID & Period

Now you create six relationships from Teachers to Classes using each of the fields in Teachers to match to the Teacher_Period field in Classes

"Classes|Teacher_Period_1" set to "Teacher_Period_1=:Teacher_Period"

"Classes|Teacher_Period_2" set to "Teacher_Period_2=:Teacher_Period"

etc.

Now create a layout in list view that has the following fields in each column:

Teacher_Name

Teacher_Period_1::Class_Name

Teacher_Period_2::Class_Name

Teacher_Period_3::Class_Name

Teacher_Period_4::Class_Name

Teacher_Period_5::Class_Name

Teacher_Period_6::Class_Name

That should do the trick. It doesn't use a portal, but it gets the job done.

Chuck

Your solution worked perfectly. I actually implemented it with a self-join because I am developing this for people with little familiarity with FMP and wanted to keep it simple. Thanks!!

You wouldn't happen to know how to:

1. force a subsummary to page break when the records in a category will run over to a second page?

2. enter the total pages of a document in a footer so, e.g., "page 1 of 7" can print?

Reports are one of my weaker points, but someone else here might know of a way to do what you want with that.

Regarding the other question, however, create a global field called gPage_Count and include it on your layout like:

Page ## of <<gPage_Count>>

Then include the following code in your script:

Enter Preview Mode []

Go to Record/Request/Page [ Last ]

Set Field [ gPage_Count, Status (CurrentPageNumber) ]

Go to Record/Request/Page [ First ]

Chuck

Thanks again