Jump to content

Help, Relationship Record Creation


DamoTheOmen666
 Share

This topic is 4341 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 )

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

You are an absolute genius :D

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 month later...

This topic is 4341 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.