3 Replies Latest reply on Mar 19, 2016 5:34 PM by user19752

    How do I filter a portal to only show unique values and then filter using extra calculations?

      Hi all,

       

      I'm using FM Pro 14. I've read lots of discussions regarding this, and I am still having problems. My question is how do I filter a portal to only show unique values and then filter using extra calculations?

       

      • I have a portal that shows a table of offices, but some of the records in the portal are offices of the same company.
      • I want the portal to only show one office from each company.
      • I have two number fields in the offices table, one for the ID of the individual office (these are all unique numbers) and one for the ID of the company (these are not unique as a company can have lots of offices).

       

      • I read this example and copied it FileMaker Portal Show Distinct Value Tutorial - The Scarpetta Group, Inc.
      • I have a parent table with a global field with the number 1 in it and I have a global field on the offices table with the number 1 in it too.
      • I joined the parent table to OFFICES TABLE 1 using this global field. I then added this calculation into the OFFICES TABLE 1 portal on the parent table:

       

      (OFFICES TABLE 1::UNIQUE TABLE NUMBER FOR OFFICE = GetNthRecord ( OFFICES TABLE 2::UNIQUE TABLE NUMBER FOR OFFICE ; 1 ))

       

      THIS WORKS, I get a portal that shows only one individual office of each company BUT MY PROBLEM/QUESTION IS THIS:

       

      When I add another calculation to OFFICES TABLE 1, for example:

       

      (OFFICES TABLE 1::UNIQUE TABLE NUMBER FOR OFFICE = GetNthRecord ( OFFICES TABLE 2::UNIQUE TABLE NUMBER FOR OFFICE ; 1 )) and OFFICES TABLE 1::CITY ≠ "LONDON"

       

      The portal misses some records out, and I see this as I am checking it against a portal beside it to so show all the results that it should show with just the calculation:

      OFFICES TABLE 1::CITY ≠ "LONDON"

       

      How to add extra calculations to this portal so that it does not miss records?