Jump to content

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

Link to comment
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
Link to comment
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.

Link to comment
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.

Link to comment
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

Link to comment
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?

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.