1 2 Previous Next 24 Replies Latest reply on Feb 6, 2013 8:20 AM by coachie

# New to FM

Recently purchased FM 12 and can't seem to find solution to formatting 7 digit phone numbers. Would like to have 7 digits entered into a field that would re-format to XXX-XXXX. I am sure this this is easy but I have spent several hours with no progresss.

Thank you

• ###### 1. Re: New to FM

Let ( [

F1 = Table_Name::Phone_Field ;

F2 = Filter ( F1 ; "0123456789" ) ;

F3 = Length ( F2 ) ;

F4 = If ( F2 = 7 ; Left ( F2 ; 3 ) & "-" & Right ( F2 ; 4 ) ; F1 )

] ;

F4

)

• ###### 2. Re: New to FM

I am really new to FM.  This goes at:  validated function options?   anything else?  TY for your assistance

• ###### 3. Re: New to FM

No.... in the field options, click on the Auto Enter tab, not the validations.  Go down to the Auto Enter option for calculating the result and put this calculation in there.  Of course subsitute your real field name.  Then make sure to uncheck the box that says "Do not replace existing value of field".  Then you should be good to go!

• ###### 4. Re: New to FM

Hi Taylor

I've always been taught to apply meaningfull names to all variables used in calculations as it helps avoid errors ...

F4 = If ( F3 = 7 ; Left ( F2 ; 3 ) & "-" & Right ( F2 ; 4 ) ; F1 )

Best wishes - Alan Stirling, London UK.

• ###### 5. Re: New to FM

Now I am really upside down.....insert your joke here..........The field name in my db is Work Phone 2....where do I add/subtitute 'Work Phone 2' in the formula....which goes Auto Enter Tab > Data> Formula...........have you stopped laghing yet????  TY

• ###### 6. Re: New to FM

The following should work for you:

Let ( [

F1 = Work Phone 2 ;

F2 = Filter ( F1 ; "0123456789" ) ;

F3 = Length ( F2 ) ;

F4 = If ( F2 = 7 ; Left ( F2 ; 3 ) & "-" & Right ( F2 ; 4 ) ; F1 )

] ;

F4

)

If the field "Work Phone 2" was not in the same table, you would have to preceed it with the table occurrence name such as "Contacts::Work Phone 2".  I am assuming it is in the same table and therefore doesn't need the preceeding table name.

As commented above, if you don't like the F1...F4 variable names, you can give them longer and more descriptive names.  I usually do short 2 digit ones so my formulas are short as long as all variables are visible and nearby the formula.  If this was going to be a long formula (e.g, a page long) and I was reusing these variables, I would use more descriptive names like instead of F3, I would use "Length of Phone" or something like that.

Cheers!

• ###### 7. Re: New to FM

Hi

Check this site

http://www.briandunning.com

It helps me

• ###### 8. Re: New to FM

Thanks for your help.  ....but the joke continues...I have placed that formula in Work Phone 2 fieldAuto Enter Tab> Data and that is not working for me....so I must be putting this formulas in the wrong area....still laughing????

• ###### 9. Re: New to FM

wwww.briandunning.com is probably the best source for free custom functions.

I also like a lot of cool things you can do with the free ScriptMaster plugin.  But I've been looking at the Monkey Bread MBS plugin for a while and may have to start using it soon even though it isnt' free.  It sure has a lot of functions that would be useful.

• ###### 10. Re: New to FM

TY...I need all the help I can get....I have recently purchased FM12 and have been commissioned (read: madated) to design and implement a customer service db for our company.....what is it they say????...what doesn't kill you makes your stronger......

• ###### 11. Re: New to FM

I don't know if you have much training time, but the FileMaker Training Series is the official training from FileMaker for those who want to get certified.  You don't have to take the test, but you can purchase or download the FTS documents for your own learning.  I think the list price is \$99, but if you join TechNet as a paid member, I think you get it free and I saw before Christmas they had some special going for like \$60.  But I don't know if they're still doing that.

FYI, www.Lynda.com and www.vtc.com are good online training tools too.

And you can aways look for a FileMaker developer in your area to help out.

http://developer.filemaker.com/search/

• ###### 12. Re: New to FM

Coachie,

what Taylor is telling you will work. All you need to do is place that formula in the 'calculated value' dialogue box under the Auto-Enter tab.

Double click the field, click the 'specify' button next to where it says 'Calculated value'  and paste the formula into the box.

Make sure, as Taylor said, to use your field name.

Let ( [

F1 = Work Phone 2 ;

F2 = Filter ( F1 ; "0123456789" ) ;

F3 = Length ( F2 ) ;

F4 = If ( F2 = 7 ; Left ( F2 ; 3 ) & "-" & Right ( F2 ; 4 ) ; F1 )

] ;

F4

)

If you upload your database, I'm happy to take a look at it and tell you where your trouble is.

• ###### 13. Re: New to FM

First lets go over field naming do's and don'ts.

1. Don't use separate numbers in a field name. FMP doesn't know that "Work Phone 2" is a complete name or two fields and a number.

2. Don't use spaces in a field name. See number one above.

3. Use only alpha-numerics and underscore. If you ever want to make this web published or use the ExecuteSQL() command fields names like "Id #" won't work.

4. New Be aware of SQL reserved words, for example: "current_user" is a reserved word in SQL and is difficult to as a field name in a query.

SQL Reserved Words Checker:

http://www.petefreitag.com/tools/sql_reserved_words_checker/

Checking Word: current_user

SQL Server 2000               Reserved

DB2                    Reserved

ODBC                    Reserved

SQL Server Future Keywords     Not Reserved

MySQL 5.0               Reserved

PostgreSQL 8               Reserved

Oracle 10g               Not Reserved

ISO/ANSI,SQL99               Not Reserved

Microsoft also has a listing.

http://msdn.microsoft.com/en-us/library/aa238507(v=SQL.80).aspx

Which includes "current_user".

I'd suggest this becomes a lot easier to use and understand by making field name changes first.

work_phone_2

=> The best solution I know of is use a Custom Function (CF) designed to handle multiple country phone formats. (See link in response from "abrunet").

Alternative to formatting within the field.

If you make the field validation require numeric only and a max of 7 characters with a tooltip telling them to how to enter the phone number you can eliminate most data entry errors of US phone number. Then make a calculation with result as text to show a formatted phone number.

However all this presumes you are only going to get US phone numbers and do not want to include the leading "1-" we all have to use now.

The opinions expressed in this email are my own and do not reflect those of my employer or anyone else.

Regards,

Ch0c0halic, FileMaker 12 Certified Developer

• ###### 14. Re: New to FM

Lots of good help..TY...I have lots of changing to do!

1 2 Previous Next