I have two Databases: AnnualReturn and Director

AnnualReturn has the fields: ClientNumber, AGMDate

Director has the fields: ClientNumber, ElectionDate and ResignationDate

In the Annual Return Database, I need to generate a report which reflects only those directors from the Director Database serving at the AGMDate. I am trying to filter the portal to select those records with related ClientNumber in the Directors database which have an empty ResignationDate or a ResignationDate later than the AGMDate for the related records in the Annual Return Database and ElectionDate earlier than the AGMDate.

I can't seem to come up with a key field for the relationships to accomplish this. Any Suggestions??

Filtering on dates are really problematic. Since the filtering indexes on both sides of the relationship change daily.

The best suggestion I can make would be to use replaces on the indexes or to use the Today function to build the indexes. Neither is a great solution as it requires a bunch of processing to take place daily in order to rebuild the indexes.

The other option is to NOT deal with a relationship and simply use a find based on the criteria that is input into some global fields.