6 Replies Latest reply on Jan 21, 2017 5:17 PM by stevestearns

    Creating A Table Relationship With Current Records


      I am trying to create a table relationship that relates two tables with a condition that only contains current records, not historical records and am performing other calculations thereafter to join records.


      Units (table):

      • UnitNo
      • Address
      • City
      • State
      • ZipCode
      • UnitType


      UnitDeeds (table):

      • DeedID
      • UnitNo (relationship to Units table)
      • PurchaseDate
      • SalesDate
      • PreviousRecordFlag (meaning not the current Unit Deed, as it has been sold again, indicated by a "-1")


      UnitOwners (table):

      • UnitOwnerID
      • UnitNo (relationship to Units table)
      • DeedID (relationship to UnitDeeds table)
      • FirstName
      • MiddleInitial
      • LastName
      • Suffix
      • Entity (name of a trust or corporation)


      I have a second instance of the UnitOwners table (a duplicate) that has a join matching the UnitNo = UnitNo and DeedID = DeedID. I use this in a calculation to combine multiple Unit Owners of a Unit Deed together for letters, reports, etc. I want to be able to have a table with just current Unit Owners without having to do a find, with those records that only have a PurchaseDate, but the SalesDate is empty (or null).


      Is there a way to create a third condition in the relationship that would be UnitNo = UnitNo and DeedID = DeedID and if PreviousRecordFlag <=> "-1" native within FileMaker? Or is there a better way to achieve the goal of a table with only current Unit Owners of a Unit? I will be exporting this information to connect FileMaker Go 15 databases on iOS.


      Message was edited by: Steve Stearns Added MultipleRelationshipJoins.fmp12