Telephone Number Formatting

You probably have a preference for how you would like a telephone number to appear. And the appearance will probably be different if the number is a mobile (cell) or a free call number. In Australia, there are a range of different number types. My preference is for formatting like this:

Standard phone numbers - (08) 8981 1144 or 8981 1144
Mobile numbers - 0402 839 829
Freecall numbers - 1300 797 201 or 13 13 13 or 1800 123 456

Phone numbers in the United States might be formatted (408) 987-7000. And in the UK, they are formatted like this (01923) 242926.

The problem here is having users remember how to format different numbers for different countries and uses, and then having them do it consistently. What we need is a database that can take a number and automatically format it as it should appear. This takes the onus off the user to do it properly and results in more consistently entered data.

So how is it done? Over the years, many developers have proposed many quite complex calculations to use as either a separate calculated field or, more recently, as an auto-entered calculation that replaces the text. The business logic of the required format is built into the calculation.

What I will show you here is a different approach that uses a custom function to format the phone number. That custom function can then be used directly in an expression, or indirectly through another custom function.

So let's get straight to the custom function. Custom functions can be created and edited using FileMaker Pro Advanced. Once created in a file, they can be used by anyone who has access to the FileMaker Pro calculation engine.

This custom function is called phoneMask and requires two arguments - number and format. The number is the phone number that needs to be formatted. The format is the phone mask to be used, supplied as a text string and using the # character for a digit placeholder. Some examples of phone masks you might use are:

