Zip code correction

[Maxwell Morlay]

The post office has sent a list of corrected addresses from a tab delimited file I sent them. They put the zip and zip+4 in the same field (now 9 digits long). Can I design a field or script that will take the last 4 digits out of the first field and put them in the second field?

Maxwell Morlay

[Keith M. Davie]

>field (now 9 digits long)

This field is referred to below as "zip", and assumed to contain data which looks like 123451234

One possibility is two calculation fields.

c1_zip = Case(Lenght(zip)=9, Left(zip,5))

and

c2_zip = Case(Lenght(zip) = 9, Right(zip,4))

[Maxwell Morlay]

Sorry, Keith, I couldn't make it work. Is a third field needed to receive the data? I created 2 new fields FIX1 and FIX2 using your formulas. What am I missing. Thanks for that quick reply.

Max

[Keith M. Davie]

Well there will be three fields. The one containing the 9 digit zipcode (zip in my example) and the two calc fields (FIX1 and FIX2 in your example). I checked this out before I posted and had no trouble. The field zip is a text field, indexed, and the two calc fields are Result is Text. I generally set calc fields to Unstored.

[Keith M. Davie]

Just to clairfy further, if the field zip contains the data (e.g.) 123451234

the field which you have defined as

Case(Lenght(zip) = 9, Left(zip,5))

should display (e.g.) 12345

and the field which you have defined as

Case(Lenght(zip) = 9, Right(zip,4))

should display (e.g.) 1234

[Maxwell Morlay]

got it. I was putting in "Zip =" as in your example. Also your length is misspelled.

Thanks, Maxwell

[Keith M. Davie]

Also your length is misspelled.

Oops. Fortunately it is a "point and click" in the create calculation box, so spelling is not so much a problem.

Glad you got it working. You should see how I spell filed.

[Keith M. Davie]

Also, FYI, c1_zip in the name of a field, as is c2_zip. And it occurred to me that you were misreading that.

In this instance c1 and c2 for calculation1 and calculation2; and zip since the calculations are of that filed (field).

The underscore joins the c1 and zip together so they should be seen by a viewer (developer) as a name and, (now this is really the important part) if the db should ever be included in a browser solution, there will be no problem with a space in the field name. Because at a minimum in a browser solution a browser will convert a space to %20 in a url, and this can play havoc in a FM db trying to utilize that url.

Because I develop browser solutions, I do not use spaces in db names, layout names, field names etc. Because html is not case sensitive, I also usually avoid pressing on the shift key when writing names.

Also you will find than many developers also designate globals with a starting g as in g_zip

[Rigsby]

Keith

[Maxwell Morlay]

Thanks very much to the Pros. Your help is appreciated.

Maxwell Morlay