2 Replies Latest reply on Jan 16, 2009 6:36 AM by ahdavidson

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



      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:


      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.