9 Replies Latest reply on Jan 18, 2017 7:46 AM by philmodjunk

    Question regarding usage of auto generate serial number

    glennatenr

      Hi,

       

      I am using auto-enter serial number to generate an incremental quote number.  I'm pretty new at this so possibly doing something incorrectly. When we are on the master screen and want to create a new record in the entry screen I have a button that directs the user there.  The script for the button is

       

      Go to Layout [ layout name ]

      New Record/Request

      I arrive at my layout, my blank record is there and ready to be filled out, my incrementally generated quote number is there and correct.

       

      The problem I'm having is when a user would want to cancel the addition/creation of the record.  My button cancel script deletes  the

      the record, however the quote number still increases despite the cancellation.  If my last record in the DB is 701,when we go to

      the entry screen and the user cancels the request, if we generate another new request the generated quote number is now 703.

       

      Everything works great unless the user cancels the request.  I have generate on commit selected and figured this would keep this from happening

      if the user canceled the entry, but I can't seem to stop it from incrementing even though it does not create a record.

       

      it's probably something pretty easy for you more experienced users but it's driving me nuts !

       

      any help would be appreciated.

       

      Thanks

       

      Glenn

        • 1. Re: Question regarding usage of auto generate serial number
          ninja

          At least two ways to look at this...with different solutions...

           

          1. Do you really care if you skip numbers in your Quote numbers sequence?

            If not...you don't have a problem.

            If so, autoenter serial is likely not the best choice.

          Me...I don't care if numbers are skipped, what I need is a unique number, and I have that whether I skip or not.

           

          2. If you care about skips, don't use the autoenter.

          Use a field in another table that has only one record...and stick the number there.

          That way your New Quote script can grab that number, add 1 and set your quote number to that...then add 1 to the placeholder field.

          On cancel...simply delete the quote record and subtract 1 from the placeholder field.

          1 of 1 people found this helpful
          • 2. Re: Question regarding usage of auto generate serial number
            siplus

            1) there's a script step that might interest you: set next serial value.

             

            2) don't use incremental numbers, use Get(UUID)

            1 of 1 people found this helpful
            • 3. Re: Question regarding usage of auto generate serial number
              philmodjunk

              Your auto-entered serial number is working exactly as you have set it up to work--it's just not working the way that you want it to work. This feature is best used to generate unique ID's that are hidden from the user. In such cases, the "skipped" value in the sequence is meaningless.

               

              While it's possible to reset the next serial value via a script, this is dangerous if it's possible for one person to be creating a record at the same time that another is canceling.

               

              There are two approaches that you can use to ensure an "audit friendly" sequence of values is managed free of any gaps that make auditors ask unfriendly questions:

               

              Set up your layout for creating a new record with a set of global fields. The user fills in the needed global fields and clicks a "save" button. A script performed by that button creates the new record and transfers the data from the global fields to corresponding non-global fields in the new record.

               

              Don't let the user delete records from this table. If they want to cancel, set up a cancel button that changes a status field to "Void" or some such label that tells the users and any auditors that this transaction was voided.

               

              but you don't need to do either unless you have a reason to avoid gaps in your sequence.

              1 of 1 people found this helpful
              • 4. Re: Question regarding usage of auto generate serial number
                glennatenr

                Thanks Guys I appreciate the responses

                 

                Unfortunately I do need to have consecutive numbers with no gaps.  So I'll try to work with the options that you have given me.  Being new with Filemaker I was burning up a lot of time thinking my coding was off and that I just didn't have the correct script, I figured it would not increment the count unless the record was actually saved. I do have the prospect of multiple entries being done at the same time so I believe I'll experiment with a Eric's #2 suggestion of using an external field.

                 

                Thanks for the responses I'll post when it's resolved.

                 

                Glenn

                • 5. Re: Question regarding usage of auto generate serial number
                  philmodjunk
                  I figured it would not increment the count unless the record was actually saved.

                  There is an option in the auto-enter section of field options that specifies that option instead of "on creation". But it's very easy for your users to save a record just by clicking a blank area of the layout so it may not be of much use to you.

                  • 6. Re: Question regarding usage of auto generate serial number
                    keywords

                    I would just add one additional point to what's already been said. Since you say you "need to have consecutive numbers with no gaps", this field then comes under the heading of user-meaningful data. For that reason I urge you not to rely on that field as a primary key field.

                    I once had a client who got really twitchy if they had gaps in a serial number sequence. Worse still, they wanted those numbers to also be in date order. I resolved the issue by setting up what they wanted and treating that just the same as any other piece of user data, while in the background creating a separate primary key field they never saw so didn't care about.

                    These days I use Get ( UUID ) for primary keys. This function has already been mentioned and has the advantage of allowing version swaps without mucking up serial sequences.

                    • 7. Re: Question regarding usage of auto generate serial number
                      Malcolm

                      If you have to have sequential serial numbers you need to control the process at both ends. First, don't create a serial number until you need it. Second, don't delete records that have serial numbers assigned to them. A relatively simple way to do this is to have a second table which does nothing more than store the serial numbers. You can see this being done in the attached example.

                      • 8. Re: Question regarding usage of auto generate serial number
                        glennatenr

                        @

                        If you care about skips, don't use the autoenter.

                        Use a field in another table that has only one record...and stick the number there.

                        That way your New Quote script can grab that number, add 1 and set your quote number to that...then add 1 to the placeholder field.

                        On cancel...simply delete the quote record and subtract 1 from the placeholder field.

                         

                        Thanks Eric !  this one seemed to work best for me, I appreciate everyone's input and I was able to get this resolved

                        Glenn

                        • 9. Re: Question regarding usage of auto generate serial number
                          philmodjunk

                          Use a field in another table that has only one record...and stick the number there.

                          That way your New Quote script can grab that number, add 1 and set your quote number to that...then add 1 to the placeholder field.

                           

                          In a hosted solution with multiple users, this method can easily produce two records with the same serial number as they can both "grab the number" at the same time.

                           

                          Better to void undesirable records instead of deleting them in order to maintain a continuous serial number series.

                           

                          You can also use a related table but use auto-entered serial numbers to get a "auto-entered serial number on demand".

                           

                          Define the "serialNumbers" table to have two fields:

                           

                          _fkQuoteID

                          SerialNumberField

                           

                          Define SerialNumberField to auto-enter a serial number. Link it to your Quotes table like this:

                           

                          Quotes::__pkQuoteID = SerialNumbers::_fkQuoteID

                           

                          Enable the "create" option for SerialNumbers in this relationship.

                           

                          Define __pkQuoteID to also auto-enter a serial number (or use UUID's in text fields)

                           

                          Whenever you need this serial number, run this simple script:

                           

                          Set Field [ SerialNumbers::_fkQuoteID ; Quotes::__pkQuoteID ]

                           

                          If the related SerialNumbers record exists already, no change is made as you are setting a record's field to a value that it already has. If the record does not exist, this step creates it and you can reference SerialNumbers::SerialNumberField to access that value.