1 2 3 Previous Next 31 Replies Latest reply on Jul 11, 2017 1:19 AM by the_engineeer

    How to prevent duplicates from scripted password generator?

    the_engineeer

      Hello all!

      I've recently started using Filemaker again, and I realize that much has happened since version 4... :-)

      Some memory loss on my part may also be to blame.

       

      However, I need a script that generates an unique 3-character "password" for every record in the database. My script as of now looks like this:

       

      If [ Length ( MyDatabase::Password ) = 3 ]

      Halt Script

      End If

      Clear [Select; MyDatabase::Password]

      Loop

      Set Field [ MyDatabase::Password; MyDatabase::Password & Middle ("abdefhimnrtABCDEFGHJKMNPQRSTUVWXYZ1234567890"; Int

      (Random * 44); 1)

      Exit Loop If [ Length ( MyDatabase::Password ) = 3 ]

      End Loop

       

      This script generates passwords just fine, but how do I add a function that checks that no duplicates are created?

      /Thanks for any help!

        • 1. Re: How to prevent duplicates from scripted password generator?
          coherentkris

          If you were just dropped off from FM 4 land you should realize that you are now in relational database land. Each record in a table needs a "password" (aka primary key). One way to easily generate a primary key value is by setting your primary key field to auto enter calculation, do not replace, and the calculation to Get ( UUID). You can also create primary key values by setting the primary key field to auto increment serial number. Enforcement of primary key constraints can be achieved by choosing to validate as unique and not empty. It is probably not a good idea to try and set primary key values in a script.

           

          Attached are examples of primary key definitions.

           

          if you mean something other than a primary key value for a record you'll have to explain in detail exactly what you are trying to achieve.

           

          In New FM land "Password" is almost means user authentication and security.

          • 2. Re: How to prevent duplicates from scripted password generator?
            the_engineeer

            Perhaps password was the wrong word to use.

            The field content is actually a unique code that I will use to make identification foot rings for my birds.

            (I breed parrots)

             

            So, this is not a "password" field really...

            And the code generation works exactly like I want, it's just the function for "check-for-duplicate-and-generate-a-new-code-if-found" that I haven't figured out how to implement.

            • 3. Re: How to prevent duplicates from scripted password generator?
              coherentkris

              if you set the field to validate as unique then

              - pseudo script -

              set error capture on

              Loop

              Set field

              Set variable ( $err ; Get Last Error)

              exit loop if $err = 0

              end loop

               

              You will have to test and tweak the field validation options to get this to work exactly how you want.

              • 4. Re: How to prevent duplicates from scripted password generator?
                TomHays

                You have a few options.

                 

                 

                1. Generate the 3-character codes in a specific sequence that will guarantee no duplicates.  This means not using Random.  To generate these in sequence I would treat them as a three digit number in base 44.
                2. Generate random 3-character codes and keep track of all previously generated codes.  If you are using these codes for records in a table, then you are already keeping track of them.  You then just need to check the generated code against the previous ones (via a scripted Find, SQL calc, or global storage field as a key to related records).  If the code exists, generate a new one and check it again.  Keep generating until you make a unique one.  Be careful about upper/lower case since FileMaker is case-insensitive when doing some text comparisons.

                 

                -Tom

                • 5. Re: How to prevent duplicates from scripted password generator?
                  TomHays

                  the_engineeer wrote:

                   

                  Loop

                  Set Field [ MyDatabase::Password; MyDatabase::Password & Middle ("abdefhimnrtABCDEFGHJKMNPQRSTUVWXYZ1234567890"; Int

                  (Random * 44); 1)

                  Exit Loop If [ Length ( MyDatabase::Password ) = 3 ]

                  End Loop

                   

                  Note that this can be simplified to be a single Set Field[] without the Loop.

                   

                  Set Field [ MyDatabase::Password;

                  Let([

                  srcChars = "abdefhimnrtABCDEFGHJKMNPQRSTUVWXYZ1234567890"

                  ];

                  Middle (srcChars; Int(Random * 44); 1) &

                  Middle (srcChars; Int(Random * 44); 1) &

                  Middle (srcChars; Int(Random * 44); 1)

                  )

                   

                  Not only is this easier to debug and performs faster, having one assignment to MyDatabase::Password may be useful in particular if you are using field-level validation.

                   

                  -Tom

                  • 6. Re: How to prevent duplicates from scripted password generator?
                    TomHays

                    I concur that field-level validation for unique is a good approach if you want to generate these codes randomly.

                     

                    The uniqueness test be handled efficiently by internal FileMaker routines, and the approach is multi-user friendly.  Producing your own uniqueness validation may fail when multiple users are creating codes at the same time.

                     

                    -Tom

                    • 7. Re: How to prevent duplicates from scripted password generator?
                      TomHays

                      coherentkris wrote:

                       

                      You will have to test and tweak the field validation options to get this to work exactly how you want.

                       

                      You will also need to se the Default language to Unicode on the field's Storage options because the 3-letter codes are case-sensitive.

                      If you leave the Default language as English, the code "aaa" will be validated as matching "AAA" and will be rejected.  If you use Unicode, FileMaker will allow you to enter "aaa" and "AAA" into the field in separate records and still satisfy "Unique value" validation.

                       

                      -Tom

                      • 8. Re: How to prevent duplicates from scripted password generator?
                        the_engineeer

                        OK, so this would be the final script if I understood correctly:

                         

                        If [ Length ( MyDatabase::Code ) = 3 ]

                        Halt Script

                        End If

                        Clear [Select; MyDatabase::Code]

                        Loop

                          Set Error Capture [On]

                          Set Field [MyDatabase::Code ;

                          Let ( [ srcChars = "abdefhimnrtABCDEFGHJKLMNPQRSTUVWXYZ1234567890" ] ;

                          Middle (srcChars ; Int ( Random * 45 ) ; 1 ) &

                          Middle (srcChars ; Int ( Random * 45 ) ; 1 ) &

                          Middle (srcChars ; Int ( Random * 45 ) ; 1 ) )]

                          Set Variable [$err; Value: Get ( LastError )]

                          Exit Loop If [$err = 0]

                        End Loop

                         

                        (with the field "Code" validated as unique and in unicode.)

                        The first "If" thingy is just a "what-if" just in case I want to type in a code manually and don't want the script to erase it.

                        The script seems to work fine anyway!

                        • 9. Re: How to prevent duplicates from scripted password generator?
                          coherentkris

                          move the set error capture step to just outside the loop.

                          Set Error Capture [On]

                          If [ Length ( MyDatabase::Code ) = 3 ]

                               // does nothing if 3

                          Else

                               //clear not needed because the set field does that

                               Loop

                                     Set Field [MyDatabase::Code ;

                                      Let ( [ srcChars = "abdefhimnrtABCDEFGHJKLMNPQRSTUVWXYZ1234567890" ] ;

                                      Middle (srcChars ; Int ( Random * 45 ) ; 1 ) &

                                      Middle (srcChars ; Int ( Random * 45 ) ; 1 ) &

                                      Middle (srcChars ; Int ( Random * 45 ) ; 1 ) )]

                                      Set Variable [$err; Value: Get ( LastError )]

                                      Exit Loop If [$err = 0]

                               End Loop

                          End If

                          • 11. Re: How to prevent duplicates from scripted password generator?
                            the_engineeer

                            Awesome!

                             

                            Thanks so much for your help!

                            I will probably need more help later on though, in "version 2" of my database I want to add a QR code in the printouts :-)

                            • 12. Re: How to prevent duplicates from scripted password generator?
                              rgordon

                              I could be wrong on this but I but I don't think a Set Field is enough for FileMaker to return a validation error.  The Set field step needs to be followed by a commit step to return an error.

                              1 of 1 people found this helpful
                              • 13. Re: How to prevent duplicates from scripted password generator?
                                coherentkris

                                rgordon is correct. good catch.

                                the validation settings will not throw an error until a commit is triggered.

                                This has the unfortunate side effect of producing a validation failure dialog box that, if i remember correctly, can not be suppressed.

                                using validation settings may not actually be the best solution here.

                                • 14. Re: How to prevent duplicates from scripted password generator?
                                  the_engineeer

                                  Hmm, that's unfortunate.

                                  Any ideas how I can modify the script for it to work...?

                                  1 2 3 Previous Next