2 Replies Latest reply on Jan 15, 2015 10:05 PM by realgrouchy

    "Postal Code" lookup puzzle

    realgrouchy

      Title

      "Postal Code" lookup puzzle

      Post

      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:

                                                                                                                                                                                                                                                                                                                                                                         
      Postal CodeStreetLowHigh
      A1A 1A1Main St115
      A1A 1A2Main St1624
      A1B 1A1First St2335

      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,

      - RG>

        • 1. Re: "Postal Code" lookup puzzle
          philmodjunk

          Define this relationship:

          Contacts>-----PostalCodes

          Contacts::StreetName = PostalCodes::StreetName AND
          Contacts::StreetNumber > PostalCodes::Low AND
          Contacts::StreetNumber < PostalCodes::High

          You can use an auto-enter field option--either calculation or looked up Value to copy the postal code from PostalCodes into contacts but still be able to enter a postal code not currently in your table under the given street name and number range.

          • 2. Re: "Postal Code" lookup puzzle
            realgrouchy

            Ahhh, now I see what I was missing. I'm not very good with relationships. :P

            I'll try that next time I take a stab at that database.

            Thanks,

            - RG>