Script vs calculation

[J Ricketts]

I am trying create a script or calculation that will match zip codes in a data base to a specified city area. The zips are a full 5-4 and i don't know how to accomplish this. can someone give me help?

[Fitch]

Create a relationship between the zip codes fields in the two tables.

That's about all I can tell you without more details.

[J Ricketts]

Below is an example of what info i need to set

If any of the 17000 businesses have a zip that is in the list below they need to be put into the set region and city area assigned to those zip codes.

Here is the fields

1: Region

ie. Phoenix Area

2: City Area

ie. Phoenix

3: Zip codes

85001 or 85002 or 85003 or 85004 or 85005 or 85006 or 85007 or 85008 or 85009 or 85010 or 85011 or 85012 or 85013 or 85014 or 85015 or 85016 or 85017 or 85018 or 85019 or 85020 or 85021or 85022 or 85023 or 85024 or 85025 or 85026 or 85027 or 85028 or 85029 or 85030 or 85031or 85032 or 85033 or 85034 or 85035 or 85036 or 85037or 85038 or 85039 or 85040 or 85041 or 85043 or 85044 or 85045 or 85046 or 85048 or 85050 or 85051 or 85053 or 85054 or 85055 or 85060 or 85061 or 85062 or 85063 or 85064 or 85065 or 85066 or 85067 or 85068 or 85069 or 85070 or 85071 or 85072 or 85073 or 85074 or 85075 or 85076 or 85077 or 85078 or 85079 or 85080 or 85082 or 85085 or 85086 or 85098 or 85099 or 85339

[Søren Dyhr]

I would use this:

http://www.filemaker.com/help/FunctionsRef-316.html

With a record for the initial value for each town/city in an additional table - dealing with the exceptions as well!

In my neck of the woods will the numbers gradually rise the farther away the nearby railway station is situated from the capitol ... something similar predictability would properly work in your neighbourhood so postcodes from x to y means areas and suburbs of a city/town.

--sd

Edited July 2, 2008 by Guest

[comment]

If the above block represents the actual contents of the Zip codes field, then all that's needed is to replace the " or " parts with carriage returns, and proceed as per Tom's advice.

[Fitch]

Your zip code table should look like this:

Zip    Region        City 

85001  Phoenix Area  Phoenix

85002  Phoenix Area  Phoenix

85003  Phoenix Area  Phoenix

etc

Once you have that in place you can use Lookup (or LookupNext as Søren points out) to populate the Region and City fields in your businesses table. Does that make sense?

[J Ricketts]

i am still having problems with what order i should put things in.

[Søren Dyhr]

Perhaps this will help - and it's not quite what Fitch suggested. Only a record from where a range starts, and as soon as the serial stretch is broken create a new record, certainly not one of each in the entire range - waste of storage!

--sd

businesses.zip