4 Replies Latest reply on Apr 25, 2013 11:30 AM by BobbyO'steen

    Data Snapshot

    BobbyO'steen

      Title

      Data Snapshot

      Post

           Help Please! I have a database where I keep track of enrollments for different courses. Each course offering is given a unique Class ID.  The enrollments are quantities only and come from another system entirely with no connectivity between that system and the database. I would like to be able to take a snapshot of the enrollment count when a given course confirms. The enrollments may still climb after a course confirms up until the course actually runs.

           I have a field called Students that updates as new enrollments are added. It is a simple calculational field that sums the different enrollment types:

           = POStud + VoucherStud_DV +VoucherStud_CV + Other1Stud + Other2Stud

           This is working fine. I have another field called Student Count at Confirmation. It is a Number field based on a calculated value:

           If (Class Status="Confirmed"; Students; " ")

           The intent is for it to capture or take a snapshot of the student count from Students when the status of the class changes from any other status to “Confirmed”. Under the Options, if I check “Do not replace existing value of field (if any)”, it doesn’t seem to work. If that box is left unchecked, the field will populate with the count when the Class Status changes to Confirmed, but will continue to change as the student count changes. I could really use some ideas. Thanks.

        • 1. Re: Data Snapshot
          philmodjunk

               Your students calculation suggests a less than optimum structure to your data.

               Your If function could be simplified to:

               If (Class Status="Confirmed"; Students)

               But instead of trying to do it as a calculation, set up a script to use set field to capture this data at the right moment and store it in a simple number field. Then subsequent changes won't alter the value.

          • 2. Re: Data Snapshot
            BobbyO'steen

                 Sorry for my lack of knowledge here, but can you tell me how to do that? I understand how to create a script, but have never actually written one. How would it trigger?

            • 3. Re: Data Snapshot
              philmodjunk

                   I don't know how to answer that from the data you have provided so far. How is the class status changed?

                   You describe several fields, but do not indicate whether all your data is in a single table or if you have several related tables involved here. Your references to class status and data for individual students suggest that you should have at least three, if not more tables linked in relationships for managing this data.

              • 4. Re: Data Snapshot
                BobbyO'steen

                     This is an inherited DB. This is a scheduling database and uses two tables. One governs the actual events that are scheduled, another governing the actual course data that makes up the events that get scheduled. I hit the books and was able to work it out. Thank you very much for your help.