Phone Number calculation

[happyshotdogs]

Ok, I found this calculation for validating a phone number when it get entered for desired formatting--(330) 555-1234.  This works.

 

The problem is that it inserts the error message into the field (as it should according to the script...) when incorrect number of numbers (say that again) is entered--012, 012345678901, etc., so I would like to have an error dialog box pop up instead...how do I go about doing that?

 

AS IS NOW:

 

 

Case(Length(CustomerPHONE) = 7;Left(CustomerPHONE;3) & "-" & Right(CustomerPHONE;4);

 

(Length(CustomerPHONE) = 10); "(" & Left(CustomerPHONE;3) & ") " & Middle(CustomerPHONE; 4; 3) & "-" & Right(CustomerPHONE;4);

 

"Must enter 10-digits")

 

Maybe this is really elementary, but not for me at this point

 

Thanks!

 

PS  Don't be surprised as my number of posts go up exponentially in the coming days!  Also, if anyone has GOOD youtube tutorials/training links, please advise...I think I have the uncanny power of finding the LAME ones

Order Copy_20130106.fmp12.zip

[Rick Whitelaw]

You can get rid of the ten digit restriction. Country codes are problematic since they can be 2 or 3 digits. However they are usually proceeded by a + sign. You could have a third case statement that checks for the + sign and use "self" as the result. This simply allows the user to enter the number. It won't trap an error though.

[Lee Smith]

You might want to have a Look at this Thread

 

There are several resources available besides youtube. 

http://www.databasepros.com/resources.html

http://www.filemakermagazine.com

 

Check out the sponsors of this site, found on the Index page.

, 

[Chris Cain]

We use a custom function, which takes a mask (like "(###) ###-####") so that we can account for different phone formats in different countries.

 

I see that while I was playing, Lee responded with an excellent link. Don't let me interrupt you on the way to checking that out.

 

Meanwhile, just for fun, I created the following auto-enter calc. It only works for the format you described, but if you're not concerned about international, that may be enough. Also, if the user enters a short number, they'll see # for each digit they're missing, and the text will turn red. If they put in more than 10, the leftover will be assumed as an extension. 

 

We also commonly include a checkbox for "do not format" in case the user wants to enter a number we haven't accounted for, or have the auto-entry just return self if the first character is "+". If you have just a few foreign numbers, but not enough to be worth creating a countries table, this is a down and dirty workaround.

 

Anyhow, here's the calc I just put together. Hope it helps: 

 

Let ( [ 
num = Self 
; num = Filter ( num ; "1234567890" ) 
; len = Length ( num ) 
] ; 

Case ( 
	IsEmpty ( num ) 
	; ""

	; TextColor ( 
		"("
		& Left ( num ; 3 ) 
		& Case ( 
			len < 3
			; Substitute ( 
				10 ^ ( 3 - len ) 
				; [ 1 ; "" ] 
				; [ 0 ; "#" ] 
			)
		)
		& ") " 
		& Case ( 
			len > 3
			; Middle ( num ; 4 ; 3 ) 
		)
		& Case ( 
			len < 6
			; Substitute ( 
				10 ^ ( 6 - len ) 
				; [ 1 ; "" ] 
				; [ 0 ; "#" ] 
			)
		)
		& "-" 
		& Case ( 
			len > 6
			; Middle ( num ; 7 ; 4 ) 
		)
		& Case ( 
			len < 10
			; Substitute ( 
				10 ^ ( 10 - len ) 
				; [ 1 ; "" ] 
				; [ 0 ; "#" ] 
			)
		) 
		& Case ( 
			len > 10
			; " x"
			& Right ( num ; len - 10 ) 
		)

		; Case ( 
			len < 10 
			; RGB ( 255 ; 0 ; 0 ) 
		)
	)

)

)

[happyshotdogs]

I appreciate all the replies...this has been the best forum for help so far!

 

Thanks Chris, Lee, Rick!

 

I guess I could have mentioned I wasn't concerned about country codes, but good to know how to handle them anyways

Chris,

Works like a charm!