AnsweredAssumed Answered

Help with a Join Table

Question asked by sccardais on Jul 31, 2014
Latest reply on Aug 3, 2014 by FentonJones


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!