Relationship to a null field

Discussion created by bvondeylen on Oct 5, 2016
Latest reply on Oct 7, 2016 by beverly

I have connected an MSSQL database to FileMaker via ODBC, and I am joining the tables to access the SQL database in FileMaker.


The SQL database is our Student Information System, so it has millions of records in many tables. The join that I am having an issue with though, is Primary Mailing Address of a Parent (Guardian) of a student where the Primary Mailing address has changed.


{Table} [Join field]


{Contact} - [personID]

{HouseholdMember} - [personID] [householdID] [endDate]

{HouseholdLocation} - [householdID] [addressID] [endDate]

{Address} - [addressID]


The problem is  {HouseholdLocation} can have multiple records for one person. There is an [endDate] field in that table. When a Guardian moves, the [endDate] field has a date entered, but I cannot create a join on that, because I am looking for the [endDate] field that has 'null' as an entry, and FileMaker doesn't support that. So what is happening, is that FileMaker displays the 'outdated' mailing address and not the current mailing address.


Not sure what to do.