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
