[Solved] Phone number format

Creating tables and queries
Post Reply
gosling
Posts: 10
Joined: Fri Sep 26, 2008 12:49 pm

[Solved] Phone number format

Post by gosling »

Good day all-

I've been trying to set a format for a field so that when you type in a US phone number (ie 5164234567)it will be displayed as (516)423-4567.

Have not had any success so far and would appreciate any suggestions.

Many thanks,

M. Robinson
Last edited by gosling on Fri Sep 26, 2008 5:11 pm, edited 1 time in total.
OOo 2.4.X on Ms Windows XP + ubuntu
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Phone number format

Post by Villeroy »

A field may store such "numbers" as you type them into the field. Since you'll never use them for calculation they are no numbers at all. If you store that "numbers" in a filed of integers a numeric value will be stored as such and you may use some special number format with braces and dashes in a form's input box. I'm not shure about the capabilities of "formatted fields" in forms, particulary if they are able to format strings of characters. Anyway, what I try to explain -- since you asked in the "Tables & Queries" forum -- the difference between strings and numbers may be important before you try to press the values into some scheme.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Phone number format

Post by Villeroy »

OK, this seems to be potentially difficult. I prepared a table with one column VARCHAR with max. lenght of 10 chars and another column of type BIGINT (able to take that many digits).
Then I made a form with 3 pairs of controls.
1. Plain unformatted text field and numeric field.
2. A pair of pattern fields
3. A pair of formatted fields
I have not the time to investigate how exactly these things work. But you may have some fun with the attached mini-database.

The online help has some information on edit masks. It's hard to find therefor I paste it here. I searched "literal mask" in the Writer section.
Help wrote: Edit mask
By specifying the character code in pattern fields, you can determine what the user can enter in the pattern field.
The length of the edit mask determines the number of the possible input positions. If the user enters characters that do not correspond to the edit mask, the input is rejected when the user leaves the field. You can enter the following characters to define the edit mask:
Character
Meaning
L
A text constant. This position cannot be edited. The character is displayed at the corresponding position of the Literal Mask.
a
The characters a-z and A-Z can be entered. Capital characters are not converted to lowercase characters.
A
The characters A-Z can be entered. If a lowercase letter is entered, it is automatically converted to a capital letter
c
The characters a-z, A-Z, and 0-9 can be entered. Capital characters are not converted to lowercase characters.
C
The characters A-Z and 0-9 can be entered. If a lowercase letter is entered, it is automatically converted to a capital letter
N
Only the characters 0-9 can be entered.
x
All printable characters can be entered.
X
All printable characters can be entered. If a lowercase letter is used, it is automatically converted to a capital letter.

For the literal mask "__.__.2000", for example, define the "NNLNNLLLLL" edit mask so that the user can only enter four digits when entering a date.

b]Strict format[/b]
You can have a format check with control fields that accept formatted contents (date, time, and so on). If the strict format function is activated (Yes), only the allowed characters are accepted. For example, in a date field, only numbers or date delimiters are accepted; all alphabet entries typed with your keyboard are ignored.
 Edit: 2012-02-11: Fixed pattern field of varchar field. Should have had strict format option 
Attachments
num_text_formatted_patterns.odb
comparing text and int in formatted fields and pattern fields
(11.63 KiB) Downloaded 1426 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gosling
Posts: 10
Joined: Fri Sep 26, 2008 12:49 pm

Re: Phone number format

Post by gosling »

Hi-

You've obviously been able to accomplish exactly what I need. How did you format the fields in the form view? What mask did you use?

Many, many thanks,

Mike R.
OOo 2.4.X on Ms Windows XP + ubuntu
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Phone number format

Post by Villeroy »

The pattern fields? Look at the properties in edit mode.
Right-click form>Edit...
Get toolbar "FormDesign", click the 5th button to get the "Forms Navigator".
Right-click the form "Standard", call "Properties"
Click through the form's elements watching the properties.

Code: Select all

LNNNLNNNLNNNN
(___)___-____
L - A text constant. This position cannot be edited. The character is displayed at the corresponding position of the Literal Mask.
N - Only the characters 0-9 can be entered.

The formatted fields support number formats. The two I used in the form are formatted as "("000")"0000"-"000

Be careful. When you edit some records and add your own records you may notice that some combinations of form controls and data types yield weird results.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gosling
Posts: 10
Joined: Fri Sep 26, 2008 12:49 pm

Re: Phone number format-SOLVED!

Post by gosling »

Many, many thanks to you.

Cheers,

Mike R.
OOo 2.4.X on Ms Windows XP + ubuntu
h00pak
Posts: 2
Joined: Tue Jan 21, 2014 10:41 pm

Re: [Solved] Phone number format

Post by h00pak »

Hi, I can't seem to get to the place where I can enter this pattern for my phone field. I right click on the form, edit, form navigator, right click, replace with pattern field, and then I can't find any way to enter the mask.

Please advise me as to what I'm doing wrong.
Thanks,
Robby
OpenOffice 4 on MacOS 10.9
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Phone number format

Post by Villeroy »

