10 Replies Latest reply on Jun 23, 2010 3:13 PM by LaRetta_1

    Create a unique alpha numeric code

    Pam

      Title

      Create a unique alpha numeric code

      Post

      I am a novice user of Filemaker Pro v10 (MAC), with limited script writing ability.

       

      Is there a way to create a unique customer ID code. To be based on the first 3 letters of the surname and the 3 digits (commencing 001). When one inputs the letters, the next number in the sequence should be added.

      If it is not possible to automate the function, how would one show the options already used, so that the user can manually create the next sequence?

        • 1. Re: Create a unique alpha numeric code
          LaRetta_1

          You do not want a customer code which has meaning.  What if the person changes their last name?  Well, there are many reasons that you should stick with using a number field named CustomerID and make it auto-enter and select serial option and start with 1.  This is the only way that you can guarantee your relationships will remain intact.  Every table should have this unique ID.

           

          But you can create a calculation ( result is text) which you can display.  It would be something like:

           

          Left ( LastName ; 3 ) & CustomerID

           

          :smileyhappy:

          • 2. Re: Create a unique alpha numeric code
            etripoli

            While LaRetta makes a good point about not using the last name in the ID field, it might be better to start the ID numbering at something like 1001, or 10001, depending on how many customers you plan to have.  Starting at 1 seems novice.  With that said, it is also possible, and sometimes advantageous to use alphanumeric IDs, because they give you almost 4 times the possible IDs, using the same number of characters as a strictly numeric ID would give.

            However, I don't think your method is entirely simple to implement.  It sounds like you want to increment the numeric portion of the ID, based on the first three letters.  So if you have 3 customers with the last name of Smith, their IDs would be SMI001, SMI002, SMI003, correct?  One possible way to do this is by a self-relationship of the customer table, based on the first 3 letters of the Customer ID.  This would give a calculated field the ability to determine the largest number used for customer IDs with the same first 3 letters.

            • 3. Re: Create a unique alpha numeric code
              Steve Wright

              I do this to generate customer account numbers (not used as a primary key) although my method below was written quite some time ago, its not the most optimal way of doing this ! But still... it does work.

               

               

              You will need a relationship using two table occurrences of the clients table

              Join these with the accno field (or whatever you have it named as)

               

              ClientTable::accno = ClientTable_check::accno

               

               

              The script would go something like this :

               

               

              SetVariable [$i ; Value:"001"]
              Loop
              Set Variable [$test  ; Value: Upper( left(surname ; 3)) & $i ]
              Set Field [accno ; $test ; 
                If [not isempty(ClientTable_check::accno)]
                Set Variable [ $i ; Case(
                                     Length($i + 1) ≥ 3 ; $i +1  ;
                                     Length($i + 1) =2 ; "0" & $i +1  ;
                                     Length($i + 1) =1 ; "00" & $i +1  ;
                                      $i + 1
                                     )]
              Else
                 Set Variable [$done ; Value: "done"]
              End If
              
              Exit Loop If [$i > "9999" or $done ="done"]
              End Loop

               

              In the code above, from memory, I think I had to force it to put the leading zeros in place, hence the case statement.

               

              Lets take Smith as an example

               

              What its basically doing is looping through setting account numbers starting at SMI001

              Checking if that exists in the clientTable_check occurrence, if not exiting the loop, otherwise it will move on to SMI002

               

              As mentioned, I created that some time ago, I have recently been thinking of a way to speed this up such as 

              performing a search for SMI* then using the max function to get the next available acc no, but Ive not had time to play yet.

               

               

               

              • 4. Re: Create a unique alpha numeric code
                philmodjunk

                Careful,

                 

                The methods described here by Etripoli and SWS can result in duplicate codes in multi-user systems as you might get two users generating records at the same eact instant in time for people whose last name starts with the same first three letters. It may be unlikely, but it can and does happen.

                • 5. Re: Create a unique alpha numeric code
                  Steve Wright

                  Agreed !    

                   

                  Like I say, mine is certainly not the best way of doing this, I realise this myself and will be changing it.. however since its not a primary key, nor used for any other purpose other than display, the possible implications are limited.

                  • 6. Re: Create a unique alpha numeric code
                    philmodjunk

                    I wouldn't call the implications limited. :smileywink:

                     

                    Having two customers with the same ID code, even if it's not a Primary key could cause all sorts of mischief resulting in confusing and possibly inaccurate reports since you are likely to still search and/or sort on this entry.

                    • 7. Re: Create a unique alpha numeric code
                      etripoli

                      Agreed.  Another solution would be to force the field to be unique, therefore the duplicate ID would be given an error message upon commit.  But in this case, an auto-entered serial number would probably be better.

                      • 8. Re: Create a unique alpha numeric code
                        Steve Wright

                        Agreed again :-)

                         

                        The above was actually created for a single user system but is now being adopted as a multi user (hence the need for a re-work) Im not disagreeing one bit that its not perfect, in fact... Im hoping this discussion continues :-P may save me some time working out a better method.

                         

                        My requirements are to have incrementing alpha numeric codes too.. there has to be a fool proof way, ive not really started working on a solution, but will be...

                         

                        If SMI001 exists, the next must be SMI002 etc

                         

                        Using an auto enter serial would end up with numbers everywhere, soon jumping into larger numbers.

                         

                        i.e. If you have 15,000 records, but only 2 Smiths one at the start, one at the end  

                        You would end up with :

                        SMI001 and 

                        SMI15000

                         

                        Sage Line 50  for instance had (may still have) an 8 character limit for the account number

                        A method such as this allowed 99999 of each first three letter groups before you started to run into problems, which could take a while.

                        Using an auto entered serial number, you would run into the issue a lot quicker.

                         

                         

                        Edit :  999 + 3 letters =/= 8  (LOL)   I should have put 99999 

                        • 9. Re: Create a unique alpha numeric code
                          philmodjunk

                          Hmmm, I'm just brain storming here and could be way off, but maybe with filemaker 11, you could set a unique values validation rule and then use an OnObjectValidate script trigger to confirm that the value is truly unique and to bump it up one if it is not.

                          • 10. Re: Create a unique alpha numeric code
                            LaRetta_1

                            That will not get away from the issue that User may not have committed their record yet.

                             

                            "it might be better to start the ID numbering at something like 1001, or 10001, depending on how many customers you plan to have.  Starting at 1 seems novice. "

                             

                            Well I was in a hurry and I truly was trying to turn the person AWAY from this thinking (and rightfully so).  If I were to pad the number, I wouldn't do it in the serial, I would do it using SerialIncrement()  within the concatenated calculation (new field I suggested).

                             

                            Regardless, whether BRO1 or BRO10001, it means nothing.  What is the benefit from knowign that a customer is one of 3600 BRO-beginning-3-letts customers?  Besides, what business is it of the customer how many BRO customers you have?  My point is that it shouldn't be used because it moves you down the 'this ID means something' pathway.  Bad idea.

                             

                            If you MUST do this (and please do not), then at least use a Serial table and tightly script and control the whole thing.  And no, don't ask me how to do it because I do not believe in assisted suicide. :smileywink: