5 Replies Latest reply on May 4, 2016 6:37 PM by RickWhitelaw

    counting empty fields



      counting empty fields


           I'm finally switching from my Appleworks to Filemaker pro. I have a database of my photographic collection. As part of the database I have a field that indicates the value of each item in my collection. To make sure I have not accidently missed a value entry I have a summary field that checks all of the value fields and tells me how many are empty. In Appleworks I used the Count2 function. This function evaluated each value field and counted how many were empty by comparing each field to zero. So far as I can tell the Count function in Filemaker only lets me compare one field with another field rather than a string or numeric value. How do I go about counting empty fields?

        • 1. Re: counting empty fields

               count does not compare any field. It returns the number of fields that are not empty. So to count the number of fields you'd need to take the number of fields and subtract the count from that total.

               But what is not clear is how you have designed your databse. Are you counting individual fields, the same field in multiple records or different repetitions of a repeating field.

               From what little you have descrbied so far, it sounds like you should be counting the same field in multiple records.

          • 2. Re: counting empty fields

                 You are correct in your assumption. I want to check only one particular field in each record, and count only those that are empty. That tells me whether or not I have failed to enter a value in one or more records.

            • 3. Re: counting empty fields

                   Then you should be able to identify or add a second field in this table. Any field--such as a foreign key field used to link the table to other tables in relatinships or an auto-entered serial number field used as a primarykey--that is never empty and the field where you want to count the number of records where the field is empty.

                   Then this expression:

                   Count ( RelatedTable::NeverEmptyField ) - Count ( RelatedTable::SometimesEmptyField )

                   will compute the number of records wher eSometimesEmptyField is empty.

                   But note that this requires a a relationship set up in Manage | Database | Fields.

                   You would not need the relationship if you defined a summary field to count both of these fields and then you defined a calculation field that subtracted the summary fields:

                   sTotalNeverEmtpy - sTotalSometimesEmpty

                   The result will be a count of all records in the current found set where that field is empty.

              • 4. Re: counting empty fields

                Phil- I tried this calc which works great for all records in the full table.  How would you suggest narrowing to a portal set of records where the calc only counts emptys in a portal established thru value list?

                • 5. Re: counting empty fields

                  Phil's last reply to this thread is almost three years old . . .