Navigator > Right-click pattern field > Properties ...
The "General" tab is about appearance.
The "Data" tab is about binding to the parent form's record set.
The "Event" tab is for macro programmers.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Elso
Posts: 6
Joined: Sun Jun 01, 2014 8:49 pm

Re:Phone number format pattern and cursor position

Post by Elso »

I am fairly new to Office base. Figuring things out as I go here (baby steps). This phone formatting was my first search and has been very useful, thank you all for the info and questions. Trying to figure out how to have the cursor, when tabbed to the phone field, land in the first point to begin entering the phone. At the moment, when entering the phone field, then entire pattern is highlighted (i.e.(___) ___-____ ) and I must hit the right arrow key before I can start entering the phone. Using the suggested pattern of "LNNNLLNNNLNNNN".

I thought maybe a simple Macro consisting of only a single right arrow key stroke would be the answer upon "When receiving focus" in the event area. I seem to be missing something here though. I could not create this simple macro in my form, so made in Cal (which functions there) but I still can't seem to call it when entering the phone field to position the cursor at the desired location.

I am sure I am missing something rather simple or stupid, but after a few hours trying to work this out on my own, I decided to just ask so I could move forward onto bigger challenges :crazy:

Thanxs for any direction offered

Elso
OpenOffice 4.0.1 Windows XP 2002 SP3
bozonius
Posts: 7
Joined: Thu Jul 30, 2015 11:13 pm

Re: [Solved] Phone number format

Post by bozonius »

I am having the same problem where, in order to enter the first digit of the phone number, I have to first right arrow once. After that, it works. But this is a nuisance. I searched the bug base, and I've just spent close to an hour trying to find a solution.

Any resolution to this? I'd be grateful if you have one.
LibreOffice 4.3.7.2 Mageia 4
bozonius
Posts: 7
Joined: Thu Jul 30, 2015 11:13 pm

Re: [Solved] Phone number format

Post by bozonius »

Why is this thread marked as SOLVED? I do not see anything after the last June 2014 post indicating this had been solved.

What was the solution then?
LibreOffice 4.3.7.2 Mageia 4
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Phone number format

Post by RoryOF »

The original poster marked it solved on 26 Sept 2008.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
bozonius
Posts: 7
Joined: Thu Jul 30, 2015 11:13 pm

Re: [Solved] Phone number format

Post by bozonius »

RoryOF wrote:The original poster marked it solved on 26 Sept 2008.
I see. It looks like this thread got hi-jacked, and I thought THAT was the topic. (I found the thread via google, not directly through the forum, so I got a page link to the post(s) I was searching for, not the thread title.)

Another argument for why not to hijack a thread I guess.

Anyway: Was this ever solved? I'm still having the issue. Thanks.
 Edit: Just to be clear, I mean was Elso's problem ever solved (getting the tab to move the mouse to first printable character position, not the problem on this thread)? I might re-post this on a new thread already... :roll: 
LibreOffice 4.3.7.2 Mageia 4
Henri391
Posts: 3
Joined: Wed Oct 18, 2017 7:34 am

Re: [Solved] Phone number format

Post by Henri391 »

???"-"???"-"????
worked for me when the field was set to CHAR
LibreOffice 5.x on Linux Mint 17x
bozonius
Posts: 7
Joined: Thu Jul 30, 2015 11:13 pm

Re: [Solved] Phone number format

Post by bozonius »

But will '?' permit only a digit?
 Edit:  To be honest, I forget why I was even asking this. It has been a long time since I was working on whatever project it was that led to this issue. If I come across it again, I'll try something like the pattern suggested (but maybe with 'N' instead of '?'). Thanks. 
LibreOffice 4.3.7.2 Mageia 4
Henri391
Posts: 3
Joined: Wed Oct 18, 2017 7:34 am

Re: [Solved] Phone number format

Post by Henri391 »

I should point out since the system didnt allow what I wanted to type... I am using Libre BASE on Linux
in this system N or n refers to day of week, Mon,

Testing it with a Non numeric entrys ... it did not work... only numerals
LibreOffice 5.x on Linux Mint 17x
bozonius
Posts: 7
Joined: Thu Jul 30, 2015 11:13 pm

Re: [Solved] Phone number format

Post by bozonius »

Not sure what you are saying. What is it, exactly, that you should point out -- I am not reading your meaning, sorry. "N" according to the docs means a numerical input character. Maybe we are not talking about the same input field formatting, perhaps?

But you are saying that "?" only allows numerals? Huh. I thought "?" meant any single character. See Villeroy's comment, above, for an example that is like my own. viewtopic.php?f=61&t=10132&sid=d8babb7b ... 599#p47460
LibreOffice 4.3.7.2 Mageia 4
Henri391
Posts: 3
Joined: Wed Oct 18, 2017 7:34 am

Re: [Solved] Phone number format

Post by Henri391 »

Well, having researched more (and more) finally figured it out why I was unable to use
formatting like everyone else. I was not using a "pattern field".
LibreOffice 5.x on Linux Mint 17x
Post Reply