5 Replies Latest reply on May 31, 2017 11:26 AM by kk_johnson

    Count IF Calculated Field Is Not Empty

    YankeeLaker

      I think this should be easy, but I don't know how to do it. I keep a database for our association, in which each record (member) has an input screen. One new function I have added to this screen, is the ability to document and perform a simple calculation for purchasing a book, in which we capture the number of books ordered (selected from a drop-down) multiplied by a price (drop-down), equals a calculated total. No problem. The calculated total cannot be modified directly--it is only populated by being driven by the two input fields (number of books and cost per book).

       

      I have a report that needs to show how many books were ordered (Summary total of number of books ordered field) [that works correctly], at what total cost across all orders (Summary total of the calculated cost field) [that also works correctly], by how many members.

       

      19 books were bought by 9 members at a total cost of $475.00

       

      It is the "by how many members" that I don't know how to do. I had thought I could do something like a "Count if" that field was greater than zero, but there is no CountIF. I can't just sort on that field not empty and report that count, because the report needs to include other information for which that sort would not be correct. I had also created the calculation field so that it would be empty unless there were entries in the "how many" and "at what price" fields, that would then drive a calculation to change the empty field. That actually worked using just the simple count function, because the empty fields were ignored. But, for some reason I cannot figure out, when I create a new member (create a new record in the database), then that intended-to-be-empty calculation field puts a zero as a placeholder rather than just leaving it empty. But, now that zero gets counted and makes it look like that new member bought a book, when they didn't. (In the Inspector, I have left the placeholder text empty, which shows empty in all of the records which had already been created before I added this field to the database. But, when I create a new record in the database, that field gets populated with a zero...)

        • 1. Re: Count IF Calculated Field Is Not Empty
          erolst

          Create a calculation field as

           

          Case ( thatFieldYouWantToCount > 0 ; 1 )

           

          and summaryCount that.

           

          Or make the calculation just


          thatFieldYouWantToCount > 0


          and summaryTotal (sum) it.


          If you just need that number within a report, consider calculating it inside the report script using ExecuteSQL(), put it into a $$globalVar and put that onto the layout. Saves you a field in the schema.

          • 2. Re: Count IF Calculated Field Is Not Empty
            DavidJondreau

            I think your easiest solution to figure how to get the 0 out of new records.

             

             

            What's that calculation?


            David

            • 3. Re: Count IF Calculated Field Is Not Empty
              Mike_Mitchell

              David is correct. If you set the result to be "" (null) when there are no items, then a simple Count type summary field will give you the total.

              • 4. Re: Count IF Calculated Field Is Not Empty
                YankeeLaker

                David Jondreau and  Mike_Mitchell: I was using a drop down list of possible values. By adding null to the top-most entry in the drop down lists for the number of books field and the price per book field, I was able to get rid of the automatic entry of a zero in the calculated field, and then the use of Count on that field worked correctly. I had noodled that out after having sent the question, but you are both right that that was easy to do.

                 

                I had already already figured that out, when I received the response by erolst. I also tried his suggestion to create a calculation field using Case for the value of my field >0, then did a count on that field. It worked perfectly as well.

                 

                Thanks all! My problem is solved!

                • 5. Re: Count IF Calculated Field Is Not Empty
                  kk_johnson
                  MakeModelCategoryDisplay SizeSerial NumberTL Asset TagCurrent Office LocationOfficeStatusFormula
                  MSI Dominator PC_Laptop GT722QE211USK101172USWLTLPUSWLTLP01172-1f6&"-"&COUNTIF($F$6:$F6,"="&f6)
                  MSI Dominator PC_Laptop GT722QE211USK201402USWLTLPUSWLTLP01402-1
                  MSI Dominator PC_Laptop GT722QE211USK301403USWLTLPUSWLTLP01403-1
                  MSI Dominator PC_Laptop GT722QE211USK401172USWLTLPUSWLTLP01172-2
                  MSI Dominator PC_Laptop GT722QE211USK501407USWLTLPUSWLTLP01407-1
                  MSI Dominator - Wireless PC_Laptop GT722QE211USK601172USWLTLPUSWLTLP01172-3
                  MSI Dominator PC_Laptop GT722QE211USK701172USWLTLPUSWLTLP01172-4
                  MSI Dominator PC_Laptop GT722QE211USK801412USWLTLPUSWLTLP01412-1

                   

                   

                  So what I trying to acheive is for each line return the asset tag number and time the number is found going down each row.  By this I mean, 01172 is found once between row 1 and 1 from row 1 and 3, 12 times from youw 1 and row d, etc.

                   

                  Thx

                  KJ