Help with a Join Table
My FMP file is used to track membership info in our neighborhood Homeowners Association.
My question is about maintaining a history of all current and previous owners for each lot.
Lots are "owned" by a single Owner / Entity. Tables = Owners and Lots
Owners can own multiple Lots.
Over time, ownership changes. Owners may sell some lots and keep others.
Likewise, over time, one Lot can be owned by multiple Members - but not more than one Owner at a time.
If we want to keep a record of all Current and Previous owners, is this a situation that requires a Join table (e.g. OwnerLotTransaction) with match fields OwnerID and LotID?
If a single lot had multiple records in the join table, how could we automatically flag the Current owner and change the status of previous owners to "Previous"?
Would this require a calculation field (OwnerStatus) in a join table that somehow sets the value as Current in the new and Previous in the old?
This is beyond my current knowledge of FileMaker. I know enough to know it CAN be done but not sure how!