5 Replies Latest reply on Jan 4, 2013 10:17 AM by philmodjunk

    Using Multiple Serial Numbers based on a field

    AntonRiehl

      Title

      Using Multiple Serial Numbers based on a field

      Post

           Hello,

           I'm relatively new to setting up a database in Filemaker, and I have run into an issue while trying to generate serial numbers. I am creating a database of music, and need to have different serial numbers for every song based on genre.

           For example, if I have 4 genres: Rock, Pop, Classical, and Country I need to have an incremental code per genre like RCK0001, RCK0002, RCK0003, POP0001, CLSCL0001, CNTRY0001, etc. Is there an easy way to make this happen?

            

           I also have a running serial number that is based on year such as ISRC1300001 (where the first 4 characters are a code, the 13 is the year, and the 00001 is the serial number). I already have this portion working, but I need to have the serial number 000001 reset when the year changes to 2014. I know this should be done with a script, but I can't quite figure that out. 

            

           Any help would be greatly appreciated.

            

           Thanks

            

           (p.s. sorry for bringing up the similar topic of serial numbers, I just wasn't able to find this particular topic among the numerous existing topics...) 

        • 1. Re: Using Multiple Serial Numbers based on a field
          philmodjunk

               It's a question that has been asked a number of times before. You'll need to answer a few questions first?

               Why do you need to set up such serial numbers (both those that have a different series and text for every genre and the one that needs to start over with every year)?

               What problems do they solve for you that can't be managed from a simple serial number--something that is far simpler and safer to implement?

               Whatever you do, I strongly recommend that you not use either of these as match fields in your main relationships. In more technical terms, don't use them as primary keys.

               Is this for a database that currently, or potentially in the future, is hosted over a network such that multiple users might be generating these serial numbers all at the same time?

               How did you implement the year based serial number? Is the serial number portion an auto-entered serial number?

          • 2. Re: Using Multiple Serial Numbers based on a field
            AntonRiehl

                 Hi, Thanks for the fast response, I'll try to answer your questions as best as possible.

                 First, in no way are any of these codes being used as a match field ever. All of my tables have independent IDs that are auto serialized, which are used for relationship purposes, and in fact, these should almost not be considered serial numbers as much as they should be thought of as a reference code for use outside of the database.

                 There is a potential in the future that multiple users will have read access at the same time, however, it is unlikely that more than one person will ever be creating records at the same time. 

                 The reason for needing multiple codes is that we are a music production service, and we need to keep track of how many tracks are produced in any given genre. This number is used for goals we set with our writers in each genre. So for example, we currently have 120 tracks in a genre called "Deep House" and we may need to have 150 to be able to provide them to a customer in the future. 

                 As for the one that needs to reset every year, it is an "International Standard Recording Code" (ISRC) and contains the following data: A company code for our publishing company (assigned to us), the year it was produced, and the number of tracks per year. This code is defined to us by an outside organization, and we have to follow it specifically. I am currently creating the code with the following auto-enter calculation:

                 "CompanyCode" & Right ( Year ( Get ( CurrentDate ) ) ; 2 ) & ISRCSerial

                 The CompanyCode is set manually, and I have a separate field on the same record that is an "auto-enter serial" field creating the ISRCSerial code. 

                  

                 Let me know if that all makes sense, and thanks again for the help. :)

            • 3. Re: Using Multiple Serial Numbers based on a field
              philmodjunk
                   

                        however, it is unlikely that more than one person will ever be creating records at the same time.

                   "Unlikely" is not a 100% guarantee and that is something we need here or risk a situation where the method used to generate your values generates a duplicate.

                   

                        The reason for needing multiple codes is that we are a music production service, and we need to keep track of how many tracks are produced in any given genre. This number is used for goals we set with our writers in each genre. So for example, we currently have 120 tracks in a genre called "Deep House" and we may need to have 150 to be able to provide them to a customer in the future.

                   But why does that need a special ID number? You can find records by genre and year fields and produce counts in reports without needing such a special number and that approach will avoid the issues and extra design work needed for your values.

                   I think of these types of serial values as "secret decoder ring" serial numbers. They have special meanings encoded that those that know the code (have a "decoder ring") and decipher at a glance. Historically, these come from the days when computer systems were much more limited and a programmer often had just a few characters available to store such information--such as on Ye Olde Hollerith (Punched) cards. In today's relational systems, there is much less need for such as you can simply display the full values of the original fields instead of encoding the data.

                   But there are two cases that I know of where they are still necessary: 1) Some labels printed from your database may need this type of "encoded" field to pack a lot of information into a limited space on the label. 2) Legacy systems (sometimes human, sometimes electronic...) external to your database require it.

                   With the ISRCSerial code, there's a script step, Set Next Serial value that can reset the ISRCSerial field's next value back to zero. You can run such a script once a day or set a script to run daily (or each time a file is opened) that checks the year and resets the serial number the first time the year changes.

                   If you must have your genre specific serial numbers, you can set up a self join that matches by your genre field:

                   CurrentTable::genre = CurrentTable 2::Genre

                   and then:

                   Max ( Current Table 2::SerialValue ) + 1

                   can produce an incremented value specific to the genre. But extra steps are needed to ensure that you don't get two records with the same exact value.

              • 4. Re: Using Multiple Serial Numbers based on a field
                AntonRiehl
                     

                          But there are two cases that I know of where they are still necessary: 1) Some labels printed from your database may need this type of "encoded" field to pack a lot of information into a limited space on the label. 2) Legacy systems (sometimes human, sometimes electronic...) external to your database require it.

                     You are definitely right about this being a "secret decoder ring" type of code, however, it is already necessary, most likely due to a "Legacy Human System". Although, this information is also embedded as metadata in all files that we send out (and have already been sending out), and there is a perceived limit to space when applying metadata to certain fields in an audio file. 

                     Thanks for the help though, I think I can make everything work with this information, I'm just still new to scripting, so it might take me a little while to perfect my daily check script. 

                     Thanks

                • 5. Re: Using Multiple Serial Numbers based on a field
                  philmodjunk

                       I would say that your "metadata" is an electronic example of a "label" that needs to pack this data into limited space--thus needing the encoding.