"case" function peters out with large calculation

[John Fowler]

Well, the USPS has decided that all their Priority Mail is now zone rated. Whereas it used to be the same low price to ship to any US state or territory, there are now 8 different ship zones, each with their own price.

Well, I've entered all these zones into a spreadsheet--it covers every zip code in the US--and I put together a little "Case" function so that each zip code will get the proper USPS Shipping Zone assigned to it. It looks something like this:

Case((zip91900 and zip92199), 1, (zip90000 and zip93099), 2, (zip86300 and zip86499) or (zip88900 and zip89199) or (zip93100 and zip93599) or (zip93600 and zip93899), 3, etc, etc...

Well, every single one of these cases produces the proper result, except the one for and zone 7, which appears to be the largest one. For some reason, every zip code that should be zone 7 shows up as zone 8. It's all very odd. The full string itself works just fine, but as soon as the long string/case gets entered, everything gets wacky. The best I can figure is that each case itself can't be more than a certain number of characters.

Any ideas?

John

[LiveOak]

I think you are still faced with a 64K character limit for your calculation. Unless you are really in love with flat file architecture, I would make each zip code a record in another file.

I'm not sure what your calculation is really doing. What is "zip93099"? Is this one field? Is the "" some operator that isn't translating?

-bd

[djgogi]

How about join file Zip-Zone instead of calculation?!?

Dj

[Kundinger]

Hello John,

I also agree with "LiveOak" and "djgogi". You should move your ZipZones to a 'related' file. I recommend this for a couple of reasons:

a. your main file won't have huge calcs in each record

b. the USPS in their 'infinite wisdom' will probably change the Zone tables in the future, and this allows you to make changes to your Zone file and not have to change your calc fields

I had to make some changes to my clients' systems to generate the Zone Code for their mailings.

I went to the USPS.com site and downloaded the approriate table. I then used Word & Excel to 'clean' and convert their data to an ASCII file which I then imported into a ZipZones file. I then created a 'relation' with a 'lookup' to 'capture' the correct Zone. The 'lookup' allows for changes to affect new records only, no old records are recalculated (as when using a calculation).

Before implementing these changes, I created two sample files that I used for testing. I can e-mail these to you if you would like to see how I implemented this. Let me know...

Good Luck!

Bob Kundinger

[danjacoby]

A seemingly unrelated question...

Why are you using Priority Mail in the first place? It costs more and does nothing extra -- correction, you do get a nifty envelope.

Priority Mail is delivered at the same "speed" as first-class mail. There is no guarantee of delivery, and no way to track it (unless you pay even more). It's a waste of money, and a complete rip-off! I have been yelling about this for years, and NBC news just did a piece on this.

NEVER use Priority Mail!!!

[Kundinger]

Alright Dan... You go get 'um...

I've been arguing this point with some of my clients for a long time. The USPS should refocus their efforts on First Class and Bulk mail only. Let the 'big boys' (UPS & FedEx) handle packages and 'fast' delivery. UPS & FedEx are much more 'tuned-in' on how to utilize technology to remain competitive. USPS with their government 'mentality' try to implement new systems that take a 'nightmare' or two just to conceptulize how to implement their changes. They seem to get away with this because they really don't have to compete.

I guess... this must be what they call "job security" for me...

Anyway... sorry to vent! I know this was not the 'Forum' for this...

Bob Kundinger

[LiveOak]

Priority mail did have its uses. The standard packages, no charge for pickup (weekly minimum for UPS), and flat rates were useful. The new zoned approach eliminates a lot of this. Rates have changed too, I just did a web purchase from one of the large vendors and had the following choices 1) Fedex Ground $6.99, 2) Airborne 2 day $8.99 and 3) UPS Ground $9.37. UPS rates are up a lot!

-bd

[Kundinger]

You know... somethings do cost more initially... but take into account the hidden costs...

With UPS & FedEx, I can track packages, USPS loses packages and has no way of tracking unless you buy that service.

Implementing USPS changes are a real pain... lots of FMP work everytime someone at the USPS gets a new idea on how to do things.

My clients can go right to the UPS or FedEx website and enter a new package and UPS or FedEx automatically pick up the package. Everything is web based. Some of my clients have a contract with UPS or FedEx for a monthy fee... this includes software, pickup etc. Thing you don't get with the USPS and you have to take the package to them.

And as a final note... the 'hassle factor'... very costly with the USPS... UPS & FedEx have done away with most of this. Again... it competition vs. NO competition.

Enuf said... just my opinion!

Bob Kundinger

[John Fowler]

Thanks for the replies re: The case equation. Yes, there were some "less than or equal to" and "greater than or equal to" characters that didn't translate into the post. The calculation itself isn't that long--perhaps 10 lines or so total. After posting that, I switched two zones, and everything was peachy. Go figure. But you're probably right about using a related file. I just resist getting too baroque with my relationships, because I'm always tempted to use a lot more than I need to.

