5 Replies Latest reply on Nov 17, 2009 7:00 AM by comment_1

    Many to Many property identification number problem.



      Many to Many property identification number problem.


      Hello, I'm pretty new to Filemaker and I'm hoping someone might be able to help me out. 


      I'm using Filemaker pro Version 10 on Windows Vista Home Premium 64bit. 


      I am working on a file and I have a major many to many problem I don't know how to solve.


      I have a couple of tables  


      File No. 1-Home Owners

      This file includes a lot of information, the most important piece is a PIN that is (sometimes) unique, the time that the owner bought and sold their property (if they have not sold this number is todays date). There is a lot of information that is included in this database, such as the value of the home, and so on. 


      File No. 2-Foreclosures

      This file also has a lot of information. Again this file uses the same PIN numbering as file no. 1. It also has a date for when the property was foreclosed on.


      The problem I'm having with the relationship I've attempted to build is that the PIN is not very unique. The files I am using go back several years, so many properties have changed hands and each row is the owner, not the PIN, so a PIN might occur in file no. 1 half a dozen times. The same problem occurs in file no. 2. Each row is a foreclosure, and unfortunately there have been a lot of foreclosures in recent history.  


      What I want to do is create a relationship where if a foreclosure PIN matches a Home Owner PIN AND the foreclosure date falls inbetween the tenure of that owner all (a person could have several foreclosures) of the foreclosure data  (including the PIN and Date there are 4 relevant fields I would like to include) would be transferred to the Home Owner row.


      I would really appreciate any guidance.  


      Thanks very much,

        • 1. Re: Many to Many property identification number problem.

          Speaking specifically to your request:


          I think you have two date fields in both tables that represent the dates a person acquired and then relinquished(or sold) the property.


          The following relationship would link the records, based on the above assumption and what you described. I'm using my own field names, you'll need to subsitute the actual names from your tables.

          Home Owner:: PIN = ForeClosures:: PIN AND

          Home Owner:: AcquireDate < ForeClosures:: RelinquishDate AND

          Home Owner:: AcQuireDate > ForeClosures:: AcquireDate


          Frankly, it sounds like you need to restructure your tables. Does PIN mean "Personal Identification Number" and uniquely identify one person? That's how I read your description but could be wrong. If so, you need an "owners" table where one record = 1 PIN (and now PIN is unique). The other information in table one should reside in a separate table where one record = either one real estate parcel or one real estate transaction (can't tell which from your post).


          I'd consider the following table structure if I were you:

          Parcels (one record represents a single real estate parcel)

          Owners (one record represents one person)

          Transactions (one record represents one change in ownership. Foreclosures and sales would be recorded here.)

          ParcelOwners (one record represents the ownership of a specific parcel by a specific person for a specific period of time.) This last table would be a Join table linking Owners to Parcels.

          • 2. Re: Many to Many property identification number problem.

            Thanks for your response, and sorry for my lack of clarity... When I had written that I was still a little dazed from all the filemaker induced confusion. 


            The home owner file is something like this  

            PIN DATE GOT   DATE SOLD Owner  


            001 01/02/2002 04/05/2006 john

            001 04/05/2006 11/16/2009 joe

            002 01/01/2000 11/16/2009 jorge

            003 03/02/2001 03/14/2002 jim

            003 03/14/2002 06/14/2002 (unknown)

            003 06/14/2002 11/16/2009 chase bank

            004 01/01/2000 12/12/2008 jenny

            004 12/12/2008 11/16/2009 john



            The foreclosure file is something more like



            001 03/25/2006

            003 04/25/2002

            003 05/02/2002

            003 05/20/2002


            In this case PIN actually stands for property identification number.


            I hope this illustrates the reason that PIN isn't unique, because it refers to a single property, but by addition the dimension of time it is used repeatedly.


            Obviously my database is much larger than this, and it's several hundred thousand rows deep and about 140 columns wide. I am afraid my structure isn't very good, but us researchers have to take what we can get. I agree that it would be good to change the structure but don't know if there is a easy way to do this in file maker given my huge dataset. 


            Before I posted I used the relationship that you included, the problem was that the date part didn't seem to match up. For example a match in one record was being applied to all records with the same PIN. It was a big blob of a match with a lot of duplicates (one foreclosure would be applied to every property with the same pin, regardless of the date parameters). 


            Thanks again for your response.  


             P.S., sorry about the formatting of my example tables. I formated it nicely but when I hit submit I was alerted that the formatting was invalid. I used the formatting buttons included, I wonder if it is a problem with Google Chrome. Oh well. 

            • 3. Re: Many to Many property identification number problem.

              Define the relationship as:

              HomeOwners:: PIN = Foreclosures:: PIN
              HomeOwners:: DateGot ≤ Foreclosures:: ForeclosureDate
              HomeOwners:: DateSold > Foreclosures:: ForeclosureDate



              Place a portal to Foreclosures on a layout of HomeOwners to see all foreclosures that occured during the period of the currently viewed ownership.



              BTW, if your data is sensible, there should be only one owner at the time of any foreclosure, so this is NOT a many-to-many relationship.

              • 4. Re: Many to Many property identification number problem.

                This formula doesn't work. It creates far too many duplicates. 


                Unfortunately recorder of deeds data is not always sensible. Sometimes a date is missed and the ownership change is not recorded.


                Often a single owner will be foreclosed on several times and it is not uncommon for more than one institution to hold claim for foreclosure.



                • 5. Re: Many to Many property identification number problem.

                  ehalpe2 wrote:

                  This formula doesn't work.


                  Check your implementation, because it should work as stated. Also make sure your date fields are indeed Date fields, and contain valid dates.




                  ehalpe2 wrote:

                  Often a single owner will be foreclosed on several times


                   Possibly, but at the time of a foreclosure (in fact, at any point in time) there should be only one owner.