2 Replies Latest reply on May 13, 2015 2:37 PM by MT_2

    Sub Summary report search by unique ID and fields



      Sub Summary report search by unique ID and fields


      Hi, I am not sure if this is possible!! Could someone let me know if it is or is not.

      I have a table called  "Product" when a new record is created a new "ClientID" is created per record.

      one of the fields is called "product status". I am trying to produce a sub summary result that shows the unique count where a product status is set to one thing (lets say A) but the client does not have other products (records) set to one of two other statuses (lets say B & C)

      I have created now with the forums help the fields to calculate Unique Record count of "ClientID" and that is working perfectly - so thank you again but I can not work out how to get a result of unique "ClientId" where one record is at one status but the other ones are not at two different possible statuses!!! 

      Is this possible.

      Thank you in anticipation of your help


        • 1. Re: Sub Summary report search by unique ID and fields

          It's possible.

          (was tempted to stop there as that's all you asked...)

          An ExecuteSQL query comes to mind as a way to get this count with a WHERE clause that omits records from the client count when related Product counts have one of these other values, but perhaps this approach is simpler.

          I am assuming this relationship:

          Clients::__pkClientID = Products::_fkClientID  
          (this is the wrong relationship, BTW, if the same product can be linked to more than one client)

          In the Clients table, you can define a calculation field like this:

          IsEmpty ( FilterValues ( LIst ( "B" ; "C" ) ; List ( Products::Status ) ) )

          This value will be 1 (True) only if status "B" or status "C" is NOT specified for at least one related products record. Otherwise, it is zero (False).

          With this in place, you can perform a find with two requests on the products layout. The first request specifies status "A" in the Products table and the second request would be an Omit request specifying 0 in this new calculation field in the clients table.

          • 2. Re: Sub Summary report search by unique ID and fields

            Hi Phil,


            Thanks for the information.

            The Product only ever is connected to one Client so I believe this relationship is fine, but if you disagree please let me know.

            Thanks again