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.
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
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!
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.
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.