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

    ahdavidson

      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