Help, Relationship Record Creation

[DamoTheOmen666]

Basicly I'm computerising a school system.

I've got Student and Subject table, and also a StudentSubject table that links the two so many students can take many subjects.

I've got a portal showing the records linked to it through StudentID to SubjectID

The portal only shows records when i manually input data into my StudentSubjects table

Is there any way i can create a new record with the same StudentID in the StudentSubject Table from my Student Form to prevent me having to go to the StudentSubject table and re-entering the StudentID for every subject each Student is taking?

Sorry if that made no sense, but please help!

[comment]

In the definition of the Student - StudentSubject relationship, allow creation of records in the StudentSubject table. This will allow you to create new records directly in a portal to StudentSubject placed on a layout of Students - you only need to select the SubjectID.

[DamoTheOmen666]

In the definition of the Student - StudentSubject relationship, allow creation of records in the StudentSubject table. This will allow you to create new records directly in a portal to StudentSubject placed on a layout of Students - you only need to select the SubjectID.

Thankyou very much!!

Also, I'm trying to make a table of Days and time periods for each subject so that the system flags up if two chosen subjects clash in the timetable, what would be the best way to do this?

[comment]

How many days will your timetable have? And how many times can one subject appear in the timetable?

[DamoTheOmen666]

How many days will your timetable have? And how many times can one subject appear in the timetable?

Monday to Friday

with 5 lessons per day

One Subject has 5 lessons throughout the week

[comment]

OK. This is not going to be exactly simple, so let me just make sure I have the right picture:

1. The schedule repeats weekly;

2. A subject has many (5) lessons in the schedule, e.g.:

Math:

• Monday 10:00 - 11:00

• Tuesday 10:00 - 11:00

• Wednesday 8:00 - 9:00

• Wednesday 12:00 - 13:00

• Friday 9:00 - 10:00

Does a student enrolled in Math attend ALL the above lessons?

[DamoTheOmen666]

Yes, that's right

Although the main subjects would be double or triple

So Monday Lesson 1, 2 & 3

And Thursday Lesson 4 & 5

Yes the student attends all of the 5 lessons

If this helps

Normal timetable would be-

8:45 - 10:45 2 lessons

then 15 minute break

then 11:00 - 12:00 Lesson 3

then 12:00 - 13:00 Lunch

then 13:00 - 15:00 lessons 4 & 5

[comment]

OK. As I said, this is not going to be simple, and I am doing it "on paper", so it may need some adjustments.

Starting with the core relationships:

Students -< Enrollments >- Subjects -< Lessons

where the Lessons table has these fields:

• Lesson ID (auto-entered serial number)

• SubjectID

• DayName

• StartTime

• EndTime

Note: if you have fixed periods, and you prefer to use a period number instead of StartTime and EndTime, that's fine too.

1. Add a gStudentID field (Number, global) to the Lessons table.

2. Add one additional occurrence of the Enrollments table and two more occurrences of Lessons to the relationships graph, and define the following two relationships:

Lessons::gStudentID = Enrollments 2::StudentID

Enrollments 2::SubjectID = Lessons 2::SubjectID

3. Add a cLessonsIDs field (Calculation, result is Text)) to the Lessons table =

List ( Lessons 2::LessonID )

4. Define the following relationship:



Lessons::cLessonsIDs = Lessons 3::LessonID

AND

Lessons::DayName = Lessons 3::DayName

AND

Lessons::StartTime < Lessons 3::EndTime

AND

Lessons::EndTime > Lessons 3::StartTime

AND

Lessons::LessonsID ≠ Lessons 3::LessonID





This checks conflicts for ONE selected student whose StudentID is in the Lessons::gStudentID field. You should make a script that populates this field with the currently viewed student's ID, and set up a trigger to run this script OnRecordLoad in students layout.





Finally, select a field in the portal to Enrollments and apply conditional formatting when:

not IsEmpty ( Lessons 3::LessonID )

[DamoTheOmen666]

This checks conflicts for ONE selected student whose StudentID is in the Lessons::gStudentID field. You should make a script that populates this field with the currently viewed student's ID, and set up a trigger to run this script OnRecordLoad in students layout.

Finally, select a field in the portal to Enrollments and apply conditional formatting when:

not IsEmpty ( Lessons 3::LessonID )

Right i've done everything, but i need help with these parts

The OnRecordLoad Function isn't there, and how do i apply conditional formatting?

[comment]

To trigger a script OnRecordLoad, go to Layout mode and select Layouts > Layout Setup… > Script Triggers.

Re conditional formatting, see:

http://www.filemaker.com/11help/html/edit_layout.10.14.html#1028330

