5 Replies Latest reply on May 16, 2013 7:04 AM by Atkins

    Specifying EXACT field format



      Specifying EXACT field format



           I have a field in my database for the MAC Address of a piece of equipment.  MAC Addresses can be formatted many different ways, but the format we have decided on is XX.XX.XX.XX.XX.XX where the X's represent capital letters, A - F, or numbers, 0 - 9.

           If a user were to manually enter a MAC Address, they may input it as so:  XX-XX-XX-XX-XX-XX, or possibly:  xx-xx-xx-xx-xx-xx

           It is also possible, for someone with no experience, that they may mistake a 0 for an O, and try to use that, but O's do not exist in MAC Addresses.

           We also use a bar code scanner when possible, and the common read-out for that is XXXXXXXXXXXX with no seperators.

      The Question:  I'm curious if there's a way to limit the field to only accept the correct format and characters, or to run a conversion script on entry that will validate what the user has entered.

      •           If they use lowercase letters, it will convert them to uppercase.
      •           If they don't use seperators it will add them in.
      •           If they use hyphens it will convert them to periods.
      •           If they use a character that is not valid it will prompt them to re-enter the address.

           I realize that this will require some complex scripting to cover all the bases, but that's why I'm at the forum.  This is a feature that you would commonly see on other entry forms, but I don't know if it's something FileMaker necessarily supports.  Thanks for any input and assistance!

        • 1. Re: Specifying EXACT field format

               Try this auto-enter calculation:

               Filter ( Upper ( Substitute ( Self ; "-" ; "." ) ) ; "0123456789ABCDEFGHIJKLMNPQRSTUVWXYZ." )

               And clear the "do not replace existing values" check box.

               It's hard to see the period, but that's the digits 0...9, the period and the uppercase letters "A".."Z" except "O" in the quoted text.

               Then you can set a validation rule specifying that the number of characters has to be 17

               And note that there is no scripting done at all here.

          • 2. Re: Specifying EXACT field format

                 This will work even when inserted by scanner, iow w/o inserting any separator :


                 Let ([
                 v = Filter ( Upper ( Self ) ; "1234567890ABCDEF" ) ;
                 sep = "."
                 Case ( Length ( v ) = 12 ; 
                 Replace ( Replace ( Replace ( Replace ( Replace ( v ; 3 ; 0 ; sep ) ;  6 ; 0 ; sep ) ; 9 ; 0 ; sep ) ; 12 ; 0 ; sep ) ; 15 ; 0 ; sep )
                 The user can insert even no separator, but the number of chars must be 12
            • 3. Re: Specifying EXACT field format

                   Both answers were very helpful so I'm not going to choose a 'Best Answer'.

                   I wound up using a hybrid of these scripts so that if I extracted just the letters and numbers I wanted, I would have 12 characters.  I then convert them to Uppercase and add the separator of ".".  If there are letters that are not valid it prompts the user to fix the problem, and if there are not enough characters it prompts the user to fix the problem.  Other than that it will accept any combination of separators and lowercase/uppercase letters and spit out the right format.

                   Thanks guys...I never would've figured that one out on my own, but now it makes a lot of sense!

              • 4. Re: Specifying EXACT field format

                     Would you mind sharing your hybrid code?  I'm in the exact same boat as you were, except we've chosen the "XX:XX:XX:XX:XX:XX" format.

                • 5. Re: Specifying EXACT field format

                       Hi Evan, glad to help!

                       I've packaged all this into one script that either validates or refuses to validate the MAC Address entered, and sets a variable to 'Yes' or 'No' accordingly.  I run this script from another script and then check that variable to see if it's a yes or a no, then that script proceeds accordingly.  You could set it up however you want, but that was easy enough for me.  It helps keep this part of the script self-contained.

                       First I have an "If" statement for length.  This weeds out any entry that doesn't have the correct number of characters, either entered as a single string of characters (12) or using separators (17).  If they put in separators in most of the spots but miss one they'll come up with 16 characters and it'll tell them it's not a valid entry, they must try again.  The system can only be so smart...


                            If [ Length ( Temp::g_MAC Address ) ≠ 12 and Length ( Temp::g_MAC Address ) ≠ 17 ]

                       Next I make sure that what is entered is the right number of characters taking into account only valid characters.  The first step allows any characters.  In this case it is all numbers and letters 'A' through 'F'.  This script (in another "If" statement) counts only "valid" characters and makes sure you have the right number.


                            If [ Length ( Filter ( Upper ( Temp::g_MAC Address ) ; "0123456789ABCDEF" ) ) = 12 ]

                       Lastly, if the user input passes both of these tests it has qualified as a valid entry, so I address the formatting:


                            Let ( [ v = Filter ( Upper ( Temp::g_MAC Address ) ; "1234567890ABCDEF" ) ; sep = "." ] ;
                            Case ( Length ( v ) = 12 ; 
                            Replace ( Replace ( Replace ( Replace ( Replace ( v ; 3 ; 0 ; sep ) ;  6 ; 0 ; sep ) ; 9 ; 0 ; sep ) ; 12 ; 0 ; sep ) ; 15 ; 0 ; sep ) ) )

                       The script above is in an 'Insert Calculated Result' script step that places the result in the same field that the user entered it in.  You could change that to any field you want.  In case the script doesn't make sense, here's what it's doing:

                  •           First, we filter the number again and set it as variable 'v', and set another variable 'sep' to a period (.).  You would want to use a colon here.  The 'Upper' command changes all the letters to uppercase.
                  •           Then we make sure that what is filtered has a length of 12.  Adjust accordingly if needed.  That does not include separators, just the numbers/letters.
                  •           Lastly we use a bunch of "Replace" commands to add the separators back in.  In each of these instances, 'v' is our string of characters and the next number indicates where in line (from the left) our separators go.  'sep' just tells it to put in a period (.).  If you look at the MAC Address format I'm using (xx.xx.xx.xx.xx.xx), the periods come in spots 3, 6, 9, 12, and 15.  That's how those numbers were derived.

                       Hopefully that all makes sense!  Let me know how it goes for you.