1 Reply Latest reply on Jan 16, 2012 12:48 PM by philmodjunk

    FIND REQUEST....

    goodkinbrett

      Title

      FIND REQUEST....

      Post

      FM Pro 11 Advanced...

       

      I have a table containing history of all mailing addresses (with a date field) for a related table of people....

       

      I need to isolate the most current address for each person and then search the result to see which addresses meet a list of zip codes....

       

      HELP ME !!!!!!!!!   (please)

        • 1. Re: FIND REQUEST....
          philmodjunk

          I think you have this relationship:

          Person::PersonID = Addresses::PersonID

          In this relationship, if you haven't done so already, double click the relationship line and specify that addresses be sorted by the date field you mentioned in descending order. This gives us a relationship where the most recent address will be listed first.

          Now, define a  calculation field, currentZip as Addresses::Zipcode

          Given the sorted relationship, this zipcode will be the zipcode from the most recent address and now you can use it in Find Requests where you need to enter zipcode criteria for the most recent address. It's an unstored calculation so your finds will not be quick with large data sets, but it will work.

          For faster results, use a number field in Person that "looks up" the zipcode from Addresses, then use script triggers on the zipcode field to force an update of this field any time you edit the zipcode of the most recent address.