4 Replies Latest reply on Feb 16, 2013 7:33 AM by Oliver_Reid

    Unique Value Count

    strngr12

      Hello Everyone,

       

      Is there a way to get a summary or other type of field to give me a count of unique values in another field? For example, lets say I have five fields:

       

      A

      A

      B

      C

      C

       

      The value I'd want returned in my summary field would be 3 because there are three values: A, B and C. The story is that I am trying to break down a daily average of uploads to a site. What I know is the size of each individual upload and the date they happened on. Because the uploads didn't happen daily I can't just take a date range ad divide the total, I need to divide the total size by the number of days that uploads actually occurred.

       

      Any help would be greatly appreciated!

       

      Thanks,

       

      Dan

        • 1. Re: Unique Value Count
          brsamuel

          One way:  go to Brian Dunning's web site and look for a custom function that does what you're looking for, such as http://www.briandunning.com/cf/596

           

          What this function returns is a list of unique values.  On this result use FileMaker's built in ValueCount function.

          1 of 1 people found this helpful
          • 2. Re: Unique Value Count
            strngr12

            Thanks, brsamuel, but I tried it and I'm not sure that's what I'm looking for.  This seems to return only the unique values from a list in a field in a single record.

             

            To be clear, in my scenario there is only one value per field per record, but many records may have the same value in that field.  In other words, each record has a single value in the "Uploaded On" field, but multiple files may have been uploaded on the same day so multiple records may have the same value in the "Uploaded On" field.

             

            What I'm looking for is list of those singular values summarized in one, global field.

             

            If two files wer uploaded on Monday, one on Tuesday and three on Thursday, I want one, glopal field that says: Monday, Tuesday, Thursday much as I can have a global summary field that says "6 total files were uploaded."

             

            Does this make more sense?

             

            Thanks,

             

            Dan

            • 3. Re: Unique Value Count
              Oliver_Reid

              Execute Sql    (  "Select COUNT(Distinct datevalue from Table) ",";"cr")  for the count

               

              Execute Sql    (  "Select Distinct datevalue from Table ",";"cr" )  for the list of dates

              • 4. Re: Unique Value Count
                Oliver_Reid

                PS make sure the field is not called Date or Day .. SQL is picky