6 Replies Latest reply on Jun 30, 2012 1:01 AM by user16491

    User, time and date on all updates to a notes field?

    dscott23

      Running Pro 12 Advanced

       

      Will be using domain authentication for login.

       

      I need to design all of my notes field in my layouts to track who did what and when

       

      should look something like this

       

      dhenne23@1:41pm 6-28-12

      I made this note

       

      other user@1:42pm 6-28-12

      I updated this note

       

      Thanks.

        • 1. Re: User, time and date on all updates to a notes field?
          Mike_Mitchell

          Hello, dscott.

           

          There are several ways to accomplish what you're looking for here. They are all based on a couple of concepts, and have varying levels of sophistication.

           

          You can set a createID, createTS, modID and modTS field in your table. These can be tied to specific fields (like, say, note) by using calculations like this:

           

               createID = Case ( not IsEmpty ( note ) ; Get ( AccountName ))

               createTS = Case ( not IsEmpty ( note ) ; Get ( CurrentTimestamp ))

               modID = Case ( not IsEmpty ( note ) or ( IsEmpty ( note ) and not IsEmpty ( createTS )) ; Get ( AccountName ))

               modTS = Case ( not IsEmpty ( note ) or ( IsEmpty ( note ) and not IsEmpty ( createTS )) ; Get ( CurrentTimestamp ))

           

          When you make these fields, what you'll probably want to do is leave the checkbox that says, "Do not replace existing value" checked for the creation values, and uncheck it for the modification values. In this way, the first time someone enters a value in the "note" field, it'll record who did it and when, and from then on, it'll stick in the create value. However, each time someone modifies the value, it'll overwrite it in the modify value.

           

          Now, how to record the information is where it gets interesting. If you want to write these values to a field that's visible (a single text field), then you could have a field auto-enter these values with some text parsing into that field. You can also set up a tooltip that shows the creation and modification information. Or, you can create individual records every time someone changes the record using a script trigger.

           

          There's also a plugin out there that does this. I can't recall the name right now, but it's pretty sophisticated. Not only does it track changes, it allows full rollback of whatever fields you want, which can be handy.

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: User, time and date on all updates to a notes field?
            thosliot

            If you can live with the name/timestamp being placed after the text to which it refers than all you need is the following auto-enter calc with "Do not replace…" unchecked:

             

            If ( not IsEmpty ( Self ) ; List ( Self ; Get ( AccountName) & "@" & Get ( CurrentTimeStamp ) & ¶ ) )

             

            If you've got to have it before the text then you're probably going to have to use script triggers. At it's simplest you could append the required text in a script run onObjectEnter, but this leaves the possibility that the user deletes or otherwise alters what the script has added; or you could capture the length of the contents of the field onObjectEnter and onObjectSave insert the required text at this point:

             

            onObjectEnter script:

             

            Set Variable $$noteLength; Value: Length ( Get ( ActiveFieldContents ) )

             

            onObjectSave script:

             

            Set Field [Value: Replace ( Get ( ActiveFieldContents ) ; $noteLength ; 0 ; ¶ & Get ( AccountName) & "@" & Get ( CurrentTimeStamp ) & ¶]

            Set Variable [$$noteLength; Value: ""]

             

            the problem here is that if the user messes with the text already in the field, the name/timestamp may be inserted in the wrong place

             

            A more robust solution would be not to allow direct editing of the notes field and have a editable global field (gAdditionalNote) where the user can enter their contribution which would then be appended to the existing note, along with the name/timestamp, via an onObjectSave triggered script on gAdditionalNote:

             

            Set Field [; Value: List ( Self ; ¶ & Get ( AccountName) & "@" & Get ( CurrentTimeStamp ) ; gAdditionalNote )]

            Set Field [gAdditionalNote; Value: ""]

             

            cheers

             

            Tom

            1 of 1 people found this helpful
            • 3. Re: User, time and date on all updates to a notes field?
              thosliot

              Corrections to my previous post:

               

              the 2nd parameter in the Replace() should be $noteLength + 1

               

              cheers

               

              Tom

              • 4. Re: User, time and date on all updates to a notes field?
                keywords

                Ray Cologon has an excellent audit tracking demo which is downloadable and useable if you have FMP Advanced. Find it at:

                 

                http://www.nightwing.com.au/FileMaker/demosX/demoX01.html

                 

                Cheers!

                • 5. Re: User, time and date on all updates to a notes field?
                  dscott23

                  Tom..your first solution works just as you anticipated.  Thanks for the help.  The FMP community is proving to be filled with special people

                  • 6. Re: User, time and date on all updates to a notes field?
                    user16491

                    Definitely like your last suggestion.  I need to implement such a solution and while username and date/timestamp are needed, it is also critcal that current notes cannot be modified directly.

                     

                    Thanks much!

                     

                    Jay