6 Replies Latest reply on Feb 23, 2009 10:48 AM by comment_1

# Setting a calculation result to a minimum amount?

### Title

Setting a calculation result to a minimum amount?

### Post

Greetings all.  I'm using Filemaker for personal (as opposed to business) reasons, and so I'm not exactly what you'd call a math wiz.  I've come across a situation in designing a calculation field, but I'm not sure of the formula I need to do so.

Basically, what I want is to have the calculation field do some math, and return what the final result is.  No problem.  However, I also want to ensure that there is a minimum value for that result.

For example, the formula is X-Y, and the threshold is 20.  So 100-20 would return 80, 150-100 would return 50, but 50-45 would return 20, not 5.

I suspect I could do it fair enough with the if/then bit, but I suspect there may be a simpler operation in there I'm missing.

• ###### 1. Re: Setting a calculation result to a minimum amount?

Hi SteveMND,

Try using the Max function:

=Max[yourCalcField; Threshold]

(e.g. Max[(X-Y) ; 20] )

• ###### 2. Re: Setting a calculation result to a minimum amount?

i think max/min must use fields... not values

try if statement... in the Specify Calculation window, not the Edit Script window

• ###### 3. Re: Setting a calculation result to a minimum amount?
The max function takes either fields or values in the Specify Calculation window.
• ###### 4. Re: Setting a calculation result to a minimum amount?

maybe so, but given documentation states this... you may create some compatibility issues in the future...
field - any related fieldrepeating field, or set of non-repeating fields; or anexpression that returns a field, repeating field, or set of non-repeating fields.
Parameters in curly braces { } are optional.
• ###### 5. Re: Setting a calculation result to a minimum amount?

Howdy SteveMND,

Thanks for the post.

In your calculation window, simply type in:

If(   ABS(FieldX-FeildY)<20  ;  "20"  ;  ABS(FieldX-FieldY)   )

This checks to make sure the result is greater than 20, otherwise it just returns 20.

The ABS takes the absolute value to avoid negatives.  Leave them out if they ar inappropriate for your application.

No muss, no fuss, no need for simpler function, it's already simple.

Enjoy the day!

• ###### 6. Re: Setting a calculation result to a minimum amount?

hiatts wrote:
given documentation states this... you may create some compatibility issues in the future...
field - any related fieldrepeating field, or set of non-repeating fields; or anexpression that returns a field, repeating field, or set of non-repeating fields.

The documentation of the Max() function is quite poor. The fact is that:

Max ( value1 ; value2 { ; value3 ... })

is a perfectly valid syntax and it has been working that way since version 3, IIRC.