Fields in Portals not filling with Related Data.

[Veronica]

I'm not such a newbie but I have run into something that is totaling frustrating me. I've looked elsewhere in this forum and can't seem to find a similar situation.

I am begining to develop a rudimentary student information system. I have 4 tables, (TO's) Teachers, Schools, Students, and Attendance. Attendance is the many2many table. Each table has a key ID field. But now to describe the problem.

The TeacherTO has TeacherID & TeacherName. The SchoolTO has SchoolID & SchoolName. Each TO has a portal to hold the ID and name fields from the other TO. Currently I have one record in Schools and 3 records in Teachers. None of the records from either of the TO's populate the portals in the opposite TO.

I have experimented with the fields formatted as "Edit Boxes" and "Pop Up List" but neither works. In the relationship I permit records to be created in the TeacherTO through this relationship. But that doesn't work either. I get a message telling me "This action cannot be performed because the field is not modifiable." I have changed the Keyfields with various combinations of permitting or not permitting user to modify; with uniqueness or not; and simply letting them be serial auto-entry fields. Nothing has worked.

I almost forgot to mention that I tried placing these fields as single occurance fields on the opposing TO's and they still did not work. I no longer know if I'm coming or going. What the heck am I doing wrong? Please, Please, Please help!!!

Veronica

[Ender]

Let's take a step back see if you have what you need for tables.

Are you going to have Classes in this system? If so, does one teacher have one class, could a teacher have more than one class, or could a class have more than one teacher? Could a teacher have classes in different schools?

Are you going to need to keep a history of student enrollments (when a student drops, will you need to know the class(es) they have been in, and will you need to see where a student was the previous year)?

[Veronica]

Hi Ender, Thanks.

Actually the Schools are the Classes. A School(Class) can have more than one teacher and during the year, a Teacher could be transferred to another School(Class). We are going to have to keep a record of the Student's Attendance. (Also, the Student could be transferred between Schools or unenrolled and re-enrolled.)

[Ender]

Okay Veronica,

Attached is an ERD (Entity-Relationship Diagram) for a typical Student-Enrollment system, where a class has more than one teacher and a teacher has only one class at a time.

Your table occurence graph will look a little different, as a TOG can't have a loop. But the diagram may help in the overall concept of how to relate the different entities.

It sounds like you're having trouble figuring out what relates to what in the relationships. First, remember that each table should have it's own primary key, which is usually an auto-entered serial number that is unique to each record. To link relationships between TOs, make sure you have a corresponding foreign key field set up in the table you're linking to. Foreign keys will be regular text or number fields (the same type as the primary key you're matching.) Example: relating Student to Enrollment requires the primary key 'Student ID' in Student to relate to the foreign key 'Student ID' in Enrollment (the ERD does not show foreign keys as these are implied.) With 'Allow creation of records through this relationship' set up, the foreign key will get populated automatically as you add rows to a portal with this relationship.

[Veronica]

Ender,

I viewed your ERD and at first didn't get the necessity of an EnrollmentTO. But I worked through it. Thank you.

But I still don't understand the problem with the fields. In all the TOs, I've set up the Primary Keys as suggested in the FileMaker Pro 7 Training CD that I have. They are text fields, auto-enter serial numbers, always evaluated(?), user prohibited modification, always validated, required and unique.

These fields, as Foreign fields in other TOs are text fields. They are additionally formated as pop-up lists connected to the appropriate TO and ID field. When I click on this field in any TO, an appropriate list is presented. When I click on a member of the list I get a dialog box that says: "This action cannot be performed because this field is not modifiable." At first thought, if I go back to the home TO and deselect the user prohibition, when I try to select something from the list I get another dialog box that now says: "This field must have a unique value." Any ideas?

Veronica

[Ender]

Make sure the foreign key fields you're trying to modify are regular text or number fields (not calcs,) and have no validation options selected.

Check that the fields you're setting reside in the table you're layout is based in--that you're not trying to change a field from a related table.

Also, you need not have the Unique validation on for your primary keys, as the serialized numbers will be unique anyways, and that validation option tends to slow things down as you get into tens of thousands of records. And the 'Not empty' validation is not necessary either.

