5 Replies Latest reply on May 22, 2015 9:41 AM by philmodjunk

    onCommit serial value

    CodyKrauskopf

      Title

      onCommit serial value

      Post

      I'm trying to create a serial value to track changes made in our db. However, I setup the field to auto-enter on commit a numeric value increment by one but when I make a change to a record the field does not populate. Any suggestions? 

        • 1. Re: onCommit serial value
          philmodjunk

          It will only enter a value on a new record the first time that it is committed. Most times, On Create, turns out to be the better option.

          • 2. Re: onCommit serial value
            CodyKrauskopf

            So you're telling me I have to find some convoluted way to increment a number to track changes then? Any suggestion on how to create a unique number across all records when someone makes a change? I'm trying to create another table that will copy their changes so that if they make a huge mistake I can go back and fix any issues. 

            • 3. Re: onCommit serial value
              philmodjunk

              You'll need to explain that in more detail.

              Exactly how do you plan on using a serial number to track changes? I can see the need for generating a new value each time the record is modified, but not why it has to be unique across the entire table. This could be scripted with a text field, get (UUID) and the OnRecordCommit trigger, but I can't quite see the reason for the value to be unique and so I have to wonder what I am missing here in what you want to do.

              Auto-entered serial numbers were mainly intended as a way to generate primary keys--unique values that, once assigned to a new record, should never, ever, for any reason change and they serve that purpose quite well.

              You might consider a Field that auto-enters a timestamp (date and time) on modification. That value combined with the record's primary key would be a unique value that changes each time the record is modified.

              Likewise, OnRecordCommit could run a script that increments as "Version number" field. This could be combined with the primary key to generate a unique value also such as a Record identified as 23458.01, where "01" is the version number.

              • 4. Re: onCommit serial value
                CodyKrauskopf

                Ohhhhhh. I was a little set in my way of thinking. I like the version number idea. Every record version# could be set to start at 0 and any time a commit was made that value is incremented and then a unique value could be product#.version# that is set as unique value in my track changes table. Excellent! That's exactly what I needed I was just thinking about the problem incorrectly. 

                 

                • 5. Re: onCommit serial value
                  philmodjunk

                  I once used this trick to set up a hidden "security log" on a PO system for a scrap metal/recycling company. Every time a script was run to print an invoice or to mark it as "ready to print" (one user did the data entry and a cashier then selected and printed the receipt), a new record was created in this log with user names, the primary key and the current version number. This let the manager check for suspicious activity--such as a cashier creating and printing the invoice instead of our "weighmaster" creating it as well as possible cases where a user was making an unusual number of data entry errors that required them to "take back" the invoice from its "ready" status in order to make corrections as this then logged a third or more record for the same PK.