8 Replies Latest reply on Dec 9, 2016 8:47 AM by philmodjunk

# Calculation dependent on an empty field

Good afternoon FM community,

I'm fairly new to FM here (using Pro 14 with a hosted DB) and would like some help with a Calculation I'm trying to get correct. I have a field that will do a calculation based off other fields in the table.

Specifically I'd have Field A, B, and C in a Calculation looking something like this: ((Field A-Field B) x .01 )x 5 x Field C x 10,000 =

This Calculation works just fine but the issue we're having is when Field C does not have a value. The Calculation will return a 0. While this answer is correct, a "0" in this field actually means something different to the operators that use the database so I'm looking for a way for it to display nothing of the referenced field C is empty. Is this best done by an If or Case function? Thanks in advance for any help here!

• ###### 1. Re: Calculation dependent on an empty field

Yes, If or case could be used here:

If ( Not Isempty ( Field C ) ; ((Field A-Field B) x .01 )x 5 x Field C x 10,000 ; "" )

If you change "if" to "case" in the above example, you'll be using a case function instead of IF to get the exact same results. You can also omit the ; "" at the end and still get the same results.

1 of 1 people found this helpful
• ###### 2. Re: Calculation dependent on an empty field

makj1 wrote:

I'm looking for a way for it to display nothing of the referenced field C is empty. Is this best done by an If or Case function?

I always use Case() so I don't have to ask myself that question … ( Case() can do everything If() can, but not vice versa).

It doesn't matter “in this case” anyway since there is only one test:

Case (

not IsEmpty ( fieldC ) ;

( ( fieldA - fieldB ) x .01 ) * 5 * fieldC * 10,000

)

where the empty result for the case that fieldC is empty is implicit.

btw, you have three numeric literals in there, and you could do way with one pair of parentheses; why not write:

( fieldA - fieldB ) x .01 x 5 * fieldC * 500

If these literals have a special meaning, I'd do this (this time, let's use If() ):

If (

not IsEmpty ( fieldC ) ;

Let ( [

gravityConstant = .01 ;

fineMatterConstant = 5 ;

ageOfTheEarth = 10,000 ; // just kidding … ;-)

totalFactor = gravityConstant * fineMatterConstant * ageOfTheEarth

] ;

( fieldA - fieldB ) * fieldC * totalFactor

)

)

so these "magic numbers" are explained and documented.

1 of 1 people found this helpful
• ###### 3. Re: Calculation dependent on an empty field

Just to register an opposing preference, i prefer IF over CASE when there is clearly going to be only two possible result values, one for a True result and one for False. That just seems to work as an extra note to myself that we have a binary solution set for that calculation. If later analysis or changing needs require a change to Case, the needed edits are too trivial (to me at least) to make me start everything as a Case function.

But note that this is a very mild preference on my part. Should a new version of FileMaker deprecate IF in favor of doing everything via Case I would not protest over the change so long as they include conversion code to translate everything over to Case.

1 of 1 people found this helpful
• ###### 4. Re: Calculation dependent on an empty field

philmodjunk wrote:

But note that this is a very mild preference on my part.

Duly noted. What would coding be without the opportunity to cultivate an individual style.

btw, semantically I find the word "if" better; so why not deprecate If() as a function and rename Case() to If()?

Now that would be confusing …

• ###### 5. Re: Calculation dependent on an empty field

As a Comp Sci major in college, I worked with a number of programming languages and their "logic control" statements/functions were always one of the key differences. Case is one such tool that has two distinct "flavors". Some coding systems use Case only to select from a list of discrete values. Others, like FileMaker scripting use a list of Boolean expressions with the result determined by which expression first evaluates as True--which is potentially a much more powerful use of the concept.

I always liked the "condition" function in LISP which is very much the same as FileMaker's use of the Case function.

• ###### 6. Re: Calculation dependent on an empty field

Yes, this works great. I was wondering when would be the appropriate time to use If or Case. From my understanding, Case is better when evaluating multiple things whereas If is just 2 unless you do nested If statements. Thanks so much for your help!

• ###### 7. Re: Calculation dependent on an empty field

Thanks erolst! My formula was actually wrong here. The "5" was actually a reference to another field that the operator had represented by 5 (Awfully confusing don't you think?) so it was only 2 numeric literals. Either way, the people who actually use these functions are sticklers about having all the work shown so your example was a good way for me to get familiar with including those "special meanings." Thanks again!

• ###### 8. Re: Calculation dependent on an empty field
Case is better when evaluating multiple things whereas If is just 2 unless you do nested If statements.

Yep, and one of the reasons for using Case is to reduce the need for such nesting of these functions.