Help with script for duplicating records
Hi. I'd love it if someone could help me with how to get a set of found records to duplicate and then change the value of one of the fields for all of those new records. (Or if I'm on the wrong track with the structure of this, then that'd be good to know too.)
I have a database that tracks student involvement and attendance in ensembles at school.
The tables I have are:
Student Records (with a primary key for student ID)
Ensemble (list of the various ensembles that the school offers)
Ensemble Participation Data
The Ensemble Participation Data table is related to both of those tables, through the Student ID to the Students Record table and through the ensemble name to the Ensemble table. In this Ensemble Participation Data table, it has a field for the school term, so it tracks who is in the ensemble and in which term (so a student might have multiple records in this table, if they are in the ensemble for the whole year, so term 1, 2, 3 and 4). It also has a repeating field called "Attendance", which allows the teacher to select "Present" or "Absent" for the 15 rehearsals in that term.
There is a portal on the Ensemble layout that shows which students are in that ensemble, and I have a Global field for the "term filter" on this layout so that the teacher can select just one term to view, and not see the multiple records of students in the ensemble for the whole year. This then allows the teacher also to mark the attendance for one term through a layout based on the Ensemble Participation Data which has subsummaries when sorted by term.
My problem is that I don't want the teacher to have to input their students manually for each term. It's likely that most students will be in the ensemble for the whole year, but not all. So once they've put the students into their ensemble for term 1, I want them to be able to click a button that automatically duplicates the records of just that ensemble, and renames the "Term" field in the new records to a new term, such as "2". Ideally there would be a dialog that would ask them what term they would like to be inputted into the new records. (But if I have to have 3 buttons, one for duplicating the records for terms 2, 3 and 4 then that wouldn't be the end of the world.) Then the teacher can go through and delete any students who have left for that term, and it will also mean they now can mark the role for Term 2, while leaving Term 1's role in tact.
Does that make sense? Any ideas about how I would go about doing that?