"Postal Code" lookup puzzle
I have a scenario that can best be described with an example of a postal code lookup.
My main table is a contacts, including a street address (with house number in a separate field from the street name). I have a secondary table that contains a "key" for postal codes within a particular area, listing the range of house numbers on a particular street for each postal code within the defined area:
|A1A 1A1||Main St||1||15|
|A1A 1A2||Main St||16||24|
|A1B 1A1||First St||23||35|
You'll note that there can be multiple instances of the street name, since different addresses on that street will correspond to different postal codes (Odd and even house numbers, on opposite sides of the street, would have different postal codes but that's irrelevant to my current scenario). Another twist is that someone might be at an address outside the boundaries of the area defined by my postal code, e.g. 25 Main St.
1 Main St returns A1A 1A1
9 Main St returns A1A 1A1
8 First St returns (blank)
99 Main St returns (blank)
My goal is that when I enter the street name and number of a new contact, it will automatically populate the Postal Code field (by calculation, lookup, triggered script, or whatever) to save me having to look up the most common codes. I'll also need to be able to override the field in case we get someone outside the area for which I have the known postal codes.
In other words, when I enter the street name and number, it looks to see if the Postal Code Key table has an entry with the given street name AND a range of numbers within which the given street number falls, and if so to return the postal code.
I've tried all sorts of different methods, including creating interim calculation fields that concatenate the street name and number in both tables and perform a LookupNext(lower), and even converting the text to numbers (using the method suggested here: http://fmforums.com/forum/topic/84372-convert-text-to-number/ ) when it didn't seem to want to calculate the streetname and number as one string. The Lookup autofill seems to be more reliable than the Calculation autofill, but not flexible enough.
Is it simply beyond the capacity of FMP to look up a value based on two fields, or merely beyond my ability to script it?
Thanks in advance for any insight,