Controls

Re: Help and explaination of a format control on phone numbers?

rcl0404 — Mon, 04 Aug 2014 14:29:17 GMT

I need to be more specfic, as I've cleaned it up a bit, let's go from here:

I unconditionally translate ALL non-numbers to NULL, so (723)555-5566 becomes 7235555566 and 1+3456667788 becomes 13456667788 and so forth. So I can manipulate these fromcommon characters, I only need to account for international and local formats.

(for local) I am curious on how I would be able to make a rule to tell the field to put a hyphen after the first 3 numbers, then another after the first 6.

Then I would have a seperate rule for if the number has more than 10 digits using a format condition of >10 characters, (which would mean International), that would put a "+" after the first number then put a hyphen after the first 5 numbers, then another after the first 8.

Thanks

Re: Help and explaination of a format control on phone numbers?

JenniferLindsay — Mon, 04 Aug 2014 04:03:07 GMT

There is no single formula to translate all of these different formats. What you want is a separate action rule for each phone number format. Each rule will have a condition checking if the phone number is format X, and if it is then you will have a translate function to translate the phone number into your desired 10 numbers 2 hyphens format. However, this requires that you know all of the different possible formats your database contains. If you don't know all of the different formats, then the best you can do is put a validation rule on the field that will outline it with a dashed red line if it doesn't match the correct format.

Each formula is going to be a bit complex, so I'll explain one of them and hopefully you can figure out how to come up with the rest.

For the (123)456-7890 format:

First you want to compare the starting format with the desired format:

Starting: (\d\d\d)\d\d\d-\d\d\d\d
Desired: \d\d\d-\d\d\d-\d\d\d\d

Looking at these two, you see that the starting one can be transformed into the desired one by removing the left parenthesis completely and by converting the right parenthesis into a dash. Each of these can be accomplished by using the translate function:

Remove left parenthesis: translate(Field,"(","")
Turn right parenthesis into dash: translate(Field,")","-")

Now just combine the two translate functions. The final rule is below:

Condition: Field - matches pattern - (\d\d\d)\d\d\d-\d\d\d\d
Action: Field = translate(translate(Field,"(",""),")","-"))

Hope that helps!

Help and explaination of a format control on phone numbers?

rcl0404 — Thu, 31 Jul 2014 15:41:20 GMT

I've seen "copy and paste this" into the formula boxes to achieve different effect, but I'd like them explained a bit as well so I can change it if needed.

I recieve phone numbers in various formats from a database, I want them in the 123-456-7890 format. I recieve them in (123)456-7890 1234567890 and others, including international 1+1234567890.

I want all of these into a 123-456-7890 format, 10 numbers 2 hyphens. I am familiar with the translate and substring-after commands, not so much on combining them to achieve the desired effect.

please give me the formula, and break it down verbally to I can understand it.

Thanks