AnsweredAssumed Answered

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

Question asked by ahdavidson on Jan 15, 2009
Latest reply on Jan 16, 2009 by ahdavidson


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


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:



  president: number (citizen_id)

  vice_president: number (citizen_id)



  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.