Regarding shipping methods, well, I have to disagree with all of you, based on my experience. FedEx has a delayed and lost rate that approaches 10%. UPS is even higher. I've had UPS lose and damage thousands of dollars in merchandise. But I've used Priority and Express Mail to ship hundreds and hundreds and hundreds of packages comprising hundreds of thousands of dollars in merchandise (handmade guitars and accessories), and never once have they lost a package, and the 2 delays I experienced (out of those hundreds of orders) were with Express Mail--the guitar was 3 hours late--and the refunds were paid immediately, in cash, on the spot in my local P.O. Insurance claims are paid within a week, although I've never had to file one myself. Try getting THAT out of UPS. Until yesterday, I could ship a $400 guitar pickup to any US State, APO/FPO, or Territory for $3.50. Try shipping anything to Hawaii with UPS for that cheap, or try getting it there in 2-3 days. And overnight with USPS is a no-brainer. To ship a guitar with USPS is $50 anywhere in the US overnight. That same guitar with FedEx or UPS costs 3 times as much, and gets the gorilla treatment. Not so with the PO, although overnight stuff does ironically take a FedEx plane! The USPS also delivers to every single address in the US, including PO Boxes. They charge nothing for Saturday delivery, and for Express Mail stuff, they also deliver free on Sundays.

The new zone system, I must confess, is disconcerting, and will likely mean a significant increase in my shipping costs. But I get free daily pickup, I pay nothing for shipping materials, and even with the increased prices, it's still cheaper than the alternative. Up until the rate increase, in 9 months of doing business I have saved literally thousands of dollars in shipping compared to FedEx or UPS, with nary a customer complaint. My savings will be less now, but they will still be savings.

John

[danjacoby]

Oak, you're comparing apples and oranges.

UPS & FEDEX can track your packages, guarantee on-time delivery, and refund your fee if they're late.

Priority Mail is untrackable (unless you pay extra), has no guarantee, and never refunds a durn thing.

Compare Priority Mail to regular 1st class mail, and you'll find very few instances where 1st class isn't the better (i.e. less expensive) choice. Specifically, unless the weight of the letter or package comes in at or just under the weight limits for the various weight classes of Priority Mail, 1st class is cheaper.

Now, if you want to weigh every package you send before deciding which way to send it, and divide the packages up accordingly, go ahead; you'll save a few pennies here and there. Personally, I consider it a waste of time and resources.

As I said before, NEVER use Priority Mail.

[John Fowler]

Well, according to usps.com, the maximum weight for First Class is 13 ounces. If you never ship anything over 13 ounces, then First Class might work for you. But if, like most small businesses, your packages are in the 1-10 pound range, First Class ain't gonna do you much good.

I will concede that the USPS rate change makes FedEx--but not UPS--more attractive, but I figure, I can get something in my customer's hands in 2 days, or I can let them check their tracking number for a week or more while they wait for it to arrive. I've compared ship times both to and from my store. FedEx and UPS average 4-7 calendar days, with rare instances of 8 calendar days. Priority Mail is 2-3 days, with no instances of anything longer than 4 days. Of course, this average which is aided by the free Saturday delivery. These aren't theoretical times, these are actual ship times to and from addresses all over the US. I never have my vendors ship to me by anything other than Priority Mail anymore--I just got tired of waiting for my stuff to get here with FedEx or UPS (the one exception is vendors within 150 miles). And I've asked my customers which they'd prefer: A trackable delivery that takes 5-7 calendar days, or an untrackable one that gets there in 2 or 3. Every last one of them would rather get their stuff sooner.

If I had listened to the "NEVER use Priority Mail" advice, I'd be about $3000 poorer, and I'd have a lot more frustrated customers.

Also--to an earlier question, I used a calculation because I didn't have a complete list of all the zip codes, just ranges. So I had to come up with a greater than/less than calculus to account for those ranges.

Thanks for the thoughtful replies!

John

[danjacoby]

Having talked with the Postal Service (and been bounced around by bureaucrats)...

The "limit" on First Class mail doesn't really exist -- it's just that, after 1lb., it's actually less expensive to go the Priority Mail route. This is a new thing!

Above 1lb., however, it's cheaper to go 4th Class. The down side is that packages will probably take an extra day or two to get there.

Priority Mail still isn't trackable without an extra charge, and there is still no guarantee of "on-time" delivery and no refund for packages that get horribly delayed.

Bottom line -- "you pays your money and you takes your chances".

P.S. My amended final statement: For anything under 13 oz., NEVER use Priority Mail.

[Kundinger]

Hi, again, John...

According to your post...