11 Replies Latest reply on Jan 22, 2010 12:53 PM by philmodjunk

    Serial Number by Calculation?

    wmixter

      Title

      Serial Number by Calculation?

      Post

      Hello!

       

      So, I need some help. I'm trying to set up an automatic serial number generator, but I want it to be specific to the records category. For example, lets pretend I was organizing movies by comedy and drama. I would like the drama ones to be D-001, D-002, etc. but the comedy to be C-001, C-002, etc. If I set up another field with a drop down menu to chose comedy or drama, would there be a way to make an automatic serial field that basically says "if field x = comedy, create next 'C' serial number, else, create next D serial number? Originally i was working with 2 separate tables, but that was causing other issues.

       

      I would LOVE any input anyone may have. Thanks!

       

        • 1. Re: Serial Number by Calculation?
          philmodjunk
            

          Is this a database that will be shared over a network or the web between multiple users that all use the database at the same time? That detail can complicate assigning calculated serial numbers as you may get two users attempting to assign the "next value" at exactly the same time. Filemaker's auto-entered serial number handles those issues for you, but when you want a serial number with separate sequences for different categories, you have to take that detail into consideration.

           

          Whatever you do here, I strongly recommend that you not use this specific serial number as a primary key. Keep a standard serial number field and use it in relationships to link related records. Use this field as a "label" field for finding and sorting records--just don't make it THE link between tables.

          • 2. Re: Serial Number by Calculation?
            wmixter
              

            Hi Phil,

             

            Thanks for your input. While I think this will be shared via the web with others, its bound to be only a handful of people (3-5) and it will mostly be edited and updated by myself and one other person. My use for the serial numbers by categories is not to  link tables, but  to have an automated way of creating internal contract numbers based on the contract categories. I would create a separate document id for linking these to other tables. When I mentioned tables before, it was because I originally attemptedto separate my records by category into different tables. I would prefer to not do that, as I would like them to be visable in the same lists, etc.

             

            Sorry, did that clarify things at all? If you have any additionally suggestions, I would greatly appreciate it-I know you're quite the wiz at filemaker!

             

            • 3. Re: Serial Number by Calculation?
              philmodjunk
                

              Thanks, we just needed to nail down some details before I suggested an approach. Since the database is multi-user there's a small chance that two records could get the same exact serial number label simply because two users triggered the same operation at nearly the same time.

               

              Here's a method I worked out with another forum member, in your case, we'll want one table for each category, but don't worry, we're only going to use these tables for the serial numbers. Your combined table will remain intact.

               

              This technique exploits filemaker's built in auto-entered serial number code to avoid duplicates, but also gives you separate series of serial numbers. The only down sides are creating the extra tables and the need to script the process instead of setting it up as an auto-enter field option.

               

              Define two new tables: ComedySerials and DramaSerials

               

              Define each with the same two fields:

              ForeignKey (number)

              CategorySerial (auto-entered serial number, you can include the "C" or "D" in the next serial value setting)

               

              Let's call your main table "main" and set up relationships:

              Main:: PrimaryKey = ComedySerials::ForeignKey (enable creation of records via this relationship for ComedySerials)

              Main:: PrimaryKey = DramaSerials::ForeignKey (enamble record creation for DramaSerials)

               

              Now, when you are ready to assing this label to a new record, run this script:

              If [Main::Category = "Comedy" ]

                Set Field[ComedySerials::ForeignKey; Main:: PrimaryKey]

              Else

                Set Field[DramaSerials::ForeignKey; Main:: PrimaryKey]

              End If

               

              If you have more genres than this, you'll need more tables and some Else IF steps added to the script.

              • 4. Re: Serial Number by Calculation?
                wmixter
                  

                Hmmm, for some reason it isnt working. Does it matter that my category field is a drop down? Does my "PrimaryKey" field need to be something other than a text field? I'm fiddling with it some, but I'm just not getting any results...Maybe I'm just not understanding when I need to run the script?

                 

                Sorry, I'm still building my knowledge of how to use filemaker. I'm thinking about investing in a "filemaker for dummies" type book....

                 

                Thank you for your patience...

                • 5. Re: Serial Number by Calculation?
                  philmodjunk
                    

                  Substitute your internal serial number field for Primary Key. I assumed this was a number field and described the serial number tables with number fields for the foreign key as well.

                   

                  "Doesn't work" doesn't give me much to go on. How doesn't it work?

                  • 6. Re: Serial Number by Calculation?
                    wmixter
                       When I try to run the script, an error box pops up saying "This field cannot be modified until 'PrimaryKey' is given a valid value."
                    • 7. Re: Serial Number by Calculation?
                      philmodjunk
                         Yep. PrimaryKey is your auto-entered serial number it should be setup this way and it should assign the serial number "on creation" rather than "on commit" so there's a value in this field before you try to assign the categorized serial number.
                      • 8. Re: Serial Number by Calculation?
                        wmixter
                           Hm, ok, well it still isnt working- its just spitting out the next chronological number with every new record, regardless of category. It ok though, don't worry about it, its probably just me missing something. Thanks for your help though!
                        • 9. Re: Serial Number by Calculation?
                          philmodjunk
                            

                          Silly me, in adapting this for multiple categories, I forgot a step. It's creating the numbers, you just can't see them. If there were one table, you'd just link to that table inorder to display the label, but since we have more than one....

                           

                          Define one more field in main, CategorySerial.

                           

                          Modify the script:

                          If [Main::Category = "Comedy" ]

                            Set Field[ComedySerials::ForeignKey; Main:: PrimaryKey]

                            Set Field[Main::CategorySerial; ComedySerials::CategorySerial ]

                          Else

                            Set Field[DramaSerials::ForeignKey; Main:: PrimaryKey]

                            Set Field[Main::CategorySerial; DramaSerials::CategorySerial ]

                          End If

                           

                          Main::CategorySerial is where you'll be able to see the category serial number.

                          • 10. Re: Serial Number by Calculation?
                            wmixter
                              

                            Ah, I thought it was working for a second but I'm still getting the "This field cannot be modified until 'PrimaryKey' is given a valid value." message...

                             

                            Right now PrimaryKey is set up as an auto-enter serial (on creation)- should it not be? Should CategorySerial just be that?

                            • 11. Re: Serial Number by Calculation?
                              philmodjunk
                                

                              PrimaryKey should be set up as an auto entered serial number. (I thought you said you already had one of these defined?) It needs a valid number before you perform the script. "On creation" is the simplest way to get that.

                               

                              CategorySerial in your Main table should be a simple number field.

                              CategorySerial in each of the related tables should also be auto-entered serial numbers.