Phone Number data disappears

[mbrasseau]

I am creating a CRM database in Base. I used the wizard to initially set this up. I successfully modified the Zip Code field to take either 5 or 9 digits and format them as “00000-0000”. But I cannot seem to do the same for the Phone Number. I attempted to format as “000-000-0000” and it just displays weird behavior. First it would change every phone number to a large negative number (the same number for every phone number). So I went in and formatted it to be identical to the Zip Code field, all except for the actual number formatting. Now the phone numbers just disappear. I enter the correct phone number, save, close, and when I re-open it, the phone number is gone.

Changes that I made to the Phone Number field: changed the field from txt to fmt; changed the formatting in the property control. Changed the minimum value to 000-000-0000 and the maximum to 999-999-9999 (although this is not how the ZipCode field is formatted, I do not know how to get the equivalent), removed the default value.

Despite removing the default value, it still defaults as 000-000-0000.

I have already read this post but I don’t understand the difference between that, and what is clearly working for the ZipCode field, so I don’t know if that is the option that I need, or why… or why/how they are different.

Please advise.

[Villeroy]

https://forum.openoffice.org/en/forum/download/file.php?id=19462 (open the form)

A phone “number” is not a number. It should be stored as text. The pattern in my example is enforced by a pattern field.

[Villeroy]

In my repository of databases I found another solution for integer fields with a comparison to text fields. It should display the numeric value in a formatted field just as well as the textual approach.
Number format code (000")“000”-"0000 does the trick. Missing digits are filled up with leading zeroes.
Column type INTEGER would be sufficient for positive numbers up to 2147483647 (32 bit) which does not cover the whole range of 10 decimal digits. For 10-digit numbers bigger than that, you need column type BIGINT (64 bit).
http://forum.openoffice.org/en/forum/viewtopic.php?t=10132&p=47451#p47451

[mbrasseau]

Thanks. I started another mini-table and form, to test. It is holding the phone numbers and formatting them correctly. I am trying to figure out how to open them both side-by-side to compare and see what the actual differences are.

In the test table and form, I set the field to Numbers in the table and formatted it at the bottom to -000-000-1234. In the form I did not change anything.

Now that I am looking at it, I do like the formatting with the parentheses better, but now I’m afraid to change anything because I have the test table/form working properly.

[Villeroy]

A table is a container for raw data. Base provides a most simple view on raw data. Formatting does not make much sense here.
Forms and reports make up the user interface of a database. Forms are for manual input while showing calculated results and related data from other tables. Reports are for nicely layouted reading or printing.
The “form wizard” is the least useful thing in Base. It is highly misleading because it covers only a tiny fraction of the possibilities.
Right-click form icon>“Edit…” opens the form document for editing the form itself, not the data.
If your controls show one record at a time in single form controls (as in my 2nd example), Ctrl+Click selects a control without its associated label.
The context menu of a control offers options like “Control properties …” and “Replace with”> [other controls]. Get the control properties window. For your integer numbers the wizard might have created a formatted control. The type of control is shown in the title of the properties window. The binding to a table column is shown on the “Data” tab. You can set the number format in that window. If it happens to be a “numeric control” or anything else, right-click>replace it with a formatted control.

The form in my first example includes only the textual phone numbers in a pattern field but the pattern field is wrapped in a table control. A table control is a container for other controls showing multiple records simultaniously. While in edit mode, the properties dialog shows the properties of a selected table control. Clicking on the column header shows the properties of the embedded pattern field which constitutes the entire column.

Best tutorial ™: [Tutorial] Forms in OpenOffice.org Base
This is a development tool. Development takes time and effort in order to get an easy-peasy product for daily use.
With the text field being selected, you can choose “Replace with…” from the context menu and then “Pattern Field”. The help file for pattern fields tells you how to define one. If you open my form for editing, the properties window reveals how I did the 2 tricks with the integer and its formatted field and with the text and its pattern field.

[mbrasseau]

I figured it out!!! So in my problem table I had both the phone number and zip code set to Integer. In my new Test table the wizard set them to Numeric. Evidently these two settings function very differently, which is obvious to me now.

I changed the phone number table setting to Numeric, and formatted it at the bottom as I did before. I removed the extra formatting that I had done in the form (min/max setting). The “formatting” setting in that field seems to have reset to General, so I just left it as it is.

Both tables and forms are holding and formatting the numbers correctly.

@Villeroy, I think I will make another test table and also try what you are describing. I like the parentheses formatting better. I was just really wanting to keep it as a number, rather than VARCHAR in the event that we would ever be calling people directly from the database. Thank you for helping my understanding.

[Villeroy]

Yep. A table column stores either some kind of text or some kind of number and the form lets you edit numbers by means of currency controls, numeric controls or freely formattable controls (very much like spreadsheet cells). The values from a table’s text columns are displayed in text boxes or pattern fields. Text boxes can be set up for multi-line text and with scrollbars.

Table columns may also store date, time and time stamp columns and the respective form controls provide little pop-up calendars and spin buttons.

The report wizard is by far more useful than the form wizard because it covers all the important features.