Zero Prefix

[Genx]

My users can be bright sometimes... but they also have very annoying habbits. One of them for example is putting a zero in front of single digit numbers.

That is, i have a postal address "street number" text field set up. It allows for entries such as 1A, 214/3 etc etc. But if a user enters 03 for example (as they do even when i've told them not to), searching for 3 obviously doesn't work.

I need either a field validation calc or an auto enter calc.

I'm thinking of using something like the following:

If(

Substitute(TextStyleAdd(StreetNumber ; UpperCase) ;

["A" ; "" ] ;

["B" ; "" ] ;

["C" ; "" ] ;

["D" ; "" ] ;

["E" ; "" ] ;

["F" ; "" ] ;

["G" ; "" ] ;

["H" ; "" ] ;

["I" ; "" ] ;

["J" ; "" ] ;

["K" ; "" ] ;

["L" ; "" ] ;

["M" ; "" ] ;

["N" ; "" ] ;

["O" ; "" ] ;

["P" ; "" ] ;

["Q" ; "" ] ;

["R" ; "" ] ;

["S" ; "" ] ;

["T" ; "" ] ;

["U" ; "" ] ;

["V" ; "" ] ;

["W" ; "" ] ;

["X" ; "" ] ;

["Y" ; "" ] ;

["Z" ; "" ] ;

["/" ; "" ] )

<>

GetAsNumber(StreetNumber) ;

0 ; 1)

Anyone see any flawed logic here?

Cheers,

~Genx

[comment]

Yes. First, you're mixing style with contents. TextStyleAdd ( "a" ; UpperCase ) does NOT return upper-case 'A'; it returns lower-case 'a', FORMATTED to DISPLAY as upper case. IOW:

Exact ( TextStyleAdd ( "a" ; UpperCase ) ; "a" )

returns true. To truly change "a" into "A", use the Upper() function. Of course, you could use simply Filter ( text ; "0123456789" ) to get only the numeric characters in text.

But the real problem is that GetAsNumber ( "5th Avenue, 08" ) returns 508. So you will probably need some kind of recursion here to check each number/word individually.

[Genx]

But... Its only for the number, i.e. the street name has a different field.

But as for the Functions, it never really occured to me that TextStyleAdd was a style function (oops!) or that i could use filter.

Anyway, Cheers comment, you've saved me from mistakes so many times now!

~Genx

[LaRetta]

Its only for the number, i.e. the street name has a different field.

Let me get this right ... you ask your Users to put part of the street address (the number parts) in one field and the textual portion in another? What happens when an address mixes them (as most do)? I must not be understanding your process. :confused:

[Genx]

On all mailouts, reports etc. i simply reference both fields, whether as merge or in a calc... Plus this allows for drop down selection of prior entered street names during data entry, finds etc. which comes in very handy... especially when grown people can't spell .

Also makes searching and data entry easier as far as im concerned because it is more visually split.

I thought everyone did it this way...

But hey, my opinion here..

[John Mark Osborne]

If I've read your post correctly, it sounds like you just want to remove any leading zeros? Why don't you just use the following in an auto-enter calculation:

Case(

Left(StreetNumber; 1) = "0"; // that's a zero an not a letter

Right(StreetNumber; Length(StreetNumber) - 1);

StreetNumber)

[Genx]

... you know what, good question... , cheers

[LaRetta]

Interesting. In 30 years of wadding in data systems, I have never split the street address in this way (and I've worked with some large ones). I am open to the idea however. A leading zero in the address field has simply never come up for me. I wonder if, since your Users are entering the first part of the street in another field which LOOKS like a number, they attempt to treat it as a number; think it should BE a number (visually split) and enter it that way - thus perpetuating your problem with it?

Leading zeros in an address isn't something I've ever had to strip OUT of data either. I doubt it would be more convenient for data entry ... it just goes against the natural inclination of a person to enter a whole street as one entity. If it brings up prior-entered addresses, might they be spreading incorrect spellings as well?

How do you handle PO Boxes, foreign addresses (which are listed thus: Berkingham 124 Westchester Mir)? I use a database to hold streets which is tied to the zip/country and prefills. I use a Value List based upon that field (for searching). I would consider splitting my addresses - I'm not opposed to identifying a problem I may have been blind to for all these years but I'm unsure if this would qualify for me. Thanks for bringing it up, Genx!

LaRetta :wink2:

[Genx]

Okay well, my current database is for a company that does mass marketing.

They need 2 sets of addresses:

1) Property Address - Formatted According to local standards i.e. street number, street name, suburb, state --> i only prefil post code or add if the suburb state combo doesnt exist. The street name value list works off this field for the simple reason that a property would always be local and people can't live in a PO box (or so i have made the presumption).

2) Postal Address - This tends to be local aswell around 3/5ths of the time so i attach the street name value list to this for options. A P.O. Box i simply recommend entering P.O.in the "number" field and the rest in the street name (as this doesn't occur very often) - this is required because prior to mail-outs etc. it checks for valid (nonblank) postal address. Though if this was something that occured often, a tick box could always be added for PO box entry's.

3) International formats haven't been an issue yet to be honest seeing as the company only does local marketing (i.e. within the country).

