6 Replies Latest reply on May 20, 2010 12:12 PM by aammondd

    automated revision tracking and sorting



      automated revision tracking and sorting


      Hi guys,

      I'm new to filemaker pro (I have version 11) but not to scripting (although it's been a long long time).  I am running windows7, 64-bit.  


      I am a scientist at a University and I have a series of data that I need to manage.  This data will be changing through time and I want to keep track of these changes.  I want to be able to look at the latest for a given project or experiment, but i want all previous entries to be retained.


      For example, let's say I have the following fields:  (of course it is more complicated than this, but to illustrate my point I think this will suffice).

      project #  (this is a number)

      experiment #   (this is also a number)

      notes   (this will be text)


      so I would have the following records, for example:

      project: 1, experiment: 1, notes: blah-blah

      project: 1, experiment: 2, notes: bloo-bloo

      project: 1, experiment: 3, notes: blee-blee

      project: 2, experiment: 1, notes: bluk-bluk

      and so on ...


      now, I want to go and edit project: 1, experiment: 2 and change the notes field to yaya-yaya


      What i want is for there to be a record of this change.


      My solution is to create a "revision" field for each record.  This is filled with a timestamp (date/time) for when it was created.  So when I want to edit the record, I first click 'duplicate" which will create a duplicate record with a new timestamp.  I then make my changes.  Now I can just look for the record with the latest timestamp.


      So in our example, I'd have:

      project: 1, experiment: 2, notes: bloo-bloo, revision: 5/17/2010 8:56:26 AM

      project: 1, experiment: 2, notes: yaya-yaya, revision: 5/19/2010 4:26:46 PM


      The approach seems like a decent one, but if there is a better one, I'd love to hear it.


      Also, I'm trying to figure out how to do this automatically.  I'd love things to be automatic.  I think I could make a script that would trigger the "duplicate" command whenever I make a change.  I don't know how to do that though, but i'm trying to figure it out.


      The other probelm is how to display the data.  I only want to see the latest of each record, but i don't know enough on how to control the display.  I want to see all the projects and experiments, but, if there is a duplicate project AND experiment then to only display the one with the latest revision.


      So .... any ideas on how to best accomplish this?


      My cursory look at the scripting commands and the power of the sort seems to indicate that filemaker pro can do this.


      Any help would be appreciated.  As I said, I'm quite computer literate and do have some scriptiing knowledge, but not with filemaker pro.  And by no means am I an expert, but I should be able to understand enough to get this done ... with some help.


      Thanks in advance!






        • 1. Re: automated revision tracking and sorting

          So I found a blog by a "filemaker addict' and he posted a script which seems like it might do what I need.  I'm in the process of testing it now, but, for those interested, here's his script: (It uses teh Contact Management template as an example).


          [ 1] #Grab the ID of the contact.
          [ 2] Set Variable [ $ID_Contact; Value:Contact Management::k_ID_Contact ]
          [ 3] #
          [ 4] #Get the field names and values of all fields on the layout.
          [ 5] Set Variable [ $FieldNames; Value:FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]
          [ 6] Set Variable [ $Audit; Value:"Audit Information..." ]
          [ 7] Set Variable [ $i; Value:1 ]
          [ 8] Loop
          [ 9] #Ignore fields that are from related records.
          [10] If [ PatternCount ( GetValue ( $FieldNames ; $i ) ; "::" ) = 0 ]
          [11] Set Variable [ $Audit; Value:$Audit & "¶" & GetValue ( $FieldNames ; $i ) & ": " & GetField ( GetValue ( $FieldNames ; $i ) ) ]
          [12] End If
          [13] Set Variable [ $i; Value:$i + 1 ]
          [14] Exit Loop If [ $i > ValueCount ( $FieldNames ) ]
          [15] End Loop
          [16] #
          [17] #Create the note.
          [18] Go to Layout [ “Notes_Utility” (Notes) ]
          [19] New Record/Request
          [20] Set Field [ Notes::kf_ID_Contact; $ID_Contact ]
          [21] Set Field [ Notes::Text; Notes::Text & "¶" & $Audit ]
          [22] Commit Records/Requests [ Skip data entry validation; No dialog ]
          [23] Go to Layout [ original layout ]


          He says it isn't bullet-proof and is a simple script and could be made better ... but it is a starting point.


          What I'm interested in changing is 

          I want it to do exactly what it is doing, and copy all the records contents to a notes field.

          In addition, I also want it to copy only one specific field to a second notes field.  


          For example, using my example above, let's say I also have a field named "box".  Well, if I make a change to the record, it will copy ALL the fields contents to a notes field as well as copy the contents of the box field to a box_notes field.


          I'll try to make such changes and post it here ...


          but in the meantime, if anyone has any ideas or a better way ... I"d love to hear it.

          (also, if anyone knows how to do the changes I mentioned, I'd appreciate some help as well).



          • 2. Re: automated revision tracking and sorting

            In your earlier thread I suggested an audit record that you could apply to multiple changes

            This has the advantage of not duplicating a bunch of data that doesnt change you can in addition to the changed data capture the keys for the record you are using in order to tie the change back to the original record.


            You would just have to add the following fields to both the audit record and a record with global storage to pass them along.


            Primary Key


            On entry of the field you would set a global field to these values on a modification save you would write them out to an audit record.


            You could duplicate each record like you suggest but you would be duplicating a lot of data if you have lots of revisions.

            • 3. Re: automated revision tracking and sorting

              Ill add that by adding a portal into the audit record linked by the primary key you could view all the revisions at once.

              • 4. Re: automated revision tracking and sorting

                Thanks aammondd!


                I'm sure your suggestions will work, and in fact I think I will try to implement them.  The only reason why I hesitated and posted a second similar question was because I found that script which accomplished my ultimate goal, but, as you pointed out, has some problems (such as repeated data).


                The obvious benefit is that the script is already written and so I just have to modify it to meet my needs.


                But I am all game for implementing a better solution per your idea.  I think I will spend the time doing what you suggested, but considering my lack of experience in databases in general and scirpting for them, I'm kinda leery about it's success.  But, again as you said ... it'll be an adventure!


                Anyways, I think i WILL try your idea.  If successful I'll most likely learn a lot more about FM and scripting.  If I run into problems, i might post seeking help.


                Thanks for your help!



                • 5. Re: automated revision tracking and sorting

                  Just a suggestion but I would put my global storage fields in their own table.


                  When you do this you will need to make sure that you create 1 "New" Record. or the relationships wont work.


                  I like to ensure that my "global records" are setup when the file loads. (ie part of the file properties on open or close perform script)


                  • 6. Re: automated revision tracking and sorting

                    On of the things Ive been playing with on this is being able to turn the field auditing on or off via control tables


                    I  was using the following script triggers

                    Layout Script Triggers


                    On the Layout load I capture any environment variables such as username layout name etc

                    On Record Load  I capture the keys for the record. (You have to define a number of global fields to hold these keys 2 or 3 is usually sufficient as most unique records only consist of a few keys



                    On the field script triggers I capture the contents of the fields 


                    On ObjectActivate  I capture the Get(ActiveObjectContents)

                    On ObjectSave I capture the same and perform  the script Wrtie out Audit Record which consists of just going to the Audit Layout new record and passing a few fields.


                    You can do some more logical checking around those triggers like setting a new record flag in the global fields to prevent writing an audit record on a new record

                    You can also setup a table to check to see if the current field requires an audit record so you can make the writing of  records dynamic. In this case nearly any field you would want to  track the edit of would require all the script triggers to be set. This can be a bit of a development pain. 

                    For me I have a layout that has a number of field controls already setup this way. I open it in a new window and copy to my new layout and only have to assign it the field name for whatever layout I have. This ensures that all my script triggers are set already for any new field layout.

                    The scripts themselves check to see if this field is in a list of fields that needs audit records.


                    At any rate seems to work pretty well for what I want to do with it. It takes a bit of leg work to setup but once I had it all in place it makes doing field level audit pretty simple. Displaying the changes is pretty easy too.

                    Some things you may want to capture in your audit recrod

                    layout name

                    Table name

                    field Name

                    This will give you some flexibility in displaying specific change data on a layout if you wish to.