Then you should create a new records while keeping the original. A field in this table can identify the school year. If there's a studentID field here that's an auto-entered serial number, then you'll need to divide the table into two parts, one with the student ID number that does not change and has no duplication and then a related record of teacher, class, year, etc. That is created anew with each school year that the student is enrolled.
Thank you for the amazingly quick response! Thinking about it further, it seems the better direction for us to go would be to make a note of changes. I know how to create a note that says who updated a field and when, is there a way to make a note each time a field is altered that lists the previous values of that field?
It is possible, but the method I described will be much, much simpler. Not only will it be easier to set up, but easier to review if you want to go back and see what data was there for a given student in a previous year.
You'd need script triggers set on each such field that capture the original value of the field when you enter the field and another script trigger for another script that compares the value in the field when you exit to the original value and saves that value if a change has occurred. Then you need scripting to log that change in a record somewhere. Seems easier just to keep the original records and create new ones for each new school year.
Okay, i will try to do that. I am unfamiliar with how to split a table, though. I would like to do this in a table I have labeled master that has BankID, which is unique to every student, then that student's name, grade, teacher, and homeroom number. I will be adding a field to specify school year, and then the changing fields depending on that will be grade, teacher, and homeroom number. The Bank ID is my key that is unique and used to relate all of my tables. I am sorry if I am extremely novice at this, first project using filemaker. If there is somewhere I can go to read about splitting tables and how that works, I would love to check it out. Thank you for your assistance!
You'll need to use Import records to move the data from your current table, into a new table and then, once you have confirmed that the import was successful, delete the unneeded fields from the original table.
Let's say you have these relationships now: StudentInfo------<[OtherTables]
with StudentInfo::BankID as your match field and primary key for linking to the other tables in your system.
What you should have after this change is:
StudentYearInfo::BankID = StudentInfo::BankID AND
StudentYearInfo::SchoolYear = StudentInfo::SchoolYear
WIth this setup, you can use Import Records | File to copy current records from StudentInfo into StudentYearInfo. In fact, you can use Import Records to create the new table as part of the import records option if you specify the New Table option as your target table. Once you have imported, you can remove the auto-enter option for BankID in the new table and link the tables as shown here.
StudentInfo::SchoolYear might be a field with global storage so that you can select the current school year once for all students or you may be able to define it as an unstored calculation field that uses the current date to compute the correct value for school year.
Thanks for the instruction. I am still unsure about which table is being modified from year to year. I am assuming studentyearinfo would contain the grade teacher and homeroom fields, and I would just add new records there every year? Would I then be able to make a drop down list for school year to select that year? Thanks so much!
Your assumption is correct and the SchoolYear field in StudentInfo could be given global storage and formatted with such a drop down list.