Anyway, to me this just seemed the logical way to split the data especially for the purpose of sorting by street name when reporting because this allows the company to market streets at a time.

Anyway, not that any other way is illogical..

I don't really see the harm in having the user enter two seperate peices of data (mine have been doing it from the start and it seems to work for them).

Anyway, if none of this makes sense - i had no sleep last night because of accounting so dont be to hard on me :.

~Genx

[John Mark Osborne]

Interesting. In 30 years of wadding in data systems, I have never split the street address in this way (and I've worked with some large ones).

I agree wholeheartedly!

[Genx]

That it's a bad idea or that you haven't seen it. It's not good to be afraid of things just because you haven't seen them before .

[LaRetta]

It's not good to be afraid of things just because you haven't seen them before

Nothing was said of the sort. 1) I am afraid of nothing except myself and 2) I adore seeing things I've never seen before. I am always open to change. But NOT having this need in 30 years (and 90% has been in this field) should tell YOU something.

UPDATE: I'm not saying it's a bad idea, Genx, and if that's what you're waiting for me to say, you'll have a long wait. But the experience of others is VALUABLE input in your decisions which should add weight in making the BEST decisions for you. That's all ...

Edited May 15, 2006 by Guest
Added update

[Genx]

I was actually waiting for someone to say it was a bad idea (you missed my in your quote). While there is not necessarily a particular NEED for it, this particular method of data split definitley improves user efficiency and data consistancy (just like splitting an address in to suburb, postcode, state --> you could have those all in the same field, there is no NEED to split them) - in the end isn't this one of our biggest aims as developers?.. or um, young developer like young indiana jones in my case (god i hated those movies).. However, i understand that in the end it is the circumstance that defines the technique.

In this circumstance it definitley seems to work for me and I understand that in other's it might not suit.

Finally, the reason i went with what i went with was because prior to building what i have built, i worked with a database made for a similar purpose as a user, the street address was split in the same manner --> i also did some research on 2 other databases also for similar purposes, same thing, hence my adoption. It never even occured to me to combine the two fields.

But, I haven't looked back ... Yet.

Oh well, my original problem is solved so yay!

Thanks guys,

~Genx

Edited May 15, 2006 by Guest
example added

[LaRetta]

Then you should go for it. But just because it's been done before and just because someone has done it, STILL doesn't mean it's the best way. And yet again it MIGHT BE! Always question the needs of each solution and each process.

Perpetuating a process ... Even if something has been done before, you can still get yourself into trouble. Example: My mamma cut the ends off her ham every Christmas. I asked her why. She said it was because HER mamma cut the ends off. So I asked grandma. Same reason, because HER mamma always did. I asked great-grandma and she said, "Yes, I always cut the ends off my ham because my pan was too small."

I question everything and everyone. It is ALWAYS good to question. And know that I've filed this tid-bid in the back of my mind for future as well (to pull into my decisions) and that is why I thanked you. :wink2:

[Ender]

Ya, those ham ends are good eats!

[Genx]

:P We discussed this very issue in managment yesterday even though i fell asleep, thats not the point and even though it was regarding apes and banana's rather than ham's, i love the analogy / metaphor, whatever you want to call it.

Though the only reason i mentioned i saw it was because you and john said you hadn't.

~Genx

[LaRetta]

By the way, if from your example if it's a PO Box, you said you had them enter the box number in the number field and just PO in the street field.

But when you concatenate the street back together, you'd end up with (for PO Box 536)

536 PO Box

I will bet your Users have a cheat-sheet next to the computer to keep straight what you want where. Overkill can be a design-flaw as well. I can understand how splitting might be useful in your case since you are producing data-sets by Street name but you might want to address your concatenation calc to swich the order for PO Boxes. :wink2:

[comment]

Ah, so that was you?

[Genx]

Well, it doesnt actually say "number".

As for comment.. lol!

[LaRetta]

And so true - ideas are like ham ends, Mike ... discarded ideas could have taken us to the moon in the 1600's if people would have questioned 'ole NORM more. And of course, this wasn't a true story ... it is a parable. But it struck me and stuck... :wink2:

[LaRetta]

Thank you Michael! I always wondered where the true saying came from!! :wink2:

[Genx]

Ah, but if we question the norm all the time, when will we ever have time to move forward? Especially as the norm expands towards infinite horizons . The norm cannot ALWAYS be questioned, but if we don't question it we can't move forward!

Quite a conundrum we are faced with then yes?

Norm norm norm norm norm, norm norm norm, norm!

~Genx

[Oldfogey]

people can't live in a PO box (or so i have made the presumption).

That's a pretty rash assumption. Have you looked at Sydney's real estate prices?

[Genx]

Hahahahahahahahaha

[comment]

Ah, so that's why the pan needs to be so small...

[Søren Dyhr]

Well beyond the joy and giggle, should it be said that there sometimes is a point in what Genx does when separating. At least here in Denmark and northern Germany, where we use a different notation

H. C. Andersens Boulevard 4 2 th

H. C. Andersens Boulevard 4 st th

H. C. Andersens Boulevard 4 st tv

