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.
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
PIN FORECLOSURE DATE
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.
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.
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.
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.
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.