I am developing a database and just a beginner. I would like to know how I can set up a field that will put numbers in a phone number format (xxx)xxx-xxxx when entering the the data. I have filemaker 16.
In your Field Options window, click the "Specify" button next to "Calculated value" and enter this (below) as the calculation. I use something like this in one of my files. When the user has finished typing/editing and leaves the phone number field, if there are 10 digits, the calculation will fire and the number will reformat.
str = Filter ( phone ; "1234567890" ) ;
first = Left ( str ; 3) ;
second = Middle ( str ; 4 ; 3 ) ;
third = Right ( str ; 4 )
Length ( str ) = 10 ;
"(" & first & ")" & second & "-" & third ;
Department of Physical Therapy – Clinical Education
Smiddy Hall 335
953 Danby Rd. | Ithaca, NY 14850
firstname.lastname@example.org<mailto:email@example.com> | ithaca.edu
You're welcome! That's a simple 10-digit example, and may be all you need. You could edit the calculation further (or use some of the other validation options) to handle instances where a user might try to add a leading "1" or "+1", or an extension (like "x100"). Different ways to handle stuff like that.
Thank you so much. I appreciate your help. Can I ask you another question?
One of my field are usually current $ .00
How would do that so that when you add the numbers if come up in that format?
I am such a beginner and have no real computer programming background!
A few things come to mind:
1. formatting on the layout (or manually by user:
2. display of numbers in the field (by using the Inspector):
Specifying formats for fields containing numbers
3. some "auto-enter" (or calculation) that changes entered values from 'number' to 'currency' (text)
I think perhaps you want to look at the field in Layout Mode and how the Inspector displays the field contents (#2)
While the calc provided by firstname.lastname@example.org "worked", note that it will only fire if the correct number of digits (10) are entered by the user—that is governed by this: If ( Length ( str ) = 10.
If they happen to enter more or less than 10 digits—or they may enter letters or other characters the omission of which results in less than 10 digits—the result will be to leave whatever they typed unformatted, which will leave your database with dodgy data and the user may not even notice. You may need to extend the calculation so that it gives feedback to users in these cases.
I use a calc with similar logic, but designed to cater for erroneous data entry as well as recognise various different types of phone numbers (land lines, mobiles, info numbers, etc) and format each according to its type as well as return feedback to users in the event of errors. Its a bit long and specific to my area for posting in its entirety here. Suffice to say that instead of using If ( ) it uses Case ( ) which gives you an infinite number of options you can return.
If the field type is defined as `Number`, then you have several formatting options available in the Inspector, in Layout Mode. Click the last tab in the Inspector and scroll toward the bottom, where it says Data Formatting. You can set currency symbols, number of decimal points, hide 'zero' values, etc...
This is a perfect situation for a custom function. There are a few that format phone numbers on Brian Dunning's site:
FileMaker Pro Custom Functions
I like the FileMaker Custom Function:fnPhone(text) function because you can have the extension added to the phone number if needed.
My field calculated value then looks like:
fnPhone ( Supertrim ( TextFormatRemove ( Self ) ) )
I did stumple upon that
This is a free resource. Its for FMP 15 but still applies. Its the FileMaker Training Series. FileMaker Training Series: Advanced for FileMaker 15 - Now Available
Tutorials on how to make an app — FileMaker Custom App Academy
You may want to spend some time as these are excellent learning tools...
Thank you. Will look that up.
I do 2 things for phone number field (US)
First I use an Auto-Enter calc (uncheck Do not replace...) in the phone number fields
Let (@Numbers = TextFormatRemove (Filter( Self ; "0123456789" )) ;
Length(@Numbers) = 10 ;
Left(@Numbers ; 3) & "-" &
Middle (@Numbers; 4 ; 3) & "-" &
Right ( @Numbers; 4 ) ;
TextColor (Self ; RGB (255 ; 0 ; 0 ) )
This way the field turns red if they enter more or less than 10 digits. They can also just enter the digits and not worry about formatting.
Then an OnObjectExit script trigger on the phone field with this script, which can be used on any phone field, and easily transportable.
Set Variable [$Phone; Value:Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName )]
If [not (IsEmpty(GetField ($Phone )))]
If [Let ( @Numbers = Filter(GetField ( $Phone); "0123456789" ) ; Length (@Numbers) ≠ 10 )]
Show Custom Dialog [Title:"This Phone Number Field Can Only Contain 10 Digits"; Message: "This Phone Number Field can contain 10 digits"; Default Button:“OK”, Commit:“Yes”]
Exit Script [Result:False]
I check for empty so the user can delete the entire entry and move on.
Retrieving data ...