11 Replies Latest reply on Aug 13, 2012 5:12 AM by RogerAC

    Simple question


      I have a table in a database that autonumbers each record as it is created with a number sequence that started at 2000.


      I want to change this sequence to start at 100 and to renumber the records already stored.


      Is there a simple way to do this?





        • 1. Re: Simple question

          I forgot to say I am using Filemaker pro 12 advanced

          • 2. Re: Simple question

            Yes! Go to your field. Then, do a Replace Field Contents (Under the Records Menu) and set it up just like the picture I attach here.


            Screen Shot 2012-08-12 at 3.25.10 PM.png


            Best wishes!


            Diego D'Alessio

            (the) FileMaker Happy Guy


            Twitter: @fmhappyguy


            MySpace: Really?? MySpace?? Who uses MySpace??

            • 3. Re: Simple question

              I just knew that once I mentioned it was a simple question that it would prove to be difficult.


              Thanks for this advice fmhappyguy but there is a snag, I have done what you say but that option is greyed out as I think it has been created from a hidden table.


              Further advice needed please.

              • 4. Re: Simple question

                Hi playbyte,


                Do you have any relationships which depend upon this serial?  I would suspect yes particularly if you have a serial table creating your IDs.  Why do you feel you need to start numbering over?  That is a rare request.


                If this table holds real data, I would suggest moving with caution until you are very clear how to safely proceed.  Otherwise you can break an existing system (which you obviously do not yet understand)  and relationships involved. :^)


                BTW, if you find the right table and DO decide to proceed using Replace Field Contents[], be sure to show all records first.

                1 of 1 people found this helpful
                • 5. Re: Simple question

                  It could be that the field has the "Prohibit modification of value during data entry" option set, in which case you won't be able to change the field values until you reset it.


                  Screen Shot 2012-08-12 at 4.52.16 PM.png

                  Also, before changing the values of the field, you should check to see if other tables use it as a foreign key to relate back to it. If so, changing the values will break those relationships. So proceed with caution!


                  -- Tim

                  1 of 1 people found this helpful
                  • 6. Re: Simple question

                    Yes there are relationships that depend onthis serial number.


                    This is from a database that I have had designed for me and I am working with a copy so that I dont completly mess up the real data.


                    I am endevouring to understand the logic and how it all works to inrease my own knowledge of FIlemaker.


                    By changing this serial number I am trying to line it up with the current paper system that was used before I commissioned this database.


                    I can live with the way it is as far as its main purpose but understanding how these things work is the key to betterment.

                    • 7. Re: Simple question

                      Hi Playbyte,

                      The serial number field that you have with the relationships tied to it can be left as is, in fact does not even need to be displayed on layouts that the users interact with.  You can add a new field if you so desire that can coincide with the current paper system.  The field can even be allowed to edit, if need be in the case where a paper document skips the sequence.


                      Relationship keys to other tables are best as a background to the data schema in most cases.

                      • 8. Re: Simple question

                        Ah yes that is a good idea one that I hadn't thought of.


                        This serial number is used on  an Invoice so I would need to replace it with my new field that I could set to coincide with the issued Paper ones.


                        Brill, I knew I would get my befuddled mind un picked on here thanks.

                        • 9. Re: Simple question

                          Hi Playbtye,


                          Good on you for trying to understand it all and for working on another copy of it.  Since you are just studying the file the following wouldn't really apply but others reading this thread may be working on real systems so I wanted to add this:


                          As Ben says, no need to change that unique serial.  The Invoice number is a reference number, just like social security or phone.  And it can be editable but still validate to unique always and do not allow User override since it mirrors an internal invoice.  I wouldn't leave the field completely free-form without protections because otherwise your Users can make mistakes or get creative and you can end up with invoice numbers such as: 164 Wanda verify this number please.  No kidding.  :-O   You can use Filter ( Self ; "1234567890" ) in auto-enter (replace), validation on number of characters ... whatever it takes.



                          • 10. Re: Simple question

                            I am endevouring to understand the logic and how it all works to inrease my own knowledge of FIlemaker.


                            By changing this serial number I am trying to line it up with the current paper system that was used before I commissioned this database.


                            This will be an invaluable lesson. The main thing you will learn is "Never mess around with the serial numbers".


                            If you have an alternate series of numbers that you want to use you should create a new field "my old ID". You can enter any value into that field and you can use that field on layouts where you would like to see your old paper system ID.



                            • 11. Re: Simple question

                              Thank you to everyone who helped me to understand what i was trying to do..


                              The warnings are well received as well as the suggestions.


                              I knew i would get ideas for the way forward as i realise that no one person can possibly know it all and by working together a working solution would be found.


                              As a relative new comer to filemaker i am hungry to learn.


                              This thread is now closed, thanks again.