7 Replies Latest reply on Aug 15, 2013 3:59 PM by philmodjunk

    How to create a database with changing records while keeping note of previous field data

    AaronNutter

      Title

      How to create a database with changing records while keeping note of previous field data

      Post

           Hi again. I am really stumped on this one and could use some help figuring out where to begin on this. I have a banking database for students that keeps track of their school/grade/teacher information as well as the deposits they make and their dates. I have data entered into my working database for the 2012/2013 school year. Many of these students will be returning for the 2013/2014 school year. When this happens, their grade, home room, and teacher name will change. I have a table listing this information related to a deposit and record table. 

           My question is what is the best way to update these records wtih their new information while not losing their old information (teacher name, homeroom number, and grade)? I really want to set it so that in this table listing the student information, I can select the specific school year and have the teacher, homeroom, and grade fields change appropriately. Is this possible? How would I go about doing this?

        • 1. Re: How to create a database with changing records while keeping note of previous field data
          philmodjunk

               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.

          • 2. Re: How to create a database with changing records while keeping note of previous field data
            AaronNutter

                 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?

            • 3. Re: How to create a database with changing records while keeping note of previous field data
              philmodjunk

                   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.

              • 4. Re: How to create a database with changing records while keeping note of previous field data
                AaronNutter

                     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!

                • 5. Re: How to create a database with changing records while keeping note of previous field data
                  philmodjunk

                       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------StudentInfo------<[OtherTables]

                       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.

                  • 6. Re: How to create a database with changing records while keeping note of previous field data
                    AaronNutter

                         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!

                    • 7. Re: How to create a database with changing records while keeping note of previous field data
                      philmodjunk

                           Your assumption is correct and the SchoolYear field in StudentInfo could be given global storage and formatted with such a drop down list.