Creating multiple related records

[goanna300]

I have a database of students. When I enrol a student in a "Course.fp3" I also want to enrol them automatically in each of the related "Subjects.fp3" in a separate database. I have over 30 Courses and need to create multiple records automatically. Any ideas?

Steve

[Ugo DI LUCA]

Steve,

Are you sure ? This may be the "terminology" you use, but I have the feeling that a Student has no relation to a Subject file. There's though a relationship from the Student to the Course, and a relationship from the Course to the Subjects.

What do you want exactly ?

[goanna300]

Ugo,

I need to enrol a student in each subject as a discrete record. I don't just want to see those subjects and their content. Because each student has a unique result and attendance in each subject. There are fields for progress, assignments due, etc. The enrolment database records can be could be identified as a concatenated "studentIDcourseID_subjectID_enrolment" field. (the long name is just here to explain the process). The database named "studentIDcourseID_subjectID_enrolment.fp3" will track that student's attendance and achievement in that particular subject. I have no problems with these (concatenated ID) enrolments. I do them frequently. Now all I need is a way to automate multiple record creation for a larger number of subjects.

[Ugo DI LUCA]

Ok,

Might be scripted then, as much as I can guess, with an entry being created in the Enrolment Table with these Compound keys you mentionned.

You'd just create an initial record in a Portal in the Course File, related to the Enrolment file, and trigger a script that will loop and set as many records and keys as the number of subjects related to the Course he is enrolled into.

If you were using FM5 or later, using the ValueListItems ( ), you'd just loop through the list and set as many Enrolment keys.

As a start, is each Subject assigned different Courses, or is it a One To Many where only one Course is affiliated to a Subject ?

The standard script could be ;

1. Go To Related Record (by CourseID)

2. Loop from first record to last and set a global Multiline (or use the CopyAllRecords) with all the SubjectID.

3. Pass the global to the Enrolment Table through a constant relationship

4. Loop there and create as many records and Compound keys of type StudentID&" " &CourseID&" "&ExtractedSubjectID.

[goanna300]

Thanks, Ugo. Will try that.

Yes, Subjects belong to various Courses. One of the uses of this exercise is to give a student recognition for prior learning (RPL), whereby a subject already passed as part of another course may not need to be studied again.

[Ugo DI LUCA]

Then if you have a join table SubjectsInCourses, you'd have to check if the SubjectID you extract within the loop doesn't already exists in the Enrolment Table.

Someone more experienced than I am on educational solutions might direct you in the correct direction. I'm still unsure you really need to create all new records at the moment of the Course Enrolment. I'd favour a script triggered from the Subject file when needed, but that is little concern I think.

HTH

[goanna300]

Ugo,

Please trust me, I need to enrol students in subjects. Most enrolment databases do this; not just filemaker. It is been done in mysql by a local college. Now, back to my topic, which should interest plenty of people outside of education: how do you safely and reliably create a multitude of related records?

[-Queue-]

See this file for one option. You'd need to tweak this to loop and set a incrementing/decrementing global until you've reached the desired number of related records or the global is zero, if you've initialized it with said number. I'm assuming you'd also want to set related fields with the class information. So you'd need to tweak it to pick up on a valuelistitem relevant to the number currently held by the global.

If you require further assistance, just let me know.

[goanna300]

Queue,

Thanks for a nice exercise in adding new records; it almost complements Ugo's looping solution but I think to make it meaningful you need some real-life data. (Sorry, I'm a teacher).

Like,

An airline may need a maintenance schedule. Each of their *ourplanes.fp3* (records) may have any one of 12 *wingtypes.fp3*. Each *wingtype* (record) may have between 21 and 352 *wingtype_Part* and each *wingtype_Part* has its own age and condition for each plane. (Text Field in a related *planes_and_Their_Wingtype_Parts.fp3* database)

So if you specify a certain wingtype for each of the individual planes in your fleet, you will need to be able to enter all kinds of maintenance data for each of that individual plane's parts; "ie for *rivet2345*, "Changed January 6 2001".

When it comes to constructing your database, you cannot predict how many parts a particular wingtype will contain; the A50 wing has 3210 and the B31 has 61. (And so on; there are tailtypes, ruddertypes, etc)...

So we need a third database in your example. I'm sure you can help..

And one more thing,

I have all versions of Filemaker but my client has around 50 versions of 4.0 and 4.1 and they cannot afford an upgrade.

Thanks,

Steve