by Lorne Walton, <lornew@wimsey.com>
FMPro's Functions,
Part 2: The Number & Trig Functions
BONUS: FUNCTNS.FP3
We continue the Beginner's Series with a look at FileMaker Pro v 3's built-in functions for handling numeric data. Open this issue's FUNCTNS.FP3 database bonus file written by ISO Contributors & Staff.
Subsequent installments will look at:
Aggregate/Summary/Repetition Functions
Financial Functions
Logical Functions
Status Functions
Where to use this?
A numeric function is a formula used in a calculation, intended to work upon a number. FileMaker does not prohibit you from applying these functions to non-number fields, but do some experimenting before you try this at home!
Functions are employed in field definitions (when defining a calculation field) and in ScriptMaker (to create calculations within a script). Since the FUNCTNS database is unprotected, you'll find examples using all of FileMaker Pro's functions by going into "Define Fields."
Because the distinction between Number Functions and Trigonometric Functions in FMPro is too fuzzy for me to understand (like why is "Log" a trig function while "Exp" is a number function?) we're going to cover both Number andTrigonometric functions in this lesson.
Abs (number)
Unfortunately, this has nothing to do with exercising your abdominal muscles. This is the Absolute Value function.
Abs(Number)
Abs(Number) is the value obtained by stripping the minus operator from the number, if there was one.
Abs (0) = 0
Abs (-3.14159265) = 3.14159265
Abs (2.7182818) = 2.7182818
Of course, functions are far more powerful when the argument (a.k.a.parameter, the number between the brackets) is not a constant, but rather a field name. Use the FUNCTNS file to check how this works for constants, then try some examples in your own database using field names.
Type some non-numeric characters into the number field in the FUNCTNS file, so you understand how FileMaker handles "garbage" entered into a field that's supposed to be numeric. This may be crucial to your understanding of a "bug" that will appear in one of your databases some day.
Abs (-3EX8.99%&^) = 38.99
Exp (number)
This is the simple mathematical function "e to the power of" where "e" is the base of the natural logarithms, or 2.718281828045... Therefore:
Exp (0) = 1... (e to the zero power)
Exp (1) = 2.71828... (e to the first power, or e itself)
Exp (-1) = 0.36789... (one divided by e)
and of course fractional arguments are accepted:
Exp (-.5) = 0.6065... (one divided by the square root of e)
Perhaps you think only mathematicians and engineers would be interested in this function. See the "Ln" function far below for an example that may convince you otherwise.
Int (number)
The Integer part of "number". Think of this as a simple "chop" that discards the fractional portion of the number.
Int (1.234) = 1
Int (-1.234) = -1
See "Round" and "Truncate" below for important differences.
Mod (number, divisor)
A function with two parameters. Returns the remainder when number is divided by divisor. In real life, the modulo function is mostly meant to deal with integer arguments.
Mod (10, 3) = 1
Mod (-10, 3) = -1
Mod (-10.15, 3) = -1.15
Mod ("Number + 1", "2")
returns 0 (false) only when the field Number contains an odd integer.
Note that Mod (Number, 0) is undefined because even FileMaker Pro cannot divide by zero. You can use fractional arguments for the divisor, but beware: because of inexact binary representation of decimal numbers:
Mod (10.1,.1) =.1!
NumToText (number)
Converts the number represented in "number" to a text string. Note that:
Set Field ("Data", "NumToText(3.4%-$29)")
puts the string "3.429" into the text field "Data". The non-numeric characters are lost because the argument is interpreted as numeric data even before the NumToText function gets its hands on it.
Random
No parameters needed here. Produces a "random" number between 0 and 1. In the FUNCTNS file, "Answer" is equal to:
Random * Number
This calculation:
Int (Random * 100) + 1
produces random integers between 1 and 100, while
Int (Random * 6) + Int (Random * 6) + 2
will simulate the rolling of a pair of six-sided dice. Note that this is not the same as:
2 * (Int (Random * 6)) + 2
because the first example generates two different random numbers. Nor is it the same as:
Int (Random * 6 + Random * 6) + 2
Try these out and make sure you understand the difference.
Round (number, precision)
Intriguing function, this. It's smart enough to ignore digits after the decimal point in the "precision" argument. And negative precisions are accepted:
Round (-3.14159265, 3) = -3.142
Round (-3.14159265, 3.1) = -3.142
Round (-3.14159265, 1) = -3.1
Round (-3.14159265, 0) = -3
Round (-3.14159265, -1) = 0
Round (-31415.9265, -2) = -31400
Sign (number)
Returns "1" for positive numbers and "-1" for negative numbers. The two following script steps are equivalent:
Set Field ("Result", "Abs (number)")
Set Field ("Result", "number * Sign (number)")
Sqrt (number)
Returns the square root of the argument.
Sqrt (2) = 1.41421...
Sqrt (-1) = undefined, as in real life.
Truncate (number, precision)
Precision represents the number of digits to which you want a number "chopped". The difference between Truncate and Round is illustrated by the following:
Truncate (9876.2375, 2.1) = 9876.23
Round (9876.2375, 2.1) = 9876.24
Truncate (9876.2375, -2) = 9800
Round (9876.2375, -2) = 9900
Truncate (9876.2375, -6) = 0
Round (9876.2375, -6) = 0
Now we will cross the invisible barrier into what FileMaker Pro likes to call "Trigonometric Functions". I'm departing slightly from the way the FUNCTNS file sets things up. FUNCTNS groups Financial and Trig functions together for display purposes. We'll look at the Financial functions in a later installment. There's relatively little to be said about the trig functions. You either need them or you don't.
Degrees (number)
An angle can be measured in either degrees or radians. "Pi" radians equals 180 degrees. "Two pi" radians = 360 degrees.
Degrees ("3.14159265") = 179.999...
The FUNCTNS file shows an example that combines two trig functions:
Degrees ("Atan (1)") = 45
because the arctangent of 1 is pi divided by 4 in radians, which in degrees is 45.
Atan (number)
The angle whose tangent is "number".
Set Field ("Pi", "4 * Atan(1)")
provides one way to store the value of pi in a field, perhaps a global numeric field, for future use.
Cos (number)
The cosine of "number", which must be expressed in radians:
Cos ("Atan(1) * 4") = Cos ("PI") = Cos ("Radians(180)") = -1
Cos ("Radians(90)") = 0
Note that the FUNCTNS file sets up a Cos (Radians(x)) function so that degrees may be entered as the argument rather than radians. You may wish to do the same, since most of us don't think of angles in terms of radians.
Ln (number)
The natural log of "number". For those of you whose math is a little rusty, Ln is the "inverse" of Exp. So:
Exp ("Ln(x)") = x
Ln ("Exp(x)") = x
Except where "x" is less than or equal to zero:
Ln ("Exp(0)") = 0
Exp ("Ln(0)") is undefined, because there is no "Ln(0)".
Ln ("1") = 0
Ln ("2.7182818") = 1
Note also that Ln (x^n) is identical to n*Ln(x). FileMaker has no exponentiation operator. Suppose you want to compute compound interest, using the formula A = P * (1 + r) ^ n: that's Amount equals (1 + interest rate per compounding period) raised to the number of compounding periods, times principal. You want to compound monthly, and have fields named:
Amount (the desired result)
Principal (the starting amount, say $1,000)
Rate (annual rate, say 6.5)
Years (number of years of compounding)
In the Define Fields dialog, define Amount as a calculation:
If ("Years <= 0 or Rate <= 0", 0, "Principal * Exp(Years * 12 * Ln(1 + Rate / 1200))")
Log (number)
The base 10 logarithm of "number". There's nothing you can do with this that you can't do with "Ln", since there is a simple linear relationship between the two:
Log ("number") = Ln("number") / Ln("10")
PI
Okay, so you didn't need the trick about 4 * Atan(1).
PI = 3.14159265
Cos ("PI / 2") = Cos ("Radians (90)") = 0
Radians (number)
Converts degrees to radians. The "inverse" of the Degrees function.
Radians ("Degrees (Number)") = Degrees ("Radians (Number)") = Number
Sin (number), Tan (number)
If you understood Cos, you already have the sine and tangent functions figured. Don't forget that the argument is expected to be in radians.
Tan ("PI") = Tan ("Radians (180)") = 0
Sin ("PI / 2") = Sin ("Radians (90)") = 1
Tan ("PI / 2") = Tan ("Radians (90)") = undefined (infinite)
Where to get help?
FileMaker Pro's online help provides clear, concise and accessible descriptions of all the functions. To truly understand how the functions work, use Mr. Fortier's FUNCTNS file. When you get beyond that, and comprehend how the FUNCTNS file is put together, you'll be well on your way to becoming a FileMaker Pro!
## END ##
This article is part of a series aimed at those who are not yet experienced with FileMaker Pro. If you have a novice-level request that you'd like to see addressed, email the author at lornew@wimsey.com
The author is grateful to Jim Fortier and Bob Cusick for their technical reviews of these articles.