Filtered Portal - Different Twist

[bankofbermuda]

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??

[Kurt Knippel]

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.