I am attempting to perform a calculation on a numeric field that sometimes contains NULL values. I would like the calculation (60*field1/field2) to give the result NULL for a record where that field1 is NULL, but instead I’m getting a 0. I tried if(field1 is NULL, NULL, 60*field1/field2), but that didn’t work either.
If you don’t need to use the Calculation field in anything else, you could have it return Text instead and use IFEMPTY(Field 1, "", 60*Field 1/Field 2)
Then you’ll get an empty string as the result or the value otherwise.
I was planning to run an average of the calculated value (one-to-many relationship). Since there are old records for which I won’t have an entry for field1, I want to exclude those records from the results, otherwise the average will be incorrectly low.
I’ll have to make some changes to the Math parser then because right now it returns either a String or a double. And a double can’t be nil.
This isn’t critical, so I can wait. Thanks for figuring this out for me!
Oakwine,
Maybe you can create another calculation field called “field1NOTempty” on your child form that returns a value of 0 if your 60*field1/field2 calculation field has the value of zero, and a value of 1 if the calculation field is not zero. Then on the parent form, instead of using an AVERAGE function on the calculation field, compute TOTAL(calculation field) divided by TOTAL(field1NOTempty).
— Mike
(I was away for a bit)
Mike, thanks, that was so close to what I needed. My ‘field1NOTempty’ needed to be calculated using Brendan’s IFEMPTY(field1,0,1) instead (because 0 and NULL are separate possible values for field1), then I could use your second formula correctly.
So now it works! Thanks everyone!
.
