3 Replies Latest reply on May 19, 2010 6:49 AM by jalexand

    newbie Q - Can filemaker Pro 11 keep track of changes in a field?



      newbie Q - Can filemaker Pro 11 keep track of changes in a field?


      I am a newbie to filemaker pro (and to databases in general) but I'd consider myself an advanced computer user.  I have filemaker pro 11 on a windows 7 (64bit) machine.  This is a database just for myself (I am a scientist with lots of data that I think is suitable for a relational database), so it is not network shared or anything like that.


      So my question is, can filemaker pro keep track of changes in fields?


      (this is an example, my data is quite different., but I think the point is the same).


      For example, say I have a record with three fields: name, address, zip


      Jason, 1234 woohoo lane, 12345


      What if the person changes address?  


      Jason, 9876 banana drive, 54321


      I would update the record with a new address and zip ... but I'd like filemaker to keep a record of the previous address.


      In essence, I want to be able to search for Jason and see his new address as well as his old one(s).


      Is this possible?

        • 1. Re: newbie Q - Can filemaker Pro 11 keep track of changes in a field?

          This is not a built in feature of Filemaker though you could design a system to support it. You can add fields to your table that auto-enter the current date and time when the record is created and/or when it is modified. Alternatively, you could add a field that stores one value for the current record and a different value for past versions. You can use either approach in finds and sorts to distinguish between current and historical copies of the same record.


          You could then add an "edit" button to your layout that first duplicates the current record when clicked. The same script can update a status field and/or your time stamp fields can simply auto enter the current date and time on the new record.

          • 2. Re: newbie Q - Can filemaker Pro 11 keep track of changes in a field?

            There are a number of ways to do this (isnt that always the case)


            The simplest to implement and use 


            1 You could keep the address fields in their own table and add a field for current address you can then display only the current or all of the records in a portal


            For the really adventurous


            2 you can create an audit record table with the following  fields

            Username -type text

            Fieldname -type text

            PrevValue -type text

            New Value - type text

            Creation Timestamp -type timestamp auto-entry creation-date


            You will also need a table with at least one 3 text fields set to global storage

            You will need to set a script trigger that on entry of the field you set a global field to its current value and one to the current field by Get(ActiveFieldname)

            You will need another script trigger on several of the exit paths to store the new value then

            use the script to write a new record into the audit table

            Using Get(Accountname)

            The fieldname global

            the old value global

            the ne value global


            you could use  this method to track any changes in the database you wish by assigning the scripts to the appropriate triggers.


            As a newbie I suggest mastering the first way first 

            then if you really want to learn you can apply the second so you can track any change.




            • 3. Re: newbie Q - Can filemaker Pro 11 keep track of changes in a field?

              Thanks!  Those are excellent ideas.  I'll look into them.  I used to program and write scripts, so, figuring out how to do the "adventurous" way sounds like a fun challenge.  If only I had the time though, lol.


              I was also thinking of having a "revision" filed for each record and when I want to make an edit, I just duplicate the record and have the revision field auto-index.  This would result in multiple entries for each record, but, the one with the latest "revision" would be the most current.  Now if only I can figure out how to do that easily ....