[Veronica]

Ender oh Ender,

AGGGHHHH! This is getting so frustrating. There must be something wrong in this particular database. Maybe I should just trash it and start over clean.

I can't figure this out. It's not that hard. I've successfully done this before though not so elaborately. I've set this database up according to the tutorial with your suggested modifications to the fields and the additional TO.

I have a TeacherTO to SchoolTO, (1-to-many)

A SchoolsTO to EnrollmentTO, (1-to-many)

A StudentTO to EnrollmentTO, (1-to-many)

An EnrollmentTO to AttendanceTO, (1-to-many)

I entered a few records in the TeacherTO, SchoolsTO, and StudentsTO.

When I am in the TeacherTO and click into an ordinary text field, with a "Pop-up List" from the SchoolTO-SchoolID field, the list of available schools pops up.

When I click on a School, I get the dialog box that "this action cannot be performed because the field is not modifiable."

I am about to pull my hair out or shoot this computer. I don't get it!

Veronica

[Ender]

Perhaps you can attached a zipped copy of your file for us to look at.

[Veronica]

Ender, I've never done this before so I hope it works.

NewTestSystem.zip

[Fenton]

I don't see Ender. Double-click the School ID field in the Teachers layout. It is NOT the local SchoolID foreign key, it's the original School ID in the School file (which is auto-enter, no modification; which is good). You want the local Teacher School ID field.

You have the same problem in the Student table, with the Teacher ID. Basically ID fields are almost always local, usually enterable (or Looked up) foreign keys. But neither SchoolID and TeacherID are really needed in Students, as they are implied by the Enrollment. They are a problem in Student, because they will not change if the Enrollment changes.

They would most correctly be related fields via Enrollment, with a relationship sorted by date descending. Then they are the valid for the "latest" enrollment for that student. Enrollment is where everything comes together.

The Teacher Name is supposed to be a non-enterable related field. But, from Enrollment it needs another table occurrence of Teachers, based on TeacherID. Because otherwise it would have to go through Schools (via SchoolID); where you have multiple teachers, hence it couldn't tell which one; the path is ambiguous.

TestSystem2.fp7.zip

[Ender]

Ya, what he said.

[Veronica]

Fenton & Ender,

I had other priorities on Thursday and Friday and it turned out yesterday, Monday also. I thought I had left a message to that effect last Wednesday. However, I've now brought my attention back to this project and your comments and solutions.

Thank you both for changing my perspective of the situation. I've just had the opportunity to study your adjustments to my system. Although I've heard about multiple tables in the training CD, I guess I didn't remember any of that. You've raised my level of sophistication up a notch, Thank You.

One last question in order to confirm or junk my approach to reporting some data. Where students can come and go in a particular school or between schools, I thought it is appropriate to use lookup fields in the AttendanceTO to record the "Week Of", days-of-the-week, "Days Attended" and "Class Days". But I would like to minimize operator keystrokes/errors/involvement by using the "CalendarTO", (I changed the name of the table). Between all the schools involved there are three distinct calendars when schools are in session, or not. It's my preference to build these calendars and use them in the AttendanceTO such that the operator needs only to record the Mon-Fri days of attendance for each student each week. In the end I need to produce a roster of school attendance each week, month and ADA Period, as well as a detailed student history of attendance.

My first thought is to keep all this info in the CalendarTO and use it to record attendance through a portal. But something in my head says this is not the best use. My second thought is to use the "Week Of' and "Class Days" in the CalendarTO and viewed through the portal in the AttendanceTO. Record attendance each day of the week and "Days Attended", (calculation field) in the AttendanceTO but also view it/use it in the portal. But that doesn't seem right either. Is there a better way?

A brief background: I've developed a simpler "Student Information System" which is currently in use with FMP 5.5 in the "Program" offices. But the reporting requirements have become more complex. This system is being developed in FMP 7 and the "Program" offices will be upgraded to FMP 7 at the appropriate time.

Thank you once again for your advice and help.

Veronica