The change of the school year in Educational Databases

[edudna]

To whom interested for:

A big and crucial problem in educational databases with Filemaker Pro 8.5 is also the changes take place at every school year. I think that many teachers or educators met this problem when they have tried to build a big educational database.

I think that is challenging for every programmer to do this if they have laser time to spend.

Problem

There are six classes in elementary school.

How to change the year of the whole database with one button and do these consequently:

1. Delete the students that have graduated from the School.

2. Copy all the left students.

3. Change the class of the students in the higher level (example A to :.

4. Paste the students to their new classes.

Of course we have to write the new students in the A class. We can not do anything for it…

Anyway which is the better way to organize a database by a school year?

[Ender]

Anyway which is the better way to organize a database by a school year?

Yes. I'd recommend adding an Enrollment table, that has one record for each Student for each School Year. Also, instead of using a Grade field, use a Graduation Year field. This would then remain unchanged (unless someone is held back) and the Grade can be calculated from a global Current School Year and the Graduation Year.

In this design, the Students that leave remain in the system, and only the status of their Enrollment record is changed. This means your relationships may need to be filtered to only show the "Enrolled" Students, but it gives an advantage of having the information for "Dropped" or "Graduated" Students readily available.

The process for promoting students to the next Grade is a pretty straight-forward script that loops through duplicating the Enrollment record and changing whatever fields.

[edudna]

Hellow again,

I had to test all above that is why I have being late but now I have many questions about scripting the change of the school year.

First in our country the expression of the school year is like xxxx-xxxx not exactly xxxx so I have troubles to calculate the difference between current school year and the graduate year in order to tell the class.

Second, I do not know exactly how can start to loop all these after a school year and promote the students.

As a novice I will be gratefull to be more specific but I am learning fast I think.

Pascal

Full of questions and reading

[Ender]

For the first part, where the School Year is in the format "2006-2007", I'd recommend an auto-enter calc like this:

12 - Left(SchoolYear;4) + Left(gCurrentSchoolYear;4)

In this case 12 is the maximum grade (US). You might need to adjust that to handle Kindergarten or Preschool grades.

For the second part, something like this should work (untested):

//Starting with the found set to promote

Sort Records [ Restore; by Name, EnrollmentID ]

Go to Record/Request/Page [ First ]

Loop

If [ SchoolYear = gCurrentSchoolYear ]

Duplicate Record

Set Field [ SchoolYear ; left(SchoolYear;4) + 1 & "-" & right(SchoolYear;4) + 1 ]

End If

Go to Record/Request/Page [ Next; Exit after last ]

End Loop

You might also set a marker in the original records as it loops through, so you know which records have been promoted.

[edudna]

It is not show intelligence but I have a little frustration between the three fields:

-School year

-global current school year

-graduate year

I can not tell the difference between school year and global current school year. Probably I am tired with this database and I have to read many thinks before atempt anything.

[Ender]

No, you're right to be confused. In my example above, SchoolYear should be GraduationYear.

[edudna]

Thank you anyway.

I have a long way to travel in databases but I am excited to do that in Filemaker Pro.

[edudna]

I recover this issue but I need more specific information for this script that can do the follows.

Duplicate the student records.

Change the school year.

Change the grade.

Erase the previous grades or classes.

Save the graduate records for a future use.

Until now I can not fix something that works FM Fellows please help...

[Ender]

If it works, what's to fix? :

Duplicate the student records.

You would duplicate the Enrollment records, but Student records should be unique.

Change the school year.

Yes.

Change the grade.

It should not be necessary to change the grade, since that can be calculated from the School Year and Graduation Year.

Erase the previous grades or classes.

A student's classes should really be held in a separate Enrollment-Section table, where each record is a join between the Enrollment table and the Class Section table. With this, you would not need to remove anything when Enrollments are added.

Save the graduate records for a future use.

Yes, these records would remain in the system, and probably marked with a Graduation Date or something so their Status would change to "Graduated". This way they can be filtered out of Finds and reports easily.

[edudna]

Thank you Ender,

Really need time to check out all these...

Please wait for a reply soon.

Another issue is that sometimes can duplicate a record a million times why is this happens?

[josebetzy]

I service post secondary and elementary schools in Puerto Rico. Dates are important and what I gather from your problem is more of organization and proper designation than programming. If your school is in the USA anywhere, then you would never want to erase records as required year end reports are a must. An enrollment table as suggested is a must, where a student has several records but different school years (fiscal years) would differentiate them. Duplicating records would be done at enrollment because you may get new students entering at each different grade level and others leaving. If you would be more specific, I may be able to help and provide a solution that you can then work on from your perspective.