[DamoTheOmen666]

To trigger a script OnRecordLoad, go to Layout mode and select Layouts > Layout Setup… > Script Triggers.

Re conditional formatting, see:

http://www.filemaker.com/11help/html/edit_layout.10.14.html#1028330

Ok I've done the conditional formatting, and i know how to do OnRecordLoad,

can you give me an example of a code that will populate the gStudentID field?

sorry :/

I tried to do it by using a replace field function specified to the StudentID

But when the script loaded it asked me whether i wanted to replace it but when i looked it was blank

[comment]

Use the Set Field[] script step:

Set Field [ Lessons::gStudentID ; Students::StudentID ]

[DamoTheOmen666]

Use the Set Field[] script step:

Set Field [ Lessons::gStudentID ; Students::StudentID ]

Ok everythings set up

When i enter two subjects that clash nothing happens?

[comment]

Why don't you post your file?

[DamoTheOmen666]

Why don't you post your file?

here you go

i havent done much validation or design yet

Database.zip

[comment]

I didn't change anything, except for some minor layout changes and adding the conditional formatting.

Database1.zip

[DamoTheOmen666]

I didn't change anything, except for some minor layout changes and adding the conditional formatting.

Ok thanks,

Does it not show an error when two subjects clash?

Could you explain to me how everything works and why everything is linked the way it is?

I'm trying to understand this, sorry for asking so much

[comment]

Does it not show an error when two subjects clash?

If you prefer you can add a validation to the Enrollments table. The condition to satisfy is that there are no related records in Lessons 3, so:

not Lessons 3::LessonID

Could you explain to me how everything works

Briefly (it is complicated):

It is easy to detect conflicts between lessons (same day and overlapping time). However, the problem is to detect conflicts between lessons of the same student. A lesson belongs to many students, so a conflict is meaningless unless the student takes both.

The idea here is to get a list of all subjects of a specific student into the Lessons table, and use it to narrow down the lessons that are checked for conflict.

[DamoTheOmen666]

If you prefer you can add a validation to the Enrollments table. The condition to satisfy is that there are no related records in Lessons 3, so:

not Lessons 3::LessonID

Briefly (it is complicated):

It is easy to detect conflicts between lessons (same day and overlapping time). However, the problem is to detect conflicts between lessons of the same student. A lesson belongs to many students, so a conflict is meaningless unless the student takes both.

The idea here is to get a list of all subjects of a specific student into the Lessons table, and use it to narrow down the lessons that are checked for conflict.

Where do i put that validation code? In an existing field or a new one?

So It's distinguishing between lessons for each student with the Global record entering into the field?

You know the relationship diagram, when each table instance is duplicated (Lesson3 etc) is it actually making a temporary new table? or is it just to define other relationships to the original existing table?

[comment]

Where do i put that validation code?

I believe Enrollments::SubjectID would be a good choice - after all, that's where it happens.

So It's distinguishing between lessons for each student with the Global record entering into the field?

Close (there is no "global record" - only a global field). More accurately, it is checking for conflicts of one student at a time - that is the student whose ID is in the global field. If you wanted to print out a list of conflicts for all students at once, you'd be in trouble.

A table occurrence is merely a pointer to the actual table; nothing is being duplicated.

[DamoTheOmen666]

I believe Enrollments::SubjectID would be a good choice - after all, that's where it happens.

Close (there is no "global record" - only a global field). More accurately, it is checking for conflicts of one student at a time - that is the student whose ID is in the global field. If you wanted to print out a list of conflicts for all students at once, you'd be in trouble.

A table occurrence is merely a pointer to the actual table; nothing is being duplicated.

Ahh ok i understand,

The validation didn't do anything, i can still enroll two clashing subjects for one student

[comment]

i can still enroll two clashing subjects for one student

Well, I can't - at least not while the student's ID is in the global.

Database2.zip

[DamoTheOmen666]

Well, I can't - at least not while the student's ID is in the global.

You are an absolute genius

I may need to re-visit because i need to make it so it automatically forms a timetable for each student, although i think i know how i can do it. If i come back where should i post- this thread or start another one?

Once again thanks for all your help!

[comment]

where should i post- this thread or start another one?

I'll let the moderators answer this. In any case, it should be really easy - but again, for one student at a time. Just use Go to Related Record[] to get a found set of the student's lessons, sort them by day and time and print.

[Lee Smith]

Since it is all part of the same need, just make your Replies here.

Lee

[DamoTheOmen666]

Ok, I'll be back :')

Thanks for everything!

[DamoTheOmen666]

ok, so i'm back, and i need help in creating a timetable of each student's subjects in the student table

what would be the best way of doing this?