1 Reply Latest reply on May 20, 2014 11:09 AM by philmodjunk

    Counting unique values within a FILTERED portal

    ShaneB

      Title

      Counting unique values within a FILTERED portal

      Post

           I have figured out how to count the number of unique values within a particular field on records inside a portal. I use this to do things like, say,  count the number of different source_property_names that appear within a "cluster" group of properties. This takes place by doing a calculation on the join table:

           ValueCount ( ValueListItems ( Get ( FileName ) ; "names_calc") )

           "names_calc" is a value list that uses the values from the source_property_name field.

           So this works great for my current portal display layout. However, I would like to be able to display the information from the records inside the portal more granularly. For instance, there is a field named "source_property_performance", and that might be "H", "M", or "L". I can create a filtered portal that will only display records with one of those particular values just fine, and can easily do things like count the total number of records in a filtered portal and display that number. However what I want to do is count the number of unique "source_property_name" fields within a portal that is being filtered by the "source_property_performance" value. This currently does not work correctly, as far as I can tell.

            

           Any ideas for successfully doing this? Again, long story short, it's "how do I count the number of unique values in a particular field of the records within a filtered portal?"

        • 1. Re: Counting unique values within a FILTERED portal
          philmodjunk

               Many portal filter expressions can also be implemented in an unfiltered portal by using additional match fields--sometimes these are calculation fields. That's how we did this before portal filters were an option.

               And now with FileMaker 12 and newer, we can also use ExecuteSQL to get these counts where we could build the same portal filter logic into the WHERE clause of the SQL query.