H. C. Andersens Boulevard 41 1 tv

H. C. Andersens Boulevard 44 st tv

...which is the sortorder filemaker establish when kept in one field, but if the postman should drop a leaflet in those letterslots in that direction would he curse us for asking. There are a few issues.

1) A house of multi storages have here letter slots on each flat/appartments door and not an array of letterboxes on the lowest floor, and the direction is the following in 95% of times:

st tv -> st th -> 1 tv -> 1 th -> 2 tv etc.

2) Even numbers goes upward on righthandside while odd number belongs to the other, and since the road in this case was a boulevard is crossing the street usually thru a stretch of park'ish stretch between the lanes, so the postman should then pull his bike or trolley thru this wilderness often littered with dogs "businesscards" - the answers would definatly be "NO WAY"

This means that different sortorders needs to be established, so the postman can avoid going up and down the same stairs, and the direction should be upward for even numbered houses, and downward for odd numbered houses, to prevent him/her from pregressing towards the trafics flow.

3) Some subscription salesmen living on commisions, are keen on signing say lifeinsurances to people already burried, and tries to disguise this fraud by putting penthouse appartements on houses where they doesn't exist ...by having such knowledge put into validations does the scams have to go elsewhere.

I have no clear recollection of my actions 30 years ago, but have been exposed to systems the last 20 years or so making exactly the distinction Genx suggests and find elevated beyond suspicion.

So the conclution must be if dealing with logistics isn't 30 years SQL'ish conventions fitting the bill ...all the time!

--sd

[LaRetta]

Well said.

Every country (and business) must have various issues to deal with. Again, fit the solution to the purpose. And now I have a broader perspective on the subject. :wink2:

[Genx]

You do love that little sly looking wink dont you LaRetta? And as I said, the technique must fit the situation - so we're all in one big happy family of agreement . Yay, i do enjoy agreement!

~Genx

[Mike D.]

Just throwing in my 2 cents - I normally split the address into separate fields like Genx did. I built a solution for a fund raiser for the local rowing club and in order to divide/conquer the work, we had to split up the sales areas by streets. The best way to do that was have the separate fields for the street number and street name.

Mike

Edited May 16, 2006 by Guest

[Genx]

Don't know why i'm digging this up...

But, one more advantage is reporting, you can report by street should you have the need, and sorting by street becomes a lot easier as well.

[LaRetta]

I agree but have two cavats: 1) Users will hate you and 2) How do you handle all the inconsistent addresses? Don't you get Users with major confusion about where to put what? I see no TRUE consistency of entry when addresses are so varied - at least in the US, such as:

127 West 45th Street, Apt 104 1/2

North Bennington Circle, 45 E.

N 44th Dover

... and so on. Not every address conforms to 3428 Lincoln Avenue. There are some strange addresses in the US (and in the world) - addresses are NOT black and white. I complain about our postal service but in truth, I wouldn't want the job of normalizing it. I suppose you would need directions for the data-entry people. And Lord help the Developer having to parse each piece into a separate field. It would still take a lot of manual (human) intervention. Either way, mistakes will happen; either during a parse or because of misinterpretion of the 'rules' by a User.

I would rather consider a search - find all Bennington street. Those found records, when sorted, would then produce a fairly consistent list because most people on the same street would list their address fairly the same. I think breaking an address into pieces would be overkill for 80% of the businesses out there. Just my personal opinion ...

LaRetta

Edited March 19, 2007 by Guest

[Genx]

Our addresses are largely consistent in Australia... and so is the usage within the software. As per users hating me, haven't had any real complaints.

Mr Henry Williams

APT 17, FLOOR 15 22 FRANGA ROAD ST

MELBOURNE VIC 3977

Or:

165 Franga Road

Or:

Unit 2/4 Cranbourne Street

Edited March 20, 2007 by Guest

[Ender]

I can't remember what this thread was supposed to be about, but I'll throw in my thoughts about address formatting anyway.

If the formatting of the address is critical, then I favor using separate fields for each part of the address. In my system, this came down to:

Street No

Street Dir. Prefix

Street Name

Street Type

Street Dir. Suffix

Unit#

City

State

Zip

I used to have the Street No field defined as a number type to help with validation and sorting, but found some dunderheads went and made half streets, like "49th 1/2 France Ave S". So rather than assume the post office knows that .5 = 1/2, we switched the Street No field to text.

Having the address in all these parts allows us to use value lists for some of the part abbreviations and auto-enter values based on lookup tables to help populate the City, State, and Zip. It's easy enough to combine them for a Full Address.

[Genx]

I can't remember what this thread was supposed to be about

Lol, the initial question was answered at about post 3, then someone brought up the question as to why the address was being split accross multiple fields...

Just in terms of the benefits, that i see:

1) Data Entry in most cases is More Rapid

2) Especially in your case, the fields are strictly defined so address formatting is going to be consistent.

3) It's easier to manipulate information using small defined strings than to try to extract information from one long miscellaneous string.

4) Sorting and grouping of data becomes simple.

Negatives:

1) Might confuse the user if not explained.

Just FYI, to my knowledge, there have been no "Return To Senders" received back as a result of incorrect addresses.