8 Replies Latest reply on Mar 31, 2010 2:46 PM by BobSchwenkler

    Generate somewhat random, but unique codes?

    BobSchwenkler

      Title

      Generate somewhat random, but unique codes?

      Post

      The subject header says most of it. Anyone have a good way to do this? I need to generate codes that individuals can use to login and download promotional material, and these codes need to be unique from each other. I'm thinking ~8 characters, numbers and letters. The scripting that uses these codes is case sensitive, so variability in upper and lower case would be nice to have as well.

       

      I'd like to create codes that aren't linearly related, or at least not related in obvious (easily guessable) ways. My math skills aren't too hot. Maybe a function that bases the output off of a sequentially increasing number global field?

       

      Thanks!

        • 1. Re: Generate somewhat random, but unique codes?
          philmodjunk

          You might script this:

           

          Define a related table, UsedValues

           

          Link them as:

          YourTable::Code = UsedValues::Code

           

          Loop

              Set Field [YourTable::Code ; Random ]

              Exit Loop [ IsEmpty ( UsedValues::Code ) ]

              Set Variable [$I ; Value $I + 1 ]

              Exit Loop If [ $I > 500,000]

          End Loop

          If [ IsEmpty ( UsedValues::Code ) ]

             set FIeld [UsedValues::Code ; YourTable::code ]

          Else

             Show Custom Dialog ["Error, could not assign unique code in a timely fashion"]

          End IF

           

          The above Random function returns a decimal value from 0 to 1, so you may want to include a more sophisticated expression or a custom function to generate a random alphanumeric sequence.

           

          You could also assign a large sequence of codes to records in a table and assign a random number to a second field in the same table. You'd then sort your records by this random number field to shuffle them into a random order. Assigning a new user a code would just be a case of using this random order to give them the next code in the table. (You'd then have to generate additional codes from time to time to keep the table stocked with unused codes...)

           

          • 2. Re: Generate somewhat random, but unique codes?
            LaRetta_1

            Hi Bob,

             

            You can assign a Customer code right within the customer table itself.  And you can expand the optional characters to 62 (which is all numbers and both upper and lower alpha.  Here is an approach (back up before applying any new process):

             

            Use a code field (text) in your Customers table to provide them with a unique code.  Set the Code field  (in definitions, Options, Storage in the bottom pop-up) to default language of Unicode.  This makes the field case sensitive whether using in field-level unique validations or whether using in relationships (on the child side).  Now duplicate your Customers table and call it Customer_Codes and join as:

             

            Customers::Code = Customer_Codes::Code

             

            Then two scripts

             

            Script 1 - Check Code:

            # Check for existing code for this customer

            If [ not IsEmpty ( Customers::Code ) ]

            Show Custom Dialog [ “No” ; “Yes” ; “Code isn’t empty. Do you want to clear existing code?” ]

               If [ Get ( LastMessageChoice ) = 1 ]

               Half Script

               End If

            End If

            Perform Script [ Create Code ]

             

            Script 2 - Create Code:

            Set Field [ Customers::Code = “” ]

            Loop

               Commit Records/Requests

               Exit Loop If [Length ( Customers::Code ) = 8 and Count ( Customer_Codes:;Code ) = 1 ]

            # grab the character at random using the Middle() function below 

             Set Field [ Customers::Code ; Customers::Code & Middle (  "0123456790ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" ; Random * 62 ; 1 ) ]

                  If [ Length ( Customers::Code ) > 8 ]

            # If length is greater than 8, it means it couldn' exit the loop because it was a duplicate to existing code so restart it

                  Perform Script [ “Create Code” ]

                  End If

            End Loop

             

            UPDATE: I modified this post to combine two posts and correct a commit records issue.  BTW, I believe using these 62 characters will give you a possible 3,381,098,545 unique codes.  You might want to begin the script by checking if there are that many customers and if so, halt the script and do something else. :smileyvery-happy:

             

            UPDATE:

            "0123456790ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

            ... my apology.  I typed this at 5 AM and mis-typed.  Please change the red portion to 89 ... non-compliant, bad fingers!  :smileysurprised:

             

            • 3. Re: Generate somewhat random, but unique codes?
              comment_1

               


              LaRetta wrote:

              I believe using these 62 characters will give you a possible 3,381,098,545 unique codes.


              I believe it's 218,340,105,584,896 (=62^8).

               

               


              • 4. Re: Generate somewhat random, but unique codes?
                LaRetta_1

                If each customer doesn't get a code but promo materials hold the code, just use a codes table joined to itself and only the Create Codes script (adding a New Record to the beginning of it .  You can have a description field and DateStart and DateEnd for the promo info as well.  This should not be used as a unique serial key for joining relationships (you should instead use auto-enter serial).  If used for each customer, it should only be a unique code they can use for accessing their special promotions.

                 

                UPDATE:  The New Record portion should be different script or outer loop.  If within the Create Codes script, and if the code needs to start over, it will create a new record instead of re-applying a new code to the existing record.

                • 5. Re: Generate somewhat random, but unique codes?
                  LaRetta_1

                   


                  comment wrote:

                   

                  I believe it's 218,340,105,584,896 (=62^8).

                   

                   


                   

                  I used this calc in FileMaker to figure it out:  Combination ( 62 ; 8 ). 

                   

                  I guess that wasn't the way to figure it out.  Much appreciated for the clarification. :smileyhappy:

                   


                  • 6. Re: Generate somewhat random, but unique codes?
                    comment_1

                     


                    LaRetta wrote:
                    I used this calc in FileMaker to figure it out:  Combination ( 62 ; 8 ).

                    That would be the right calc to use, if "ABCDEFGH" and "ABCDEFHG" were considered duplicates.

                     

                     

                    • 7. Re: Generate somewhat random, but unique codes?
                      LaRetta_1

                      Ah! Cards can shift positions so would show as duplicates even if in different order but this code is in fixed order so out-of-sequence dups are not possible!  Great example!

                      • 8. Re: Generate somewhat random, but unique codes?
                        BobSchwenkler

                        Ah, ok. Simpler than I thought.

                         

                        I had thought of storing used codes in a table and cross referencing, but I thought I'd need to do a loop script checking each new code against all used codes, which could get pretty inefficient. The technique of just checking via a relationship is excellent. I know there are other places I could use this as well. Very useful concept!

                         

                        Thanks!