(08) 8981 1144 would use (##) #### ####
13 13 13 would use ## ## ##
1300-797-201 would use ####-###-###
(408) 987-7000 would use (###) ###-####
23. 456.1212 would use ##. ###.####

The idea is that you can add any other required characters to the mask. The custom function will use the mask and replace any # characters with digits.

So you want to see the custom function?
It is called phoneMask (number; format). Here it is:

Let ([
digits = Filter ( number ; "1234567890" );
left_digit = Left(digits; 1);
left_format = Left(format; 1)
];

If ( left_format = "#" and not IsEmpty(left_digit); left_digit; left_format )
&
If ( Length (format) > 1;
phoneMask (
If ( left_format = "#"; Right ( digits; Length (digits) - 1) ; digits ) ;
Right ( format; Length (format) - 1 )
)
)

) // end Let

The first thing the function does it to filter the number supplied down to just digits (0-9) stripping out all other characters.

Then, using this statement:

If ( left_format = "#" and not IsEmpty(left_digit); left_digit; left_format )

It looks at the first character of the supplied format (mask). If it is a digit placeholder (#) and there is a digit available, it returns the first digit of the number supplied. Otherwise, it returns the first character of the supplied format.

Then, if there are more format characters to process, it calls the phoneMask function again using a reduced form of the number and the format mask. The act of a function calling itself is called recursion and is a powerful feature of custom functions in FileMaker Pro. In this case, it calls itself until it runs out of format mask characters.

An intended side-effect of this recursive process of replacing the # placeholders with digits is that if the function runs out of digits, the placeholders are used. For example, if 088981114 was entered using a format mask of (##) #### ####, it would return (08) 8981 114#. This is feedback to the user that not enough digits were entered.

So now we have a custom function, how do we use it? We could use it in an auto-entered calculated replace. That is a field option that we could set for the telephone field. We make sure that we uncheck the Do not replace existing value (if any) checkbox so that any telephone number entered is updated with the correctly formatted version. The calculation we would use could be:

phoneMask ( telephone ; "(##) #### ####" )



This will enforce the same formatting on every telephone number entered. To be more flexible, we could build in conditional selection of a format mask based on data in the record (such as country) or within the telephone number entered (such as number of digits or starting digits). An example of this is:

Let ([
number = Filter ( telephone ; "1234567890" );
digit_count = Length ( number );
format = Case (
country = "USA"; "(###) ###-####";
country = "Netherlands"; "##. ###. ####";
country = "Aust" and digit_count=6; "## ## ##" ;
country = "Aust" and digit_count=8; "#### ####" ;
country = "Aust" and Left(number;2)="04"; "#### ### ###" ;
country = "Aust" and Left(number;4)="1800"; "#### ### ###" ;
country = "Aust" and Left(number;4)="1300"; "#### ### ###" ;
country = "Aust" ; "(##) #### ####" ;
"(##) #### ####" // this is the default format mask
);
format_digit_count = PatternCount ( format ; "#" );
red = RGB ( 200 ; 0 ; 0 )
];
phoneMask ( number ; format )
&
If ( format_digit_count <>

---

Data Entry Cues-2

In this second part, we will create a text cue for a field where data entry is optional. We would like to tell the user more about what they should be entering in the field. However, we only want the cues to appear for new records. The cues should disappear after the record is first committed.

As with the previous part, we create and format text in Layout mode, and move the text on top of the field. We will do this for the company and phone number fields. As a visual cue, we will use a different colour for these optional field cues.
Next we apply conditional formatting. Select the text object and choose the Format > Conditional... command (or right-click and select Conditional Formatting... from the contextual menu). Add a condition and specify the calculation:

not ( IsEmpty(Contact Management::Company)

and Get(RecordOpenState) = 1 )

The field specified will be the field you are creating the text cue for.



What we are saying here is that the formatting of this text will change when the condition is true – when the field is not empty and record does not have an open state of 1.

So what is "an open state of 1"? A record has an open state of 1 when it is a new record that hasn't been committed. A closed or committed record has an open state of 0; a modified record that hasn't been committed has an open state of 2.

Click OK. Set the Format for the condition you have created. Set the text colour to white. Click the More Formatting... button and set the size to 1 Point. Click OK twice to close and save the conditional formatting.

Now test it! In Browse mode, create a new record and notice that the label is visible over the field. Tab into the field and enter some data. When you tab out, the label will be gone.

Now test it with another new record. This time do not enter any text into the field. When you commit the record, the text still disappears. This is the behaviour we wanted - the text cues should only be visible when entering data in a new record.

Here is a quick video of how it should work for each of the tests above:

It is also possible to have the text cues re-appear for optional entry fields when the record is being edited but only if those fields are still empty. The cues have to be triggered so the solution involves use of the Evaluate function. I will leave the rest to you If you can't work it out, email me a request to post it here.

---

---

Data Entry Cues-1

Users will often benefit from cues about data entry. These cues can indicate whether data is required or optional, and they can provide further information about what is required or allowed.
One way of providing these cues is with tooltips. Tooltips must be created and edited with FileMaker Pro Advanced. All users of the database can see the tooltips. Tooltips can be attached to any layout object – a field, a button, a field label. A tooltip pops up as a yellow note when the user hovers their mouse over the object.

Developer Quiz Question
Are tooltips visible in runtime solutions and databases published to the web with Instant Web Publishing? (Check the answer at the end of this article)

The downsides of tooltips are:
the user has to hover (not click) the mouse in the correct area
the user has to know there is a tooltip to view
the tooltip disappears after a set period
the user can only see one tooltip at a time

An alternative to tooltips is conditionally formatting text objects. Conditional formatting was introduced in FileMaker Pro 9. It allows the developer to set conditions on the appearance of text in an object such as a field, button or text block.

In this first part, we will create a text cue for a field where data is required. We have set the field validation to a strict not empty, and we want the user to know that they have to enter data in the field.

In Layout mode, create text and format it as required. Move the text on top of the field.

Next we apply conditional formatting. Select the text object and choose the Format > Conditional... command (or right-click and select Conditional Formatting... from the contextual menu). Add a condition and specify the calculation:

not IsEmpty ( Contact Management::Last Name )

The field specified will be the field you are creating the text cue for. What we are saying here is that the formatting of this text will change when the condition is true – when the last name field is not empty.

Click OK. Set the Format for the condition you have created. Set the text colour to white. Click the More Formatting... button and set the size to 1 Point. Click OK twice to close and save the conditional formatting.

Now test it! In Browse mode, create a new record and notice that the label is visible over the field. Tab into the field and enter some data. When you tab out, the label will be gone.


You can leverage what you have done for other required fields. Copy the existing label. Modify the text. Update the conditional formatting to refer to the new field.

Click here for the video

In the second part of this article, we will look at text cues for fields where data entry is optional.

---

---

Allow User Abort

Managing ‘User Abort’ in Scripts
The Allow User Abort script step is used to prevent users from stopping a script. One reason this may be necessary is when a script takes the user to an unfamiliar location. If the user aborts the script, they may not be able to get back to where they started.
By default, user abort is on. If a user clicks Cancel on a print dialog during a script, FileMaker Pro will inform the user that print has been cancelled and give them the option of continuing or canceling the script.

The problem is that many users don’t know what a script is (or that they were running one!), don’t know if they should continue, and therefore click Cancel. This stops the script dead and often leaves them in a foreign location in the database.

One solution to this problem is to insert a script step Allow User Abort and set the option to Off. With this step before the Print step that displays a Print dialog, FileMaker Pro does not ask the question when the user clicks Cancel - the script always continues. There are many other places where the user’s ability to cancel the script is stopped.

But as a developer you should also be aware that it prevents every user (even you) from stopping a script (unless you are running the Script Debugger in FileMaker Pro Advanced). This can be disastrous if an untested script enters an infinite loop or if you want to stop a process that you know is going to take a while.

So what this usually means is that you will put an Allow User Abort step in a script where you need it but set the option to ‘On’ while you are testing. When you are satisfied that there are no infinite loops or other serious bugs, you reset the option to ‘Off’.

Do you see the problem here? At some stage, you need to change all the Allow User Abort script steps in all your scripts to ‘On’. How many scripts is that? And how do you know you got them all? And what do you do when you need to turn them all off again for further testing?

Here is a simple solution - use a subscript instead of a script step. Confused? I will explain.

1. Create a one step script called Allow User Abort On. The one step is Allow User Abort with the option set to ‘On’.

2. In any script where you need to control User Abort, ‘call’ the script with the Perform Script step.

3. Test what you need to test.

4. To turn User Abort Off in your solution, open the script, change the name to Allow User Abort Off and reset the script step option to ‘Off’.

5. To turn User Abort On again in your solution, open the script, change the name to Allow User Abort On and reset the script step option to ‘On’.
6. Rinse and repeat as needed.

The point about always updating the script name to reflect the state of the User Abort step is that it makes sense when you read scripts that call it:

Now, more advanced users may be thinking - “Can we make that subscript more dynamic? I keep forgetting to change the name or the option”.

Well I’m glad you’re thinking! Of course we can. Try this script instead - it uses the last word in the script name to determine if User Abort is on or off. All you need to do is to rename the script to alternate between states.

So what we have now is a method to turn user abort on or off in our database in just one location. Master control!