10 Replies Latest reply on Feb 19, 2013 2:32 PM by vincel

    Serial number reset

    vincel

      Title

      Serial number reset

      Post

           within my database I want to have record numbers so i've created a field called "Line Number."  I've set it up to auto enter with  next value being "1" and increments of "1".   

           The problem I'm having is that users are using the same database over each time they use the database.  They just delete the records but then the "Line Number" continues to move foward. 

           Is there a way to rest the starting value with a script that the user could hit as a button to make thing easy?

           Or is there another way to use record numbering than i'm using?  

        • 1. Re: Serial number reset
          philmodjunk

               How do you use the number in this field? For certain uses, you may not need any field to get the numbers. And in other circumstances, resettting the serial number value may cause more problems than it solves.

               To use a script, there's a script set for that: Set Next Serial Value.

          • 2. Re: Serial number reset
            vincel

                 at this point I have two reasons for the number in the field but as I learn more about FM and continue to develop my database i'm sure i'll use it for more reasons.

                 #1 - Users export data to Excel and email to another employee who will take that data and enter it into our Excel file that produces the output we desire.   (Hoping one day that FM will do all of this for me automatically and I can 86 Excel)   Having the number field allows the two employees to discuss the data via telephone / email and address questions with the data by referencing the number field. 

                 #2 - I haven't yet credit this, but I have a field that I will have a check box that says that this record needs to be associated or put together with another record.   I'm thinking my best option here is to have another field with a value list of all the existing record numbers so that the user can choose the related field.

            • 3. Re: Serial number reset
              philmodjunk

                   Both are good reasons for having a serial number field, but why do these two uses of a serial number field require you to reset the counter?

                   Please note that what you want to do only works if the user is deleting the most recently created record and in multiple user systems, trying to reset the next serial number value while others are creating records risks producing two records with the same value in this field.

                   With regards to #2, this describes a "primary key" field and in FileMaker, this is most often setup as you have with a serial number field, but normally, you would not reset the next serial value setting for such a field just because a record was deleted.

              • 4. Re: Serial number reset
                vincel

                     thanks for the info.  I don't need to reset the counter specifically for either of these two reasons.

                     Only reason is that the same file / database is being used by the Ipad user (single user with file on Ipad, no network) and quite often all they do is take the last database they used and delete all the fields to start over.   They automatically get a copy and name the file when they go to export and send the database.   

                     Technically I don't really need to reset the value but it would be easier for the users if each time they created the new file / database the number was reset to 1.   I'll create the script that you mentioned earlier.

                     Thanks again

                • 5. Re: Serial number reset
                  philmodjunk

                       Well the script step that I identified, set next serial value, can be used to reset the serial value, but won't this result in records being created on the iPad with the same numbers as records created by other users?

                       Isn't that likely to create problems for your users due to confusion over the numbers?

                  • 6. Re: Serial number reset
                    vincel

                         I don't think that will be a problem since right now there is only going to be 1 Ipad user.   Any additional Ipad user in the future will have blank databases loaded on their Ipads and each time they use the file a new file name / database will be created.

                    • 7. Re: Serial number reset
                      philmodjunk

                           Only 1 iPad user, but won't they be sharing that data with other users in some form? What you describe does not fit with the two reasons you have given for having a serial number field in the first place.

                      • 8. Re: Serial number reset
                        vincel

                             Three people are involved in this scenario.   FMP database creator, Ipad user, PC/Excel User

                              

                             Myself who is creating the database via FMP and sending that file to the Ipad user 

                             An Ipad user who will be collecting data and exporting to the PC/Excel User

                             A PC / Excel user who will take that data and manipulate in a spreadsheet that is then presented to a client.

                             The Ipad user and PC/Excel user need the record numbers to talk about questions regarding those records

                             The Ipad user needs the record numbers assocatiated with the primary key for another field

                        • 9. Re: Serial number reset
                          philmodjunk

                               And this is my concern if you reset the serial number each time:

                               

                                    An Ipad user who will be collecting data and exporting to the PC/Excel User

                               That would appear to create a situation where there could be possible confusion due to the fact that you have different sets of exported data with the same numbers.

                               

                                    The Ipad user needs the record numbers assocatiated with the primary key for another field

                               Then this should not be an auto-entered serial number. The values should come from that other source.

                          • 10. Re: Serial number reset
                            vincel

                                 thanks for the info,  I see your point now