10 Replies Latest reply on Jul 16, 2009 12:35 PM by ronhofius

    How to incrementally update a notes field when a record is updated?

    ravisub

      Title

      How to incrementally update a notes field when a record is updated?

      Your post

      Hi All,

       

      I'd like to set up a text field that is updated and tagged with the account name and timestamp when the record is updated by someone. E.g. there could be a field called "Notes" and each time someone adds to it, the old text remains but the new text goes in below it with a notation about the accountname of the person who added the new text, plus the timestamp.

       

      For example, this is seen in bugtrackers when the new text is added each time someone updates the description, and the comment field has the user's name & timestamp. I can't figure out how to do this, any hints would be greatly appreciated. Thanks,

       

      -rs

        • 1. Re: How to incrementally update a notes field when a record is updated?
          mrvodka
            

          Create a normal text with an auto-entry of Modification Account Name. Let's call it zLastModified.

           

          Create another text field with an auto-entry of :

           

          zLastModified & " - " & Get ( CurrentTimeStamp ) & ¶ & Self

           

           

          If you are using FM10, you can use scrip triggers.

           

          You may also want to consider using a related log table or file instead of one text field.

           

           

          Edit: Sorry should work now.

          • 2. Re: How to incrementally update a notes field when a record is updated?
            ravisub
              

            Thanks mr_vodka. I'm using FM10. I tried the following but it did not work:

             

            1) EditLayout->Manage->Database

             

            2) Went to existing text field and clicked options.

             

            3) Made field a calculated value, clicked specify, then entered the formula.

             

            4) Also clicked "Do Not Replace Existing Value for Field (if any)"

             

            5) Saved & exited.

             

            However when I go to the box & type in text, I can edit it all just like an ordinary text field. I'd greatly appreciate any additional pointers. Thanks again,

             

            -rs

             

            ps: I'm hoping for something like this:

             

            2006-03-07 13:59:27  user1  

            Log entry 1     

             

            2006-03-07 14:03:49  user2  

            Log entry 2

             

            • 3. Re: How to incrementally update a notes field when a record is updated?
              mrvodka
                 Sorry left out some stuff... I edited my message.
              • 4. Re: How to incrementally update a notes field when a record is updated?
                ravisub
                  

                Thanks for the update. It sort of works now, but only if I leave the "Do Not Replace Existing Value for Field (if any)" unchecked. But in the main text field, I can still edit out anything, including the tags. Is there a way to get the following look:

                 

                 

                2006-03-07 13:59:27  user1  

                Log entry 1     

                 

                2006-03-07 14:03:49  user2  

                Log entry 2

                 

                 where the previous entried (timestamp, accountname & log text) can't be edited (i.e. backspaced over)? Thanks again,

                 

                -rs

                 

                • 5. Re: How to incrementally update a notes field when a record is updated?
                  mrvodka
                     If you dont want the users to enter that log field, then jsut turn off entry into it under field behaviors. I think that you really should probably use a related log table.
                  • 7. Re: How to incrementally update a notes field when a record is updated?
                    ravisub
                      

                    Thanks mr_vodka! I'll check them out & report back. Much appreciated,

                     

                    -rs

                     

                    • 8. Re: How to incrementally update a notes field when a record is updated?
                      ronhofius
                         Have you considered, instead of retaining a single notes field that gets the new text added to it, creating a separate table for your notes?  The table might contain, for example, timestamp, name of person who made the note, and the note itself.  It would also have a foreign key field containing the primary key of the main record, and would exist within a portal on the screen where your note field is now.  Then your users could add an infinite number of notes, and each one would display within the portal, time and username stamped, without affecting the others.
                      • 9. Re: How to incrementally update a notes field when a record is updated?
                        ravisub
                          

                        Thanks nmlb. I did end up doing something like this, not with another table but just another text field called "oldnotes". When a note is entered in the regular notes field, I add it to the "oldnotes" field with the author and timestamp information. I put the two fields one on top of the other in the layout, so one is where the note is entered and the other one has the accumulating entry. It seems to be working fine. I don't know enough about foreign key fields and portals, but I will go and play with that to see if I can do it as you've explained.

                         

                        -rs

                        • 10. Re: How to incrementally update a notes field when a record is updated?
                          ronhofius
                            

                          The key to this is to have a field in your main table called, let's say, pk_Client_ID or something appropriate to the nature of your table.  Make it a numeric field, and set its options to be a serial number (then it will grow continuously so that each record has a unique value).  That's the primary key for your main table.  Temporarily put that field on your layout, place the cursor in it, choose Records/Show all records, and choose "Ctrl =" on the PC (I'm not a Mac guy, but it's probably "Command =") or Records/Replace Field Contents.  Choose "Replace with serial numbers" and execute it.  It will then place a unique number in that field in every record in your database.  Then you should go back into the options for that field and choose "Prohibit modification of value during data entry" so that value remains with the record for the life of the record.  Then delete the field off your layout again.

                           

                          Now create the other table with the fields I mentioned.  The foreign key would be, for example, fk_Client_ID, and it is just a numeric field with no auto-entry or validation options.

                           

                          Now under "Relationships" in "Define Database," drag the pk_Client_ID field (or your field name) onto the fk_Client_ID field in the other table.  At that point you have the proper relationship established.

                           

                          Back on your main layout, make sure all records are showing in the found set (ctrl-J or Cmd-J).  At this point I would go to the layout that was automatically created when you created your notes table.  Choose File / Import / File and navigate to the very file you're already in.  Import your main table into the notes table, lining up the notes field, the timestamp field and the user name field.  Line up the pk_Client_ID field with the fk_Client_ID field and execute the import.  Now your notes table has a 1 to 1 relationship with your main table, but it's the starting point for having a 1 to many relationship.

                           

                          On your main layout, add a portal with at least a couple rows showing.  Make each row big enough for your notes field along with the user and timestamp field.  I usually put the timestamp and user fields to the left arranged one above the other, and to the right of them the note field showing at least 3 or 4 lines.  Be sure you include the vertical scroll bar in your portal.

                           

                          From that point on, to add a note, the user pulls down to the bottom of the portal and just starts typing in the note field.  You can use auto-enter options to fill the timestamp and the user name, and by virtue of using a portal for the data entry, the unique ID field will now just take care of itself.  Hint: if you do assign these auto-enter options to the timestamp and username fields before doing your import, be sure not to check the "perform auto-enter options while importing" when you import as described above.

                           

                          That should get you all the way there.  I hope it helps,

                          Ron