4 Replies Latest reply on Oct 29, 2012 9:56 AM by MilutinPetrovic

    Serial number calculation

    MilutinPetrovic

      Title

      Serial number calculation

      Post

      I know this might be easy, but I still need some help!


      I am trying to create a calculated field which will be a serial number to the case in the database. 
      The serial number should look like this:

      Ev - 12 - 001

      With "Ev" being just text, "12" being a two digit year, and "001" being a serial.

      So far I have got to this:

      "Ev - " & Year(Get(CurrentDate)) & EvID  --> But this returns a 4 digit year...

      EvID is a Serial Number field being auto created on data entry, but invisible from layout.

      Any ideas?

        • 1. Re: Serial number calculation
          davidanders

               Right will return a set number of characters from the right side of a word, field, or calculation.

          http://www.filemaker.com/help/html/func_ref3.33.44.html

          Home > Reference > Functions reference > Text functions

                

          • 2. Re: Serial number calculation
            johnhorner

                 well... the first part is quite easy.  you just need to use the "right" function to return the last 2 digits of year:

                 right ( year ( get ( currentdate ) ) ; 2 )

                 another option would be to simply enter the whole text string as your "next value" auto-enter serial number ("Ev-12-001" with increment of 1).  it will calculate the next one correctly ("Ev-12-002"), but every year you would have to go in and update the year and reset the serial component.   or you could write a script to take care of it.

                 also, if you plan on using this field to creating relationships, it must either be a stored calculation or simply a text field with an auto-enter calculation.

                 finally, there are a number of other solutions i have seen on the forum for generating serial numbers that don't use the filemaker serial number functionality but rely on utility fields to store the next serial value and various types of calculations or scripts.  some are very straightforward and some with more options such as auto resetting (e.g. every month or every year).  unfortunately, the search engine does not seem to be working at the moment so i can't give you any links.  good luck!

            • 3. Re: Serial number calculation
              philmodjunk
                   

                        Finally, there are a number of other solutions i have seen on the forum for generating serial numbers that don't use the filemaker serial number functionality but rely on utility fields to store the next serial value and various types of calculations or scripts.

                   Please note that such methods usually include a warning not to use them as the primary key in relationships. That warning would apply to the calculated method discussed here. A plain, unmodified, never reset serial number is far better to use for a primary key. Calculated values such as these that encode meaning into the serial ID, are best used, if at all, as a simple field in the parent table for purposes of sorting, searching and to include on layouts and hardcopy to satisfy the powers that be that wanted it in the first place.

                   The reason for that is as much a "human engineering" issue as it is a case of good database design. Once you use a meaning encoded serial number in your database, you open the door to users requiring you to make changes to its format or content at some point in the future. If you have used that field as a primary key in relationships, you then have a major challenge on your hands carefully and correctly updating first the child records and then the parent records to use the updated key. This could require shutting down your database to access by users for an extended period of time while you apply the updates and confirm that they worked. But if you avoid using the field as a primary key, you simply nod graciously to your users and update a single field definition in the database.

              • 4. Re: Serial number calculation
                MilutinPetrovic

                     The final calculation was made to look like:

                     "Ev-"  & Right ( Year ( Get ( CurrentDate ) ) ; 2 ) & "-" & EvID

                     With the EvID being the hiden public key field with the Auto-enter serial number function with the increment of one...

                     So at some point, in 2013 it will look someting like Ev-13-025 for example, but the major thing was the 13 thing, but still maintaining unique ID being that the table is linked to the other one holding the documants in container fields!

                      

                     So thanks! And once again, You guys and this forum make my life much easier! :)