3 Replies Latest reply on Jan 23, 2014 2:18 PM by philmodjunk

    Displaying a count of unique values for fields in a portal

    ShaneB

      Title

      Displaying a count of unique values for fields in a portal

      Post

           I would like to be able to display counts of unique values for fields in a portal. For instance, if a record has a portal, inside which field displayed is hotel_city, I would like to be able to count "how many unique cities are in this portal" and have that displayed to my users. Is this possible?

        • 1. Re: Displaying a count of unique values for fields in a portal
          philmodjunk

               If your portal is unfiltered, the same relationship used for your portal can be used to define a conditional value list of all cities listed in the portal. Such a value list will automatically omit duplicates and thus you can use:

               ValueCount ( ValueListItems ( Get ( FIleName ) ; "YourValueListNameHereInQuotes" ) )

               to get a count.

               If you are using FileMaker 12 or later, Execute SQL can use the DISTINCT key word with a count function to get the same count: A new way to count unique values in FileMaker 12

          • 2. Re: Displaying a count of unique values for fields in a portal
            ShaneB

                 I get how to make the value list based on the field, but the syntax of

                 ValueCount ( ValueListItems ( Get ( FIleName ) ; "YourValueListNameHereInQuotes" ) )

                 always seems to generate a value of "1". I have made a calculation field in both the join table and the table to which the portal is connected, but both just show "1" in the field when it is placed in the portal, even if more than 1 unique values from the list are represented in the portal.

                 In fact, if I put in a nonsense name for "ValueListHere", I get the same result: 1.

                 If I change the storage options to recalculate as needed, it changes to 8 for every portal, which is the number of unique values in the field, but for the entire project.

                 I'm certainly doing this wrong, so any help is appreciated.

                  

            • 3. Re: Displaying a count of unique values for fields in a portal
              philmodjunk

                   You are correct, the calculation must be unstored.

                   But it also appears that your valuelistis not a conditional value list. A conditional value list uses the "include only related values starting from" option to limit the values to only those that are related to the current record in the "starting from" table occurrence--which will be only the records shown in your portal.

                   The drop downs in the top of this dialog should specify your portal's table occurrence. (The name shown in Show Related Records From in portal setup.)

                   The Starting from table occurrence should be the one specified in show records from in Layout setup for your layout.