Working with postal code ranges

[jasonwood]

I'm working on developing a UPS Shipping Rate calculator to be attached to invoices so that shipping charges can be automatically determined based on destination postal code, weight, and service level.

The only thing I'm stuck on is determining the destination "ZONE".

For example, from my origin...

postal codes that lie between A0A and A9Z (alphabetically) are in zone 11, B0A to B2T are zone 11, B2V to B3Z are zone 9, B4A to B9Z are zone 11, and so on (there are 115 ranges of postal codes, and 13 possible zones (zones range from 00 to 12).

I will have a script on my invoice called "CalculateShipping". Which will first have to look at the postal code and determine the zone. How do you suggest I go about this? Do I have to write a 115 step case statement?? Yikes!

For those of you who are not familiar with Canadian Postal codes, they are always 6 characters alternating alpha/numeral. eg: L6J6M1... note that the zone chart I am looking at only specifies the first 3 characters of the postal code.

Thanks!

[LiveOak]

I'd use a separate file with one entry for each 3 character possibility. This approach may have thousands of records, but with only two or so fields (code, zone) it shouldn't offer any speed limitations.

-bd

[jasonwood]

Ya that could be up to 6760 records! Although you're probably right that it will be worth it.

You just gave me an idea though... a child can use a field with many entries (separated by carriage return) for a match field can it not? So it might be easier to have 115 records (one for each range, and run a script that calculates (somehow) all of the postal codes in the range and puts them in the match field separated by carriage returns.

Your relation would then match by the first 3 characters of the postal code on the invoice to one of the ranges in the zones db.

Would there be any speed problems with this?

[BobWeaver]

Do you need to use the first 3 characters of the postal code, or just the first two? That would reduce the number of combinations to 200 which is a bit more managable.

Note, Canadian postal codes don't use the letters D, F, I, O, Q or U. This leaves only 20 possible letters and the 10 numerals.

You shouldn't notice any speed problems. Handling information from related files is what Filemaker excels at. On the other hand, super complex calculated fields can sometimes be very slow.

[jasonwood]

Gooooooood points! I'll have another look at the zone chart when I get home in a week. If the ranges would permit me to worry only about the first two characters, this won't be too difficult!