7 Replies Latest reply on Aug 15, 2012 2:03 PM by philmodjunk

    Comparing and creating serial numbers with letters and numbers

    swillette

      Title

      Comparing and creating serial numbers with letters and numbers

      Post

      I need to create a script that:

      Creates a new range of serial numbers after the script checks another table field of serial numbers to make sure that serial number hasn’t been used.

      So my user types in how many serial numbers he/she wants in a Quantity field.

      Puts in the starting serial number in a field.

      The script checks all current serial numbers to make sure that it isn’t used, creates a new record in another table field with the new serial number, then adds a one to it and checks again…and so forth.

      The serial numbers have to be created manually, and they each have to be unique.

      Some serial numbers in the range may be used already.

      The serial numbers also have to have an ARR00001 (3 letters and zeros in front of the last number (s) to equal 5 digits in all. (examples: ARR00022, ARR00112, etc.)

      Here is my script:

      Set Error Capture [ On ]

      Set Variable [ $$Count ; Value:"" ]

      Go to Layout [ “Firearms” (Firearms) ]

      Show All Records

      Go to Record/Request/Page

      [ First ]

      Loop

      If [ FirearmsSerialCreation::SerialRangeNumber1 = Firearms::__SerialNumberID_pk ]

      Go to Record/Request/Page

      [ Next ]

      Else

      Go to Layout [ “Firearms Serial Creation” (FirearmsSerialCreation) ]

      New Record/Request

      Set Field [ FirearmsSerialCreation::__SerialNumberID_pk ; FirearmsSerialCreation::SerialRangeNumber1 ]

      Set Field [ FirearmsSerialCreation::SerialRangeNumber1 ; SerialIncrement ( FirearmsSerialCreation::SerialRangeLetters ; "0000"&1 ) ]

      Set Variable [ $$Count ; Value:$$Count + 1 ]

      Go to Layout [ “Firearms” (Firearms) ]

      Go to Record/Request/Page

      [ Next ]

      Exit Loop If [ $$Count = FirearmsSerialCreation::SerialRangeQuantity ]

      End If

      End Loop

      Go to Layout [ “Firearms Serial Creation” (FirearmsSerialCreation) ]

      In this script, I have a separate field for the 3 letters and a field for the numbers. I would like it to work with just one field for the serial number.

        • 1. Re: Comparing and creating serial numbers with letters and numbers
          philmodjunk

          Will this be a single user data base or might multiple users be requesting serial numbers all at the same time?

          What should happen if a user request 5 serial numbers starting with A00001 and the serial number A00003 has already been used? Should the user get an error message and be told to use a different sequence or should the numbers A00001, A00002, A00004, A00005 and A00006 be generated?

          • 2. Re: Comparing and creating serial numbers with letters and numbers
            swillette

            The database will be multi-user, but only ONE person will have the rights to create serial numbers.

            I have FileMaker Pro Advanced 12.

            • 3. Re: Comparing and creating serial numbers with letters and numbers
              philmodjunk

              Please answer the last question in my previous post and this new one:

              How will these serial numbers be used?

              • 4. Re: Comparing and creating serial numbers with letters and numbers
                swillette

                Sorry about that, the script should skip the number that is already in the database, and create the next number like your example.

                My plan was once I figured out how to create the serial numbers and check to see if they were used, would be to create another report probably with a different table that holds the serial numbers that were skipped because they were already used. Just so the user could see which numbers were skipped.

                The serial numbers will be used as a unique identifier for each item in the database.

                They have to be generated by the user because each item is manufactured for a different distributor that has their own serial numbering scheme.

                • 5. Re: Comparing and creating serial numbers with letters and numbers
                  philmodjunk

                  The serial numbers will be used as a unique identifier for each item in the database.

                  Please do not use this field as the primary key in relationships. Use an internally generated serial number with no added details for that purpose. Include this number as a simple text field so you can perform searches and sorts for it, but don't use it as a match field linking to other tables.

                  Define two fields, gFirstSerial, gQty both with global storage.

                  In Field options specify Unique values, validate always for your serial number field.

                  Use this script:

                  Set Variable [ $SerialNumb; Value:FirearmsSerialCreation::gFirstSerial ]
                  Loop
                         Exit Loop If [ $K ≥ FirearmsSerialCreation::gQty ]
                         Set Error Capture [ On ]
                         New Record/Request
                         Loop
                                 Set Field [ FirearmsSerialCreation::SerialNumberID; $SerialNumb ]
                                 Commit Records/Requests
                                 Exit Loop If [ not Get ( LastError ) ]
                                 Set Variable [ $$DupList; Value:List ( $$DupList ; $SerialNumb ) ]
                                 Set Variable [ $SerialNumb; Value:SerialIncrement ( $SerialNumb ; 1) ]
                         End Loop
                         Set Variable [ $SerialNumb; Value:SerialIncrement ( $SerialNumb ; 1) ]
                         Set Variable [ $K; Value:$K + 1 ]
                  End Loop
                  Set Variable [ $$DupList; Value:Substitute ( $$DupList ; ¶ ; ", " ) ]

                  Note, in this example I have renamed your field for serial numbers as it should not be used as the primary key in your table.

                  Demo File: https://dl.dropbox.com/u/78737945/GenerateUniqueSerialNumbers.fmp12

                  • 6. Re: Comparing and creating serial numbers with letters and numbers
                    swillette

                    This works great. I have a few questions. I’m not good with the List() function.

                    Are these the two lines where the comparison is completed, searching for a duplicate serial number?

                    Set Variable [ $$DupList; Value:List ( $$DupList ; $SerialNumb ) ]

                    Set Variable [ $SerialNumb; Value:SerialIncrement ( $SerialNumb ; 1) ]

                    If so, how would I search another related table that has past serial numbers that I can compare against to see if there are duplicates?

                    This is a database with items that will always have one serial number and it can never change. My customer sometimes takes one future (higher) serial number and gives it to one item. That’s why there is the need for looking for duplicates from past orders/items.

                    The other question is why can’t the serial number be used as the primary key? That number can’t change (or we can get in trouble) so why not use it as a primary key?

                    • 7. Re: Comparing and creating serial numbers with letters and numbers
                      philmodjunk

                      Neither have anything directly to do with searching for a duplicate value.

                      The first set variable step builds a return separated list of Serial numbers that were found to be already in use. If you check the demo file this list is displayed at the top of the screen after the script is run.

                      The second set variable step increments the Text format serial number. It's a special script step designe for this exact purpose and keeps us from having to extract the numeric portion of the serial ID, increment it and then combine it back with the text and leading zeroes.

                      This script doesn't actually do any searching at all. It relies on the Unique values validation setting on the field to generate and error code any time it attempts to assign an ID code already in use.

                      You'd need to use other methods to search such a table or merge the data into a single table. One way is to use a relationship matching on the SerialID field. You can use IsEmpty ( OtherTable::SerialID ) to see if there is such a value in the other table. Another method is to perform a find on the other table for the specified record to see if any is found.

                      why can’t the serial number be used as the primary key?

                      You can, but it's not the best option as it risks unecessary complications easily avoided if you use a straight auto-entered serial number for the primary key.Any time you add additional meaning (the code letters identifying the manufacturer) or a special format (the leading zeroes), you open the door to possible scenarios where you might indeed need to change that ID code. Consider this hypothetical scenario: Some one uses your database to generate 15 new records with these IDs and enters data in related tables linked in relationships based on this field. Then you discover that a data entry error was made (maybe the wrong MFG was specified) and must change the keys to show the correct data. Think of the data update issues you'll need to deal with in your related data so that they remain linked to the correct record. Then consider that these issues won't exist if you use an internally generated serial number as your primary key.

                      and from your last post, this text suggests another possible scenario where you might have issues that can be avoided with the proper Primary key:

                      My customer sometimes takes one future (higher) serial number and gives it to one item. That’s why there is the need for looking for duplicates from past orders/items.