How to store phone numbers (and postal codes)

[mluka]

Hi, all.

Still in the design phase of my project.

In our neck of the woods, we prefer to format phone numbers, for human consumption, in this way: (999) 999-9999.

Postal codes: A9A 9A9.

QUESTION: Is there a consensus as to what the "best practice" is with regards to storing these data in the database? Should they be stored WITH the formatting (validated at input time) or should they be stored WITHOUT any formatting (and edited every time they have to be displayed/printed)?

Thanks for your input!

[John Mark Osborne]

I store phone numbers and postal codes with formatting but make sure the formatting is consistent with an auto-enter calculation.

[Genx]

To be perfectly honest it doesn't really matter if you store numbers with or without formatting (though i store with formatting). It really depends on whether you need the raw data or not. To filemaker a phone number is just a string, it doesn't really care what you do with it.

And further, should you ever need the raw unformatted data again you can always strip the formatting away should you need to, e.g. for the phone number using GetAsNumber("(999) 999-9999") would return "9999999999" anyway so if you had need of the raw data its not hard to get.

~Genx

[John Mark Osborne]

Actually, that formula will return -9999999999. Better to use the Filter function like this:

Filter(Phone; "0123456789")

[comment]

Very interesting - and completely undocumented! Thanks for pointing this out.

[John Mark Osborne]

Apparently, the parenthesis is interprated as a negative sign. I flunked accounting but I believe accountants enter negative numbers surrounded by parentheses.

Edited June 15, 2006 by Guest

[comment]

Yes, it looks like a feature for importing financial data. Funnily enough, Filemaker itself offers to format negative numbers in 6 different ways, but it seems only 2 of those are recognized by the GetAsNumber() function.

[Genx]

Hmm, there you go, oops

[mluka]

Yes, I'd noticed that when I tried a phone number with an area code surrounded by brackets.

Yet, FM's documentation is unequivocal: "Returns only the numbers in text, as data type number, for use with formulas involving numbers or numeric functions. The GetAsNumber function drops all non-numeric characters from text."

It does not say anything about the sign being affected by the characters that get stripped away.

And then it goes on with an example that strips a trailing zero: "GetAsNumber("$1,254.50") returns 1254.5."

Maybe zero is not a number...

And then... the period gets kept... (Some people actually use a period as a separator in phone numbers, as in 514.555.1212)!

Goodnight all!

[Genx]

... Well then we wouldn't use getasnumber() for that, we might use substitute(field ; "." ; "") or better yet filter as John mentions above.

[comment]

Stripping leading and trailing zeros and keeping ONE period is actually quite correct.

Leading and trailing zeros are redundant in a number.

Losing the period would lose the decimal point (I wonder if the behaviour is different on systems with decimal comma, for example).

[bdavis007]

In accounting a number enclosed in () is considered either a debit or credit depending on the disposition of the journal. You would never enter the number as a negative number (using a "-").