Portals, Filtering, Relationships, and ESS. A FileMaker puzzle.

[JHaughey]

  This question could as easily have gone in ESS or FM GO, but this seemed like the place. 

 

  I have a portal in a FM Go iPad layout. It displays records from a related products table. The products table is a SQL ESS table. The relationship is defined by two global fields in the interface file which relate to a "locationID" and "categoryID" field on the product side.

 

   The product table has a field named "productID" which is not a primary key and is not unique. The primary key is named "entryID." So, in products you can have records such as

 

                            "entryID: 100, product: widget, version: widget 1.0, productID: 500,"

                            "entryID: 101, product: widget, version: widget 2.0, productID:500"

        

 

    I want to only display one record of each productID in the portal. Even though there are two versions of the widget, I want only one "widget" to appear. 

 

    I can make a calc field in the product table and flag one record of each productID with a "1" using a self join:

 

                         If(product2::productID  =  productID;1;0)

 

     However, this is an unstored calc, so I can not use it in a relationship to the interface table. I can use it filter the portal, but this results in a data load time on the iPad which is completely unacceptable. and since it's an ESS table, I am limited to using only calc and summary fields.

 

     How can this be accomplished?