I have a field that is defined as the sum of 3 other fields in the record. However, I am trying to avoid misleading entries in the calculated field caused by one or more of the contributing fields being empty. Does anyone know of an easy way to calculate a field from 3 other fields only if none of the contributing fields is empty?

Marcel

Hi Marcel

try this in your total field

Case(not IsEmpty(field1) or not IsEmpty(field2) or not IsEmpty(field3);field1+field2+field3;"")

Phil

When checking for "none of the contributing fields is empty", the conditions should be cumulative:

Case (

not IsEmpty ( field1) and not IsEmpty ( field2) and not IsEmpty ( field3 ) ;

Sum ( field1 ; field2 ; field3 )

)

Or:

Case (

not ( IsEmpty ( field1) or IsEmpty ( field2) or IsEmpty ( field3 ) ) ;

Sum ( field1 ; field2 ; field3 )

)

Or (since the 3 fields are numbers)

Case (

field1 and field2 and field3 ;

Sum ( field1 ; field2 ; field3 )

)

An empty default result is, of course, redundant.

----

EDIT:

I should have pointed out that the last variant assumes zero is NOT a valid entry into any one of the three fields; it considers a field with a value of zero as empty.

Edited March 22, 200619 yr by Guest

Phil doffs his cap in deference }:(

Just don't call me Gov'...

Alternatively,

Case (

num1 * num2 * num3 = 0 ; 0 ;

Sum ( num1 ; num2 ; num3 )

)

Since any number multiplied by zero results in a product of zero, and since FM treats a null value as zero, this calculation will also work.

If you want your "total" field to always display a value, then Deactivate the "Do not evaluate if all referenced fields are empty" option. The "total" field will always display either a zero or the totaled value.

Activating the ""Do not evaluate if all referenced fields are empty" option will result in a null value when ALL of your constituent fields are empty. Otherwise, it will display either a zero or the totaled value.

... just a thought.

A zero is not the same as empty - see my edit above.

Mr. Comment ... a respectful (and non-mathematical) "Absolutely!"

I had a couple of "assumptions" in my draft post, but I deleted them. It seems that implementing a suggested solution will often depend on the behaviors preferred by the user ... where the theoretical bleeds into the practical aspects, you know. I think that's one reason why I post so seldom.

You could have called me on the first Case argument resulting in "0," but you didn't ... thank you. The user could insert any preferred behavior at that point, though.

I liked your last iteration best ... very efficient.

I think I should take a leaf out of Oneguy's book and think more and post less

Phil }:(

iPhil ... don't you dare post less. It takes all of us for any of us to learn anything.

And I would miss your sweet face!!

...although you SHOULD pull that tongue in once in a while. :jester:

Phil is back.

Deeply touched by your kind words I have reviewed my early retirement from the forum and have given some thought to what OneGuy said

I think he is absolutely right!

People will learn more when they see my reply to their questions being corrected by those who really know the answer than ever they would with a straightforward reply. In future I want people to look upon my posts as a service whereby I use the range of my knowledge to cleverly widen the area of discussion.

There will be no charge for this service.

Regards to all

Phil :hello:

Oh and I have taken on board what LaRetta said about 'no tongues' and changed my avatar accordingly

DOooooooooh!

I went into my profile and changed ny avatar and my signature and neither are showing now.

Says it all I guess

Phil

TEST POST

People will learn more when they see my reply to their questions being corrected by those who really know the answer than ever they would with a straightforward reply. In future I want people to look upon my posts as a service whereby I use the range of my knowledge to cleverly widen the area of discussion.

You might be over-thinking this, iPhil ... could you try expressing this through the Table Occurrence Graph?

Probably

Phil

thanks, guys. the case(not isempty....) approach worked very well.

marcel