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 )


          ] ;





          • 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 )


                         ] ;







                    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. 



                    • 7. Re: New to FM



                      Check this site



                      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. 


                              • 12. Re: New to FM



                                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 )


                                     ] ;






                                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:



                                  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.


                                  Which includes "current_user".


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





                                  => 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.


                                  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