Wish: phone number formatting

tkilshaw

A way to format phone numbers is needed by us soon. And I think is probably needed by other too. Some of our critical reports will be based on analysing customer catchment areas based on area codes and exchanges. For that to work, all phone numbers must be formatted the same way. Depending on users for this is not an option. Using the existing TEXT functions may be possible, but certainly not easy.

I know this will need to be different depending on the country, but in general, US and Canadian phone numbers have the following structure:

area code 3
exchange 3
number 4

a total of 10 digits. Common formats include:

(250) 764-7490
(250) 764 7490
250-764-7490

I imagine a function that takes a format string, a default area code, and the string to be formatted.

It strips out all non-numeric characters, checks the length, adds in the default area code if needed, then formats and returns the string.

Example:

PHONE_FORMAT_US( '(###) ###-####', SystemSettings.AreaCode, Contact.HomeNumber )

thanks,

Terry

aware_support2

Terry,

Thanks for the suggestion. The formatting support for textual attributes is on our TODO list. It should allow the use of regular expressions so you could just enter the desired expression in the attribute format and the system will do the rest - like it does for numeric or date attributes.

In the meantime you could add a Java function to do the required validation/cleanup/normalization and an object rule to use the function on the attribute.

autonomy2

Hello Support.

My project has a telephone attribute, which contains the country code, area code and telephone number. For example - +61 (02) 9999 9999.

Could you please advise on how I could extract the telephone number (9999 9999) from this attribute, using the current functions in Aware?

....and if it is simple...I'm going to kick myself.

aware_support2

You can use function REPLACE to clean up unwanted characters and leave the meaningful ones. This can be done using regular expressions (documented at the end of the User Guide). For example, characters '+', '-', '(' and ')' can be removed like this:

If MATCHES(Person.Phone, '[+-()]') = 'Yes' Then Person.Phone = REPLACE(Person.Phone, '[+-()]', '')

autonomy2

Hello Support.

Just wanting to check in regards to the following post:

I have set this business object up to test it (using REPLACE_PATTERN instead of REPLACE) and can't get this to work.

The '(' and ')' result in errors according to the Server Output:

-Execution Exception after starting system process message Internal error. Illegal character range near index 3

[+-()]

Also, have tried just looking for the '+' and the '-' characters using

If MATCHES(Person.Phone, '[+-]') = 'Yes' Then ....

with input as "+-61 (02) 9999 9999"

resulting in the condition being evaluated to false (according to the Server Output).

Any suggestions on what the problem might be?

aware_support2

Here are some suggestions on using regular expressions:

Precede any characters that may be used as special characters in regular expressions with a . For example, instead of [+-()] use [+-()]
Append + to find one or more occurrence of the specified character (or a class of characters listed between [ and ]), for example: [+-()]+
Use \D+ to find one or more non-digit characters in the text

More details on using regular expressions can be found in Appendix E of the Aware IM User Guide.

greg

I use REPLACE_PATTERN function to do a clean up. For example, unwanted characters from a credit card number are removed with this rule:

Customer.CrCardNumber=REPLACE_PATTERN(Customer.CreditCardNumber,'[ ,-]','')

it removes spaces, commas and dashes. I write the cleaned up number to another attribute used for payment processing so the customer does not get confused.

leopdurr

How can I use this to Replace all Digit on a Store credit card except the Last 5 Digits? Is there any post or seggestion on this?

I use REPLACE_PATTERN function to do a clean up. For example, unwanted characters from a credit card number are removed with this rule:

Customer.CrCardNumber=REPLACE_PATTERN(Customer.CreditCardNumber,'[ ,-]','')

it removes spaces, commas and dashes. I write the cleaned up number to another attribute used for payment processing so the customer does not get confused.

pbrad

Hi,
Here is the rule from one of my apps where I am doing the same thing. I have two attributes; a credit card number attribute and a masked version of it so that I can choose which attribute I display depending on the user and circumstances.

If Client.CreditCardNumber1 WAS CHANGED Then
Client.CC1Masked=CHARS_FROM_LEFT(Client.CreditCardNumber1,4)+'-*-*-'+CHARS_FROM_RIGHT(Client.CreditCardNumber1,4)

This should get you going down the right path.
Cheers,
Pete

leopdurr

Hi
can you explain this part of the rule for me please?

Client.CC1Masked=CHARS_FROM_LEFT(Client.CreditCardNumber1,4)+'-*-*-'+CHARS_FROM_RIGHT(Client.CreditCardNumber1,4)

thanks leopold.

pbrad

If Client.CreditCardNumber = 1234 4567 7654 4321

then the value of Client.CC1Masked is built by taking the first four characters and the last four characters separated by -*-- to make 1234--*-4321.

If you just need the last five characters then:

If Client.CreditCardNumber1 WAS CHANGED Then
Client.CC1Masked='***********'+CHARS_FROM_RIGHT(Client.CreditCardNumber1,5)

Pete

leopdurr

Thanks

Perfect.

cheers
Leopold