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?