Currently, I have a table with all the zipcodes. I have no problem in have the lookup provide me with the City, etc. But here is my problem.

[color:red]SOME ZIP CODE have multiple Cities

Example [color:green]01062

State is Massachusetts, but the cities can be>>>

1) Bay State Village

2) Florence

3) Northampton

Of course the lookup just grabs the 1st one.

So, how can I have it show me all 3?:

[color:orange]Value List, Portal, Script :

Any ideas or code examples would be GREATLY APPRECIATED :thumbup:

Bay State Village isn't a city, don't let them fool you. The issue with Northampton and Florence, however, is a problem. 70 Main St. in Florence is "Northampton Medical Center" whereas 58 Main St. in Northampton is "Florence Savings Bank." Only a little confusing.

Your "City" field could have a value list attached to it showing related values based on the zip code. Meanwhile the city field definition, rather than being a lookup, can be an auto-enter calc that checks the number of related values and enters one only if there's only one. e.g.

Case ( Count ( ZipCodeLookup::City ) = 1; ZipCodeLookup::City )

I don't know how you might want to incorporate this into your interaction. Perhaps when tabbing from zip code it goes to city causing the value list to appear?

My zip code, 91361, has multiple counties, and more importantly, multiple sales tax rates.

my city has multiple zip codes.

Lee

The only solution is to "know" the City, zip and county. I have partially solved this by using usps.com's zip code look-up to verify an address and get the zip code and county.(I currently don't need tax info for an address, but I have looked into this problem a year or so ago.) Some states have web sites that you can interface with and get the correct tax rate for a given address. (Ohio does)If your project only needs california's tax rates. I would look torwards the state goverment web sites. (There might be an easy way to look-up the tax rate. Some of the westeren states are pretty easy. Most of the eastern states are nightmares and are impossible to draw any meaningfull relationship between the city, county, or zip codes.)

This topic comes up from time to time. A serch of the this Forum should return some text files, and links for Zip Codes.

Have you tried a Google search for Zip Code Lists, I just did and there were over 10 pages returned?

HTH

Lee

These lists may be usefull for Filtering value lists or... but are not enough to loook-up the "right" Zip Code for a city or vice versa. The best that you can expect from relationships like these are a list of possible values. You may find that 90% will relate to 1 possible value but how do you deal with the other 10% ? The only real answer is to "know". You can find out (Property searches by address, Each address needs looked up seperatly.) If I was going to try and look-up the tax rate for any given address I would look to the state auditor, or Dept of Taxation. Most states will have tools to look-up tax districts. Chances are their system will also be flawed but at least if you follow thier guidlines they will be thier flaws not yours.