UPS Zone Chart text conversion

[PiedPiper]

Ive been scratchin my head for a few hours. I have attached an Excel spreadsheet showing UPS zones. I need to convert this to usable records that can be attached to customer zip codes to indicate which UPS zone each customer is in.

I figure I need script because it's in a weird way. Any ideas how to take this data and make it into records? for a relation to zip codes?

Pete

UPS Zone Chart.zip

[CyborgSam]

Pete->

Looking at the spreadsheet, I see that all the non-ground zones numbers are simply a prefix (30, 20, 24, 13, 10) followed by the ground zone. A script is not needed, nor is a group of records. I'd simply make two calculations that return text:

c.UPS.Ground.Zone, based on first two columns of spreadsheet:

Case(

((Zip.Entry >= "004") and (Zip.Entry <= 005)), "8",

((Zip.Entry >= "010") and (Zip.Entry <= 089)), "8",

...

)

c.UPS.Zone, assumes a field called UPS.Ship.Method:

Case(

UPS.Ship.Method = "Ground", c.UPS.Ground.Zone,

UPS.Ship.Method = "3 Day Select", "30" & c.UPS.Ground.Zone,

...

)

In the first calculation, some of the tests can be combined using or.

Does this make sense?

Sam

[PiedPiper]

Hi You mean I can join a relationship to Excel worksheet, Sam? And just use a calc in my Customer file? I don't need to make FM records out of it?

And what if UPS changes its zones. Would it ever? Would changing the spreadsheet just change the calcs in Customers? And I have another chart for Alaska, Hawaii and Puerto Rico that are different - more broken out and will be different rates because their not continental US. Do I delete the headings in the worksheet before I join them? Thanks for helping and I like your picture you display.

Pete

[Fenton]

No, you can't join a relationship to Excel, 'cause Excel doesn't know have that capability. You can match a FileMaker field to a column during Import; which may come in handy to update later.

The way I deal with spreadsheets is to turn them into what they would be if they were a proper FileMaker file. Each row & column combo is actually a new record. In this case you have these fields:

ZipCode (well, just 3 characters, but same thing)

Charge

Type (the column headers)

I import each "header" column separately, importing both Zip column and the Charge amount. I then Replace the Type ("Ground", "2nd Day Air", etc.)

Now I've got something I can deal with. They also have a zip range (understandable), only a lower and upper limit. This is like an old-style "lookup table," and FileMaker has the tool to deal with it. It's in the Lookup options, "When no match, lookup next lower" or "lookup next higher."

You can use this to lookup the lower limit for the Zip. Then you've got something you can use in a relationship. Because I imported each "type" into its own records, I can use a combo of the "beginning zip" and the type to lookup the charge.

Yes, it would require a bit of work to update, if UPS makes changes. But Importing the separate columns took less than 15 minutes, because it was practically the same import; I just moved the type match down to the next column.

(I actually had more trouble just figuring it out, and doing the "next lower". FileMaker 2 guys were really good at this stuff; it was all they had :-)

I'm including all the files, incl. the Excel spreadsheet and my original import into a FileMaker file, just so you can see all the steps. You don't really need the 2 "column" type files after you're done.

UPS Zones.zip

[Fenton]

Oops, I read now that the "Charge" is not a charge, it's the UPS Zone. Details, details. I often get caught up in the logic and forget the business rules entirely. Remove the currency format.

[PiedPiper]

Fenton! Your helping me again here too. And a file too. Can't wait to try this out and understand it. I'll let you know if I get total stuck but i'll try to take it from here. Hate taking others time on things, thanks. Im sure learning a bunch lately.

I may be able to impres my boss Monday after all - fat chance.

Gotta be sure I get that summaries thing firm in my head first and decide on backgrounds.

Pete

[CyborgSam]

Pete->

Fenton explained what I should have: you need to get the data into FileMaker, in my method you copied & pasted in formulas, In Fenton's you import into records.

There are many right ways of doing this, Fenton's is easier if you need to use several spreadsheets & you don't want to spend time editing calculations in FileMaker when zones/etc. change. Mine is easier for simple cases like a single spreadsheet.

[PiedPiper]

Wel this has been a while cus I got caught up in other projects - namely switching to vs. 7. :

I'm pulling this in now. I hope it's not too late for another question.

Fenton, I've duplicated everything you have exactly. I hit two snags. I want to use this popup on two different tables. First is Addresses table. Second is Invoices table. Invoices inserts address from addresses. we need it as permanent part of the invoice so if they change address, the invoice won't change.

I also have a Territories table which contains every zip code, city, state, county etc. which will be used to look up when entering an address in addresses. so now I added UPS Zones, Zips Lookup and UPS Zone Chart.

How do I use your Zips Lookup window in my 7 tables? I was thinking of script parameter. When someone clicks Look Up Zone it grabs the zip (?) and inserts it in the popup zip field? Can you direct me on pulling this finally together? Your popup rocks and I really want to use this!! But I'm lost.

Pete

[PiedPiper]

I should mention. I want the popup not as another file but within my main file. I created the layout but don't know what table to specify with it. Zips Lookup? Thanks!

[Fenton]

I guess you're confused about what the files are. The fields and mechanism in ZipsLookup can go in whatever table where you want the result. Just recreate the mechanism in whatever table you want. The table to target with the 2 relationships is always the UPS_Zones, where the data for the zip codes (1st 3 digits) and the rates are.

The only pop-up is for the type of delivery. The Zip Code is typed. The result will be automatically looked up.

As I said in my earlier post, it first looks up the "beginning" zip of the range, then uses this to get the rate.

My zip, 92116, first looks up 900 as the beginning, then gets the rate from that match.

If you don't know exactly what a Lookup is, or what Lookup Next Lower means, then you should read about it in the Help. It's been there since FileMaker Pro 2 (or earlier), and is basic to working with rates.

(P.S. Just drop both the above files on 7 to convert. You could put ZipsLookup into a table of any file, if you want to. Just import the data. Or leave it on its own. The other file, UPS_ZoneChart, was just used as a 1st conversion of the Excel file.)