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