8 Replies Latest reply on Jun 11, 2009 10:59 AM by dlabi

    summary of all records



      summary of all records


      hi, I'm sorry if my question is stupid but I can't figure out myself. I have checkbox in my database and I need to count all checked checkboxes. So I made a field with summary attribute where I selected my checkerbox's name Everything works how I expect but this counts only checkerboxes within found set of records. Is there simple way how to count all checked checkboxes instead of current list of records? Thank you.

        • 1. Re: summary of all records

          Use the new window command to open a new window.

          Find all records.

          Now your summary field gives you the grand total without changing the found set of your original layout.

          Capture the value in a variable and close the new window.




          Define a self join relationship using the cross product symbol (X) instead of equals.

          Now newselfjointableinstance::YourSummaryField should give you a grand total independent of your found set.



          • 2. Re: summary of all records

            Thank you very much. It works like a charm.


            Another thing I'm going after is count of all fields containing some text. I've tried this calculation Sum(If(table::field="mytext";1;0)) but this gives me question mark as result. Can you help me with this as well?



            • 3. Re: summary of all records

              Kudos to Laretta for teaching me the second trick in my first post the same day I shared it with you. :smileyvery-happy:


              A question mark result means that filemaker is unable to display a value. Since this is a sum, is there any chance that the field is too narrow to display the entire number? If that's the case, you should be able to click into the field in browse mode and see the result "pop out" for you to see and you can fix it by resizing the field.


              If that's not the case, I'd check your field definition again. Is it set to return a value of type number or another type? (This is controlled in a menu shown in the lower left corner of your specify calculation window of the field in question.)

              • 4. Re: summary of all records
                   I think size isn't issue it is long enough to show result even field definition is set to number. If I understand the formula correctly it should return a number, am I right?
                • 5. Re: summary of all records

                  I took another look at your posted expression.


                  Try it this way:




                  Now define a second field, define it as a summary field that computes "total of" the above calculation. Will that produce the value you want?


                  The Sum function expects a list of values to add up and the above expression feeds it a single value, either 1 or 0 as the result of the if function.

                  • 6. Re: summary of all records
                       Yes it is working fine. Thank you. I'm wondering if is always necesary to have one field for converting found text to 1 or 0 and another field for counting them? Isn't simpler solution?
                    • 7. Re: summary of all records

                      In that case, you don't need the extra calculation field.


                      "count of" summary fields and the Count() function both count the number of non-blank instances. So if you only want to know how many records have data in a given field and you don't care what kind of data is stored in it, then simply specify the data field in your "counts".

                      • 8. Re: summary of all records

                        And Kudos goes to....


                        As always brilliant answer. Thank you