12 Replies Latest reply on May 25, 2011 7:57 AM by disabled_JackRodgers

    Serial Numbers...On Commit or On Creation?

    Vinny

      Title

      Serial Numbers...On Commit or On Creation?

      Post

      Hello,

      I'm wrestling with why I would use On Commit rather than On Creation for my ID numbers in my tables.  I thought at first that on commit would prevent me from "using up" serial numbers in the event a user decides not to save the new record.  But then I realize that records are saved automatically anyway.

      Does anyone have any examples of when one would want On Commit instead of On Creation?

        • 1. Re: Serial Numbers...On Commit or On Creation?
          philmodjunk

          I rarely find a use for OnCommit, but your experiences could be far different from mine. "Using up" serial numbers has never really been a significant concern with my projects.

          If you design your layout to avoid prevent auto-save, you can create a layout where a user can enter data for a new record, but which does not create a new record and save the data unless the user specifically chooses to save the record.

          This takes several steps and one is a rather odd quirk in how FileMaker does things:

          Clear the auto-save option in layout set up...

          Place an empty, invisible web viewer on your layout, sized to fully cover the body of your layout. Move it to the back of your layout so that all fields and other layout objects are on top of it. This object will intercept all mouse clicks on the background to keep FileMaker from trying to save the record when the background is clicked.

          Add Save and Cancel buttons to the layout. The Save button uses commit record to save the entered data. The Cancel button uses Revert Record to discard the entered data.

          With this design, any other actions by the user that trigger a commit record--changing layouts or switching to a different record--will trigger a dialog asking the user if they want to save the record.

          • 2. Re: Serial Numbers...On Commit or On Creation?
            aammondd

            Ive been using tables of Global Fields as work records (doing all my validations etc) then scripting  the population of the  record(s). Its a lot of work to maintain if all I was trying to do was ensure contiguous serial numbers but it can  be an effective way of handling things. 

            Like Phil Im usually not worried about "wasting" Serial numbers and generally have cleanup routines for layouts that can create "index only" records.

              

             

            • 3. Re: Serial Numbers...On Commit or On Creation?
              philmodjunk

              If I understand aammondd's method, I'm not confident you can use that method in a multi-user environment and be able to guarantee that each new serial value is unique. (Two or more users could be trying for a new serial number at the same time and could then get the same value.)

              • 4. Re: Serial Numbers...On Commit or On Creation?
                aammondd

                I w ould agree with Phil there. Though I use on Creation rather than commit in this case and the likelyhood of "same time" is extremely low. (I dont have many concurrent user)

                 

                 

                • 5. Re: Serial Numbers...On Commit or On Creation?
                  philmodjunk

                  It's unlikely, but all it takes is one such "collision" to ruin your whole day. Wink

                  Relying on an auto-entered serial number--either On Create or On Commit--will not have this issue, only if you use a script and/or auto-entered calculation to assign a serial number--which are ways that users have tried to use when they have additional requirements for their serial numbers such as avoiding gaps in the sequence, when they need to assign the serial ID sometime after the record is first created and saved, or when they need to group their records with serial numbers that start over with each group.

                  In cases where you have to take this approach, you can use a unique values validation rule so that you can at least trap the event where the duplication occurred.

                  • 6. Re: Serial Numbers...On Commit or On Creation?
                    aammondd

                    I use the auto enter always but rely on the script to do my record creation. I just do a number of things with the work record and I update multiple tables via the script.

                     

                    • 7. Re: Serial Numbers...On Commit or On Creation?
                      philmodjunk

                      That's makes me glad I prefaced that post with "if I undertand your method". Wink

                      • 8. Re: Serial Numbers...On Commit or On Creation?
                        ninja

                        I figure the difference between Creation and Commit is what you're going to use your serial# for...

                        For a unique recordID for relationships...OnCreation...period.  Don't worry about using them up (unless you plan to have >100,000,000 records or more per table...and even then...)  Record ID is a hidden thing never used except for the Dbase structure, who cares if it's sequential or not?

                        For a serial# IN ADDITION TO recordID, a field you may want to make look pretty, perhaps the Global process above, or a in-script calculated value might be in order...but then this shouldn't be used for the function of the dBASE...only for appearances.

                        I use text autoenterID's in each table like WS-00000000001.  Increment by 1.
                        I hope to live long enough to run out of numbers!  (I tend to be a low record count user, like 30K records per year per table).  If I waste 10K recordID's per year, I wont mind :)

                        • 9. Re: Serial Numbers...On Commit or On Creation?

                          Consider WHEN not WHICH to be the deciding factor of On Create or On Commit.

                          If you are going to link related records during the script, then On Create might be better.

                          If there is a chance you will revert/toss out the record, then On Commit might be better. For instance,

                          New Record

                          Dialog enter some value Save Cancel

                          If cancel

                          revert record

                          else

                          commit

                          end if

                          Here the serial number won't be wasted if the user cancels. When the user says save, the serial number is created and saved.

                          There are instances where you do not want gaps in your serial numbers, such as when the owner asks you "Why are their so many missing invoice numbers?" and you kinda get the feeling he's thinking, "Is that why I'm losing money?" Plus a less than honest person might be inspired by the many, many gaps in serial numbers.

                          • 10. Re: Serial Numbers...On Commit or On Creation?
                            Vinny

                            Thank you all for your great answers and support.

                            Much appreciated.

                            • 11. Re: Serial Numbers...On Commit or On Creation?
                              RickWhitelaw

                              "There are instances where you do not want gaps in your serial numbers, such as when the owner asks you "Why are their so many missing invoice numbers?" and you kinda get the feeling he's thinking, "Is that why I'm losing money?" Plus a less than honest person might be inspired by the many, many gaps in serial numbers."

                              This is why the ("hidden") PK should be an auto-entered serial number and the Invoice number something entirely different . .  perhaps scripted, that can provide the "owner" with the sequential Invoice numbers he/she requires.

                              RW

                              • 12. Re: Serial Numbers...On Commit or On Creation?

                                There is easy solution creation with no concern for a reliable audit trail and then there is a fully reliable audit trail which would maintain all records and using a flag for delete, etc. The first method is easy and often used and the second method is difficult and expensive.

                                If it is your own single user database then design it to your own standards.

                                If you are designing it for someone else then you have the duty to design it for their best interests. Again, if it is single user you ask what they want, see above. If it is for a business and their profit relies on your database, then the audit trail is a real option to consider.

                                It is difficult to create a record in Filemaker using auto-entered serial numbers and allow the user to not save it without having to delete the record and leave a gap in the serial numbers. Filemaker's auto save attitude prevents this. So creating a layout using globals with a save button and a script of set this field to the global field is needed. In fact, Other applications treats the fields on a layout as globals and don't commit the data until you click a save button.

                                There are reasons to remove records from active use and this can be done using matching fields for the data and simply deleting the data in the link field. For instance,

                                ID Number (serialized number)

                                LINK ID to parent

                                LINK ID to parent Archive

                                NOTES ON DELETION

                                The archive id is the same as the original link. When you want to 'delete' this record you delete the value in the LINK ID to parent field and the record continues to exist but is not part of any calculations, reports, etc. The reason for the 'deletion' is entered. This creates a reliable audit trail.