1 Reply Latest reply on Oct 13, 2014 12:57 AM by erolst

    Count of function in Summary

    zehang

      Hi! I am wondering whether there is a way to count in a summary field for only how many unduplicated record are there. for example, there are 3,2,2 customer(s) from company A,B,C respectively, how to count the number of the companies of all the customers, which is supposed to be 3? if simply set the summary field to be the COUNT OF field COMPANY, it apears it will be calculated to be 7=3+2+2.

       

      Thanks so much in advance.

        • 1. Re: Count of function in Summary
          erolst

          Is this for a report sorted by company?

           

          Then create a calc field

           

          companyID ≠ GetNthRecord ( companyID ; Get ( RecordNumber ) - 1 )

           

          and use a summary field SumOf, or a calc field

           

          Case ( companyID ≠ GetNthRecord ( companyID ; Get ( RecordNumber ) - 1 ) ; 1 )

           

          and a summary field TotalOf.

           

          If you have FM13, you could create a summary field type ListOf for the customerID, and an (unstored) calculation field as

           

          Let (

          foundCustomerIDs = Substitute ( sListOfCustomerIDs ; ¶ ; "," ) ;

          ExecuteSQL ( "

            SELECT COUNT ( DISTINCT ( companyID ) )

             FROM customers

            WHERE customerID IN (" & foundCustomerIDs & ")

            " ; "" ; ""

            )

          )

           

          which works independently of any sort.