Are you trying to set up an SQL query to extract this info or are you trying to use Manage | Database | Relationships to define a relationship between two tables populated with data from the external data source?
I am trying to use Manage | Database | Relationships to define a relationship between two tables populated with data from the external data source.
I know I can create a portal which would show all the addresses for people who have moved, but I only want the most current displayed (for a school directory).
Using FileMaker is great for reporting (more like a desktop publishing package).
I have not delved into the SQL Query aspect of this. If that would work, I could try it, but I would need a little explanation on how to do that.
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
You should be able, in Manage | Database | Relationships to specify a sort order that sorts the related records in ascending order by EndDate. That should make the current address record the first related record.
And your other option is an SQL query with a WHERE clause that specifies that the EndDate field be null.