Add CurrentDate<=Lease Date in relationship.
I want to show the current leases, where the LeaseActualEndDate is greater than the current date or is empty.
I have created a global field for the current date and have created a relationship where CurrentDate<LeaseActualEndDate but how do I include the records where the lease date is empty?
Maybe you could use the following calculation in place of your End lease date field in the relationship:
IF ( isEmpty(EndLeaseDate); Date ( 1 ; 1 ; 3000) ; EndLeaseDate )
Clear the "Do not evaluate if all referenced fields are empty" check box and specify a "date" return type before you click OK.
I've set this calculation to use an end lease date that is a rediculously future date when no EndLeaseDate is specified so that "no end date specified" fields will be included in the relationship..
Sorry, I can be a bit slow sometimes, but where do I put the calculation?
As a field in your Leases table.
This is what I picture:
Properties:: PropertyID = Leases:: PropertyID AND
Properties::GlobalDateField < Leases::cLeaseDate
Where cLeaseDate is the new calculation and GlobalDateField is a date field with global storage selected and the auto-enter calculation I mentioned.