2 Replies Latest reply on Jun 28, 2011 11:20 AM by DavidConner1321

    Behavior of an empty numerical calculation field during search

    DavidConner1321

      Title

      Behavior of an empty numerical calculation field during search

      Post

      I was hoping for some insight into the following issue.

      I am working with Filemaker 11 Advanced.

      I have a table with a calculation field using the Sum(field) calculation. The result is set to number and sums values from related number fields. Depending on the related field the result may be a decimal number or nothing (i.e. no related fields so no value). The numerical value from the calculation is correct. However, I have problems when performing searches on that field. For example, I can find all the values that are >0. However, the search fails if I search for all records with no value (=), even though there are many records with no value. 

      I have tried changing the calculation to a conditional calculation that results in 0 if the Sum(field) calculation does not result in a value greater than 0. The expected result is displayed, but the search still fails. For example, if I search for values that are <= 1, it returns all records with 1, but not those records in which the value is 0. I have double-checked to make sure all the related fields are defined as number fields. I had thought originally that it may have been a text vs. number issue.

      Any ideas? 

        • 1. Re: Behavior of an empty numerical calculation field during search
          philmodjunk

          Using a small test file, I cannot reproduce this issue.

          I defined a table, Child with two number fields: ID, value
          I defined a 2nd table, parent, with two fields: ID (Serial number), and cSum, calculation field with Sum ( Child::Value ) as it's calculation.

          I related the tables with: Parent::ID = Child::ID with allow creation enabled for Child so that I could use a portal to child to generate some records.

          I created two parent records. Using a portal, I added two related records for ID = 1. cSum correctly computed the total. I created a second parent record and did not add any related records. Performing a find by putting = in the cSum field correctly found the 2nd record. I then added a child record with a blank value field for Parent record, ID = 2 and repeated this test. I was still able to find the record where cSum was empty.

          Can you spot any differences between your file and mine?

          • 2. Re: Behavior of an empty numerical calculation field during search
            DavidConner1321

            Thank you for the quick response. I reproduced your test file to make sure that I understood your test and, of course, it worked as you said. It did, however, lead me to realize that my file structure was  different and made me realize the mistake I made.

            I was setting up relationships between pre-existing tables that were created independently. The parent table had a comprehensive list of possible record IDs. There were multiple child tables which could have a subset of that parent list of IDs. Each child table could have a different subset of parent IDs. The child tables could have multiple records with the same ID and I wanted to sum the values from the child records with the same ID and then view the data from multiple child tables from the perspective of the parent table. I had created the Sum(field) in the child tables based on a self relationship.

            From the perspective of the parent table, an empty child Sum(field) could mean that there were no related records in that particular child table rather than that the sum was nothing. Then, of course, it was impossible to find related records that didn't exist.

            I am probably explaining this poorly, but your comments were very helpful to me to understand the mistake in my design. Thank you.