Simple masks

[Larry Gordon]

Sorry to be greedy - I hope there isn't a limit on concurrent threads per member!

In hindsight I now wish to add a 'simple mask' to my telephone number fields. Is there any easy way to reformat the existing 5,000 records to the new mask? Thanks for your help, Larry Gordon

[Stan Mathews]

Re: Simple masks

What is the real issue? Why would you need to store a mask when you can create a calculated field to display the phone number using whatever mask you desire?

What is the state of the field contents currently? Is there a field rule mask in place, is it stored or just used for display?

Take a look at an update operation if you decide you really want to store a mask.

[Larry Gordon]

Re: Simple masks

Hi Stan
Currently there is no mask in place. The reason I was thinking of using a mask is that I want the ability to search for records via their telephone numbers. The field is character based but has no uniformity, i.e. the telephone numbers contain random spaces which might skew the search.
Kind regards
Larry Gordon

[DaveM]

Re: Simple masks

Larry,

does any of your numbers have a 1 in front(like 1-800-? do any have extensions(like -1235) at the end? are any of them 7 digits instead of the customary 10?? Is the field length 14 to accomodate the mask?

You can easily remove the spaces and odd stuff with remspecial in an update operation as stan spoke of. It will be a bit harder to remedy the other stuff.


.

[Larry Gordon]

Re: Simple masks

Hi Dave
No, they all start with a 0 i.e. 020-1234-5678 and none have any further extensions. The field is long enough to accommodate the mask. The Simple Mask works fine on newly created records; problem is my 'pre-mask' records remain with random spaces or no spaces etc
Kind regards
Larry Gordon

[DaveM]

Re: Simple masks

Always test before you do, but this should work in update operations and will do all records in table.

mask(trim(remspecial("020-123-4567")),"(   )|   -    ")
'= (020) 123-4567

tested in interactive window

dim nfld as c
nfld = mask(trim(remspecial("020-123-4567")),"(   )|   -    ")

?nfld
= "(020) 123-4567"

[CALocklin]

Re: Simple masks

The reason I was thinking of using a mask is that I want the ability to search for records via their telephone numbers.

This exact situation prompted me to do something different - that does not use masks - and my users seem to really like it.

In the CanWrite Field event, I reformat every phone number using my Phone_Dash() function.
A_field_value = Phone_dash( A_field_value )
This formats ANY number to look like this:
234-345-6789
or, if it is less than 10 digits:
345-6789

The user can enter the number any way they want and it will still end up the same. These are all the same as far as my system is concerned:
2343456789
(234) 345-6789
1-(234 345/6789

This is done with Remspecial() and then a few checks to remove any leading 1 and add dashes as needed.

Why do the users like this? Because I create Find boxes that allow them to enter the phone number any way they want and then use the same Phone_dash() function to reformat the number so it matches what is stored in the database. This means they can just type the numbers or they can cut and paste from somewhere else and the Find will still work even if the original format of the number is different.

If you use a mask, then users must learn to type the number they are looking for exactly the way it is stored in the database - which could be either with or without the mask characters depending on how you set it up.

The function can also be used in a global update to 'fix' existing records.

I'll add this function to the Code Archive with examples of how it's used.

[Larry Gordon]

Re: Simple masks

Many thanks for the fantastic suggestions - although it will of course take me all night to understand how to utilise them! The good news is I do understand where you are heading.
Kind regards
Larry Gordon