AnsweredAssumed Answered

Relationship Help Needed (SQL Database)

Question asked by bvondeylen on Sep 26, 2013
Latest reply on Sep 29, 2013 by philmodjunk


Relationship Help Needed (SQL Database)


     In our SIS (Infinite Campus), I am trying to create a report in FileMaker Pro 12.

     Want to get the address of parents

     The following tables are giving me difficulty.

     dbo.identity <--> personID  <--> dbo.householdmember

     dbo.householdmember <--> householdID  <--> dbo.household

     dbo.household <--> householdID  <--> dbo.householdlocation

     dbo.householdlocation <-->  addressID <--> dbo.address


     The relationships are listed above with the field in the middle. Now the problem.

     there is a field in dbo.householdlocation the contains an endDate 

     There are some people that have multiple entries in dbo.householdlocation and the 'old' entries have an endDate

     The active entry has a null value in the endDate field. 

     How can I create a relationship between dbo.household and dbo.householdlocation to only get the correct dbo.address record?

     I cannot match anything to endDate since there is no endDate field in dbo.household

     Is this something that FileMaker cannot do?