Bi-Directional City/State/Zip Lookup [Beta-8 Advanced]

[Ocean West]

Description: You have Fields in your DATA table

[tab]City

[tab]State

[tab]Zip

You want to lookup the City and State based upon the data entered into the zip code.

Set up the standard relationship...

"data" table to your "city_state_lookup" table using the zip code as the match field.

Now you want the inverse to trigger a lookup of the zip code or at least the first matching zip code based up on the city and state entered.

Setup another table occurrence of your zip code table "zip_lookup" to your "data" table using the city and state fields as multi-predicate relationship.

Then in your auto-enter calc field of the zip code field in your "data" table enter this:

CHECK the box so that it does not evaluates if referenced fields are empty but UNCHECK the box that always replaces.

Case(

Get ( ActiveFieldName ) =  "zip" ; zip;

Lookup(zip_lookup::zip);

LookupNext ( zip_lookup::zip ; Higher )

Now you should be able to lookup from both directions... I too always want people to use the zip code because it is more precise key to do a lookup but it looks odd if you have the tab sequence skip the City/State fields or you put the zip field first. I think this will help...

Also you now can define a value list for the zip code field so that when you have a valid city/state you can see a short list of all the applicable zip codes.

Hope this helps..

Working Under: 8 Advanced

Solution Status: Beta

Pre-requisites:

Author(s) Ocean West

Date: 11/27/05

Credits: Erik Wegweiser for the challenge.

Instructions / Other Info:

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

Bidirectional_zip.fp7.zip

[Ocean West]

slight correction...

Case( 

Get ( ActiveFieldName ) =  "zip" ; zip; 

Lookup(zip_lookup::zip);

TextColor ( zip ; RGB ( 170 ; 0 ; 0 ) )

)

This will keep the value of the zip field but color it to "RED" to indicate that it is an invalid City/State combination or that there are no zip codes based upon such.

[aldipalo]

I thought I followed your directions, but, I am not getting any lookup. However, I am getting all my zipcodes in RED. Any suggestions as to what may be wrong?

[Divegeek]

ok, so stupid question. Whats (activefeildname) be easy on me, I'm new at this :

Jake

[aldipalo]

Format

Get(ActiveFieldName)

Parameter

None

Data type returned

text

Description:

Returns the name of the field currently containing the cursor.

Note: See Solving calculations on a host machine for information about running scripts in client/server and peer-to-peer environments.

Example:

Returns Country, when the cursor is in the Country field.

[aldipalo]

Just a follow up to my Feb. 7 post. I would really like to get this implemented. Can anyone give me any idea why I cannot get the lookup to work?

[AndreT]

Thanks for the article.

I've been trying to work out a solution to this problem where I just want the user to key in the Suburb/Town/City and they would have presented a drop down value list that would show the available options available. By clicking on the suburb they want, in the drop down, it will automatically enter the suburb and state and zip/postcode into their respective fields on the master file.

Is there a FM6 solution to this?

Look forward to you response.