7 Replies Latest reply on Jun 4, 2009 8:05 AM by Orlando

    How to create a auto-progressive ID



      How to create a auto-progressive ID


      For each record of mine I need to create a field with an auto -progressive ID  like this:


      BM 1/01, BM 2/01.... BM 3245/01. For the 2001's records.

      BM 1/02, BM 2/02.... BM 1245/02. For the 2002's records... until 2009 records (BM 1/09...BM 2344/09)

      The year should  by the same of the data field called "DATAOBJ" that every records already had.



        • 1. Re: How to create a auto-progressive ID

          Hi jonny


          What you want to do is set up a number field, NUMINC, with Auto-enter Serial set to increment by 1 and then have the concatenated final number in a calculation that will look like this:


          "BM " & NUMINC & "/" & Right ( Year ( DATAOBJ ) ; 2 )


          Thsi shoudl do the trick, you will need to reset the NUMINC Auto-Enter starting value at the beginning of each year however.


          I hope this helps 

          • 2. Re: How to create a auto-progressive ID

            Hi Orlando ! I translated your advice from english to italian (i work with italian version of FMP 10). I called "Progressivo" your "NUMINC" and "DATA ARRIVO" your "DATAOBJ".

            I wrote : "BM " & Progressivo &"/ " & Destra (Anno (DATA ARRIVO) ; 2) 

            This works perfectly but if i change only the day of "DATA ARRIVO" the field become wrong "BM BM 1 / 09 09" instead of "BM 1/09" simply. How can i refresh this value to the correct one without replicant in the same field (BM BM...09 09)

            • 3. Re: How to create a auto-progressive ID

              Hi jonny,


              How have you set the field up, as a Field Type of Calculation or as a Text field with an Auto-Enter Calculation? 


              Also is the Field Type of 'DATA ARRIVO' Date? 

              • 4. Re: How to create a auto-progressive ID

                Hello Orlando,


                "Progressivo" is a Text field with Auto-Enter Calculation.

                "Data " is a data field.


                How should i set them? 

                • 5. Re: How to create a auto-progressive ID

                  OK I think we have got a couple of the fields mixed up.


                  What you should have are three field:

                  1) ProgressivoNumero ( Number ; Auto-Enter Serial )

                  2) DataArrivo ( Date )

                  3) ProgressivoID ( Calculation ; Text = "BM " & ProgressivoNumero &"/ " & Destra (Anno (DATA ARRIVO) ; 2) )


                  I hope this clarifies it and that it works now.


                  Let me know how you get on. 

                  • 6. Re: How to create a auto-progressive ID

                    You are very clever! It works well!

                    Now a little problem remains. I want to auto-restart numeration for each year. Ex. BM 1/07....BM 132/07

                                                                                                                                     BM 1/08....BM 321/08 

                                                                                                                                     BM 1/09....BM 231/09

                    Are you able to do that?

                    Manual restarting it's a process impossible to apply for my company...

                    • 7. Re: How to create a auto-progressive ID

                      There is a script step called Set Next Serial Value that you could use to automatically reset the number, however you will need to have some way of triggering that at teh biginning of the year.


                      One option would be a script the administrator needs to press at the start of the year, another option is storing a value for the current year in a separate table, compare this to the current year, and at the start of the year it resets the number, and then updates the stored date. A bit of a job to do this and you will need to make sure access to this field is limited as it could reset the number.


                      My recommendation would be to have a button an Admin person has to run.


                      If you want any help setting the script up or anything then just let me know.


                      I hope this does the trick, and apologies for the delay in getting back to you.