1 2 Previous Next 16 Replies Latest reply on Mar 29, 2017 8:28 AM by philmodjunk

    Need your help with auto-increment



      Need your help with auto-increment


      Hello there,

      I have a small question. I need to make my purchase order number to be like XXX-YY, where XXX is 3-digit auto-increment serial number and YY is last 2 digits of current year (ex. 10) and I want this counter to reset each year.

      I found a lot of stuff around like that, but I can't really put it all together. I'm sorry for taking your time, but please try to help. Thank you so much!

        • 1. Re: Need your help with auto-increment

          Here's one possible solution:

          Create two fields: an auto-entered serial number field ("ID") which is used solely for relationships to other tables, and a PO_number field which is really only used for "display" purposes.

          Then, to create a new record with a PO_number that increments the way you want it to, you'll need a script that basically does the following:

          * Show all records

          * Sort the records by the ID number

          * Go to the last record (which will be the record that was last created and which will therefore also have the most recent PO_number)

          * Set a variable to capture the 3 digits of the counter and a variable to capture the two digits of the year of the last PO record

          * Create new record

          * Set the PO_number field of the new record with a calculation. The calculation tests the variables. If the "year" variable is the same as the current year, the "year" value is retained and the "counter" is incremented by 1. If the "year" variable is not equal to the current year (which will only happen for the very first PO record created in a new year), the "year" value is set to the current (new) year and the "counter" is reset to 001. The calculation then concatenates the "counter" and "year" values into the format XXX-YY.

          Here's a sample FMP file I put together to illustrate the technique. Have a close look at the Set Variable steps and the calculation in the Set Field step.




          Hope this helps!




          • 2. Re: Need your help with auto-increment

            Note that Nick's method works for single user files, but if you have several users accessing a hosted file and generating PO numbers at the same time, you can get duplicate values if two users happen to trigger this script at exactly the same time.

            If you need a bullet proof method for generating these serial ID's for a networked solution, please post back and I'll post a method that is safe for network use.

            • 3. Re: Need your help with auto-increment

              Dear Nick and Phil,

                 Thank you both for your help. I really appreciate! I'll try now to learn Nick's method. I don't need network version now, but I think I will need it in coming future. Is it really complicated to explain? 

              • 4. Re: Need your help with auto-increment

                Dear Nick

                   When i'm trying to "play" with your sample it works like this: i make a new record and press "create PO number" and it works perfect, but when i'm adding one more record - this scripts starts to make numeration from the beginning, from 001-10 again. But how can I make it work like every new record will be a +1 to this counter? and if I make a record in, let say, year 2011 it will reset itself to 001-11?

                • 5. Re: Need your help with auto-increment

                  Not really, but does require a related table with a serial number field that would need to be reset at the start of each new year.

                  • 6. Re: Need your help with auto-increment

                    But what will happen to older records if this serial number will be reset? They will lost "connection" and mess up, am I right?

                    • 7. Re: Need your help with auto-increment

                      No. You would reset the "next serial value" setting on the field in the related utility table but the values in the fields would remain unchanged.

                      Here's the method:

                      As Nick recommended, define a serial number field in the main table. Define a new table with Three fields: PO_ID, POSerial, POYear.

                      Define InvoiceID as number, POSerial as auto-entered serial nuamber and POYear as number with Year ( Get (CurrentDate ) ) as it's auto-entered calculation.

                      Link it to your main table like this:

                      PO_Table::PO_ID = PO_Numb::PO_ID (enable "allow creation of records via this relationship" for PO_Numb.)

                      Now, to assign your PO number simply run this Script:

                      set Field [PO_Numb::PO_ID ; PO_Table::PO_ID]

                      The first time you run this script on a given PO_Table record a new related record is created and POSerial is assigned the next serial number in the series.

                      At the start of each new year, you'd reset the next serial value setting for PO_Serial back to 1. You can do that manually, by clicking a button or you can even set up a script that checks for this change each time you open the file and updates the serial number setting if it's the start of the new year.

                      • 8. Re: Need your help with auto-increment

                        Dear Phil,

                        Sounds quite easy! Thanks a lot! A will try it today and let you now the result!!

                        • 9. Re: Need your help with auto-increment

                          Phil, I thought a little bit myself about this with your brilliant ideas and here is my question:

                          If we are talking about manual counter reset, what if I make a new table, put new serial number field there and year's date and then in my name table i'll make a new field with calculation just

                          POmain::PO_counter = (POcounter::POserial) & "-" & (POcounter::POyear)

                          And, of course, i'll have another counter in POmain table.

                          Maybe this is much more easier way? Or I do miss something? 

                          • 10. Re: Need your help with auto-increment

                            Dear Phil, 

                            I made this kind of a table, could you please check. There is smth wrong with relations, I think, that's why it is not working.


                            Kind regards, Anton

                            • 11. Re: Need your help with auto-increment

                              On your question about manual resets, yes there are much easier safer ways to reset the serial number setting manually. And resetting the serial counter with a script is a very short script also. There is absolutely no need to create a new table each new year for this. It's much simpler and safer just to reset the next serial value setting on the serial number field in POCounter.

                              On your sample file, there's definitely some confusion in terms and relationships as well as in what fields you actually need to define in POMain.

                              In POMain, you only need one field: POMainSerial (I called it PO_ID in my last post) I don't see the need for POMainID in your sample file.

                              In POCounter, POSerial should be a simple number field--not an auto-entered serial. It will get it's value from the matching POMain serial field. A second field in POCounter, such as POCounterID (I'd use a different field name here) would serve as the auto-entered Serial Number field.

                              You then need a script to trigger the creation of the PO number at the time you are ready to assign a number. It doesn't happen auto-matically, though you can use a script trigger for this if you want to automate it.

                              Set Field [POCounter::POSerial ; POMain::SERIAL ]

                              This is what assigns the next PO number in the series to your PO record by creating a new related record in POCounter--where the auto-enter setting on POCounterID kicks in and generates the next serial value.

                              Your calculation field back in POMain might look like this then:  POCounter::POcounterID & "-" & Right ( POCounter::POYear ; 2 )

                              • 12. Re: Need your help with auto-increment

                                Last one thing. Please check my script for every year auto-reset counter, something is wrong still, but smth minor:

                                If [Year(Get(CurrentDate)) ≠ GetNthRecord(POcounter::POyear;Get(RecordNumber)-1)]
                                Set Next Serial Value [ POcounter::POcounterID; "001" ]
                                Exit Script [ ] End If

                                Thank beforehand. I'll complete it and put online for next users.

                                • 13. Re: Need your help with auto-increment

                                  I'd use a different approach.

                                  Set the following script to run once a day as a server scheduled script set for a few minutes after midnight or each time the file is opened:

                                  Freeze Window
                                  Go To Layout [POCounter]
                                  Show All Records
                                  Go To Record [Last]
                                  If [ Year ( Get ( CurrentDate ) ) > POCounter::POyear ]
                                      Set Next Serial Value [POcounter::POCounterID; "001"]
                                  End If
                                  Go To Layout [original layout]

                                  • 14. Re: Need your help with auto-increment

                                    Oh, I see. Now it works perfect. Thanks a lot!

                                    And here is this file example, if someone need this:


                                    1 2 Previous Next