Dynamically-filtered relationship problems

[tinynumbers]

I have two tables with the relevant parts defined as follows:

Event

conferenceID - Number

eventDate - Date

Conference

ownerID - Number

lastDay - Date calculation (unstored) defined as: Max (Events::eventDate)

Owner

earliestDayToShow - Date

In the relationship graph, I have the following table instances:

Conferences - Conference

Events - Event

Owners - Owner

OwnerConferences - Conference

I have these relationships:

between Conferences and Events:

Conferences::id = Events::conferenceID

between Owners and OwnerConferences:

Owners::id = OwnerConferences::ownerID

AND Owners::earliestDayToShow ≤ OwnerConferences::lastDay

My intention with this second relationship is as follows: in my layout which shows a particular Owner (from the Owners table instance) I want to have a portal showing all the Conferences (OwnerConferences) for that owner. And I want to allow the user to select a date (Owners::earliestDayToShow) which will filter the portal. This is so that the portal can be filtered to hide old conferences (i.e. conferences where the last event of the conference was before today's date, or some other user-specified date).

However, this isn't working... If I remove the AND Owners::earliestDayToShow ≤ OwnerConferences::lastDay part of the Owners - OwnerConferences relationship definition, then my OwnerConferences portal on the Owners layout does display all the conferences for that owner, as expected. However, if I include that part of the relationship definition, then the portal shows NO records, no matter what value I have Owners::earliestDayToShow set to.

Any ideas?

[bruceR]

Are they both date fields?

[tinynumbers]

Are they both date fields?

Yes they are...

Conference

ownerID - Number

lastDay - Date calculation (unstored) defined as: Max (Events::eventDate)

Owner

earliestDayToShow - Date

Owners::id = OwnerConferences::ownerID

AND Owners::earliestDayToShow ≤ OwnerConferences::lastDay

[comment]

You cannot use an unstored calculation as the matchfield on the "other" side of the relationship.

[tinynumbers]

Ok - I thought that might be the problem - but Filemaker didn't complain about it when I created the relationship.

Any alternate approaches which you might suggest which would accomplish the same desired end-result?

Thanks.

[bruceR]

Get FileMaker 11. Much more flexible portal filtering built in. Some performance limitations if your base relation involves large record sets.

[tinynumbers]

Get FileMaker 11.

Ugh, I was afraid somebody would say that. Unfortunately, that's not possible right now, due to limited budget and limited time to upgrade the database to the v11...

[comment]

The simple way would be to script a find and show the results in a list/table view of the Conferences table. If you must have it in a portal, then it's not so simple: search the forums for "Ugo method".

[bruceR]

Ugh, I was afraid somebody would say that. Unfortunately, that's not possible right now, due to limited budget and limited time to upgrade the database to the v11...

There is no "upgrade the database". It is the same file format.

The only thing you'd have to do is set up the portal filter(s). Depends on the value of your time I guess.