multiple criteria relationship, one of which is a filter not a match

Title

multiple criteria relationship, one of which is a filter not a match

Post

I would like to present a subset of the records in a table related to a first table in a portal associated with the first table's layout. This is straightforward and I know how to do that.

Getting more complicated is that I want an AND condition between two criteria in the relationship. One is a simple match between corresponding fields in the two tables. That's fine, but I want the second criterion to be a simple filter of one field in the related table.

Here's the example, to be more concrete:

COUNTRY table

  president: number (citizen_id)

  vice_president: number (citizen_id)

CITIZEN table

  id: number (citizen_id)

  age: number (years)

Now let's say I want to present a list of citizens in a portal on the COUNTRY layout who are 35 or older so that someone could select their choice for president.

I would define a relationship that matches the COUNTRY: president field to any CITIZEN:id field. That would return all CITIZEN records to the portal.

Now I want to add a match criterion that reduces the list to just those citizens 35 or over. How can I define a second criterion that is not a match between two fields in related tables, but rather an expression like (CITIZEN:age >= 35)?

This is further complicated by the fact that neither of these tables are local to FM9, but are from ODBC data sources. So I can add only calculation or summary fields to those tables.

Any advice much appreciated.

Thanks,

.andy