Calculate a field if other fields not empty

This topic is 5734 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Share on other sites

Hi Marcel

try this in your total field

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

Phil

Share on other sites

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 by Guest
Share on other sites

Phil doffs his cap in deference }:(

Share on other sites

Just don't call me Gov'...

Share on other sites

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.

Share on other sites

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

Share on other sites

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.

Share on other sites

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

Phil }:(

Share on other sites

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

Share on other sites

And I would miss your sweet face!!

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

Share on other sites

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

Share on other sites

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

Share on other sites

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

Share on other sites

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

marcel

Share on other sites

This topic is 5734 days old. Please don't post here. Open a new topic instead.

Create an account

Register a new account