AnsweredAssumed Answered

Count IF Calculated Field Is Not Empty

Question asked by YankeeLaker on Apr 7, 2016
Latest reply on May 31, 2017 by kk_johnson

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...)

Outcomes