Hi all,

I have been trying to utilise the following (see below) to auto format phone numbers for Australia.

Also a dumb question.... where do I attach the calculation... to the feild I want formatted and if so do I change it from a text field to a calculation field or do I use validate by calculation?

Thanks in advance

Milton.

[color:blue]//Restrict input to digits-only

Let ( ( num = Filter ( number ; "0123456789" ) ;

Phone =

/* Vary occurrence of spaces, depending if

the phone number features 10 digits... */

Case ( Left ( num; 1 ) = "0" or

Left ( num; 4 ) = "1800" or

Left ( num; 4 ) = "1300";

Left ( num ; 4 ) & " " & Middle ( num ; 5 ; 3 ) & " " & Middle ( num ; 8 ; 3 ) ;

//or 6 digits...

Length ( num ) = 6;

Left ( num ; 2 ) & " " & Middle ( num ; 3 ; 2 ) & " " & Middle ( num ; 5 ; 2 ) ;

// or the default format for 8-digit numbers

Left ( num ; 4 ) & " " & Middle ( num ; 5 ; 4 ) &

// if it exceeds 8 digits, assume it is an extension

If ( Length ( num ) > 8; " x" & Middle ( num ; 9 ; 99 ) ; "" ) ) ] ;

// Allow ## ## ## formatting for 6-digit numbers e.g. "13 13 13"

If ( Length ( Num ) < 8 and Left ( num ; 2) ≠ 13;

// if number is too short [less than 8 digits] display a message in red as error

TextColor ( "Too few digits" ; RGB ( 255 ; 0 ; 0 ) );

// Reset the default colour if the phone number is valid

TextColor ( phone ; RGB ( 0 ; 0 ; 0 ) ) ) )

You'd use this formula in the Phone text field, and set the Entry Option to an auto-enter calc, with Do Not Replace UNCHECKED.

Thanks BCooney,

The problem I have is as shown in the attached graphic

Milton

Let ( ( num = Filter ( number ; "0123456789" ) ;

Phone =

You have declared two variables in your Let() statement (num and phone) but you are not using the List brackets.

Thanks Laretta,

What should the correct syntax be?

Milton.

If I am correct on the start of the calculation (seeing that you are missing the starting list bracket), look at this:

//Restrict input to digits-only

Let ( [color:red]( num = Filter ( number ; "0123456789" ) ;

Phone =

/* Vary occurrence of spaces, depending if

the phone number features 10 digits... */

Case ( Left ( num; 1 ) = "0" or

Left ( num; 4 ) = "1800" or

Left ( num; 4 ) = "1300";

Left ( num ; 4 ) & " " & Middle ( num ; 5 ; 3 ) & " " & Middle ( num ; 8 ; 3 ) ;

//or 6 digits...

Length ( num ) = 6;

Left ( num ; 2 ) & " " & Middle ( num ; 3 ; 2 ) & " " & Middle ( num ; 5 ; 2 ) ;

// or the default format for 8-digit numbers

Left ( num ; 4 ) & " " & Middle ( num ; 5 ; 4 ) &

// if it exceeds 8 digits, assume it is an extension

If ( Length ( num ) > 8; " x" & Middle ( num ; 9 ; 99 ) ; "" ) ) [color:red]] ;

// Allow ## ## ## formatting for 6-digit numbers e.g. "13 13 13"

If ( Length ( Num ) < 8 and Left ( num ; 2) ≠ 13;

// if number is too short [less than 8 digits] display a message in red as error

TextColor ( "Too few digits" ; RGB ( 255 ; 0 ; 0 ) );

// Reset the default colour if the phone number is valid

TextColor ( phone ; RGB ( 0 ; 0 ; 0 ) ) ) )

If you change the first red parenthesis to the square bracket (signifying beginning of the List) or [color:red][ then you have a calculation which is logical and which works. The calculation portion before the second square bracket [color:red]] is all part of the Let() and the portion after is the calculation itself.

I do not know the type of data your phone field would hold (Australian) but if the field had: [color:blue]334428882 ... it would (using the corrected calculation) return [color:blue]3344 2888 x2.

Does that help?

LaRetta :wink2:

Thanks Laretta,

Works a treat.

Any way I can post my program. It is 5mb as a zip and the limit here is 1mb.

Would like you to have a look.

On another matter, is FMP7 compatible with vista 64 bit?

Any way I can post my program. It is 5mb as a zip and the limit here is 1mb.

[color:blue]Do File > Save a copy as > Clone (no records) and then zip it.

Would like you to have a look.

[color:blue]I have no idea why nor am I sure I'd have the time to have a look. If you have a specific issue with it, it should be another separate new thread, presenting your issue so that ALL here can assist you. I doubt posting your entire solution has anything to do with Calculation Engine (Define Fields) and Formatting Phone Numbers.

On another matter, is FMP7 compatible with vista 64 bit?

[color:blue]I don't know. I suggest searching Forums or posting the question as new thread.

LaRetta :wink2:

Here's a great link for you: Developer Search