AnsweredAssumed Answered

Creating A Table Relationship With Current Records

Question asked by stevestearns on Jan 21, 2017
Latest reply on Jan 21, 2017 by stevestearns

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