Calculation Functions
Abs()
Category: Number
Syntax: Abs ( number )
Parameters:
number—Any expression that resolves to a numeric value.
Data type returned: Number, Time
Description:
Returns the absolute value of number; absolute value is always a positive number.
Examples:
Atan()
Category: Trigonometric
Syntax: Atan ( number )
number—Any expression that resolves to a numeric value.
Data type returned: Number
Description:
The arc tangent of a number is the angle (measured in radians) whose tangent is the specified number. The range of values returned by the Atan function is -(pi/2) to pi/2.
If Atan (x) = y, then Tan (y) = x.
Atan (x) = Atan (-x).
Examples:
Atan (0) = 0
Atan (1) = .785398163
which is pi/4 radians, or 45 degrees.
Average()
Category: Aggregate
Syntax: Average ( field {; field...} )
Parameters:
field—Any related field, repeating field, or set of non-repeating fields that represent a collection of numbers. Parameters in curly braces { } are optional and may be repeated as needed, separated by a semicolon.
Data type returned: Number
Description:
Returns a numeric value that is the arithmetic mean of all non-blank values in the set designated by the parameter list. The arithmetic mean of a set of numbers is the sum of the numbers divided by the size of the set. Blank values are not considered as part of the set.
When the parameter list consists of two or more repeating fields, Average() generates a repeating field in which the corresponding repetitions from the specified fields are averaged separately. So, if a field Repeater1 has two values, 16 and 20, and another field, Repeater2, has two values, 14 and 25, Average (Repeater1; Repeater2) would return a repeating field with values 15 and 22.5.
Examples:
Case()
Category: Logical
Syntax: Case ( test1; result1 {; test2; result2; defaultResult...} )
Parameters:
test(n)—An expression that yields a Boolean result.
result(n)—The value to return if corresponding test is true.
defaultResult—The value to return if all tests are false. Parameters in curly braces {} are optional and may be repeated as needed, separated by a semicolon.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
The Case function returns one of several possible results based on a series of tests.
Each test expression is evaluated in order, and when the first true expression (one that resolves to a Boolean 1) is found, the value specified in the result for that expression is returned. The function stops evaluating as soon as it finds a true test.
The default result at the end of the parameter list is optional. If none of the tests evaluate to True, the function returns the value specified for defaultResult. If no default result is specified, the Case function returns an “empty” result. If you believe that one of the tests in the Case should always be true, we recommend using an explicit default case, possibly with a value of “default” or “error” to assist in error trapping.
Consider using hard returns in long Case() statements to make them more readable, and indent lines with tabs, as shown previously. (Note that this example makes repeated calls to Get(SystemLanguage); in practice it might be better to use Let() to make a single call to Get(SystemLanguage) so that it needs to be evaluated only once.)
In the last example, while all three tests resolve to true, only the first line is executed and its result returned. Using the Case() function, with its “short-circuiting” feature, can help with performance tuning.
Examples:
Case (
Get(SystemLanguage) = "English"; "Welcome";
Get(SystemLanguage) = "French"; "Bienvenue";
Get(SystemLanguage) = "Italian"; "Benvenuto";
Get(SystemLanguage) = "German"; "Willkommen";
Get(SystemLanguage) = "Swedish"; "Välkommen";
Get(SystemLanguage) = "Spanish "; "Bienvenido",;
Get(SystemLanguage) = "Dutch"; "Welkom";
Get(SystemLanguage) = "Japanese"; "Irashaimasu" ;
"Sorry... not sure of your language." // default value
)
Returns a welcoming message in the language determined by the Get (SystemLanguage) function.
Case (
SalesTotal < 10; .1;
SalesTotal < 50; .2;
SalesTotal < 100; .3;
.35
)
Returns .1 when the value in the SalesTotal field is 5, and returns .2 when the value in the SalesTotal field is 12.
Ceiling()
Category: Number
Syntax: Ceiling ( number )
Parameters:
number—Any expression that resolves to a numeric value.
Data type returned: Number
Description:
Returns number rounded up to the next integer.
One common use for the Ceiling function is finding out how many pages will be required to print x items if y items fit on a page. The formula for this is Ceiling ( x / y ). For instance, if you have 16 items, and 5 can print per page, you would need Ceiling ( 16/5 ) = Ceiling ( 3.2 ) = 4 pages.
Ceiling ( 1.05 ) = 2
Ceiling ( -4.6 ) = -4
Ceiling ( 3 ) = 3
Choose()
Category: Logical
Syntax: Choose ( test; result0 {; result1; result2...} )
Parameters:
test—An expression that returns a number greater than or equal to zero.
result(n)—The value returned or the expression that is evaluated based on the result of the test. Parameters in curly braces { } are optional and may be repeated as needed, separated by a semicolon.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
Returns one of the result values according to the integer value of test. FileMaker evaluates test to obtain an index number, which is used to then select the corresponding ordinal result.
The Choose function is a 0-based list. Choose ( 1; "a"; "b"; "c" ) will return "b".
Any fractional value of test is ignored (as opposed to rounded) when obtaining the index number. Choose ( 1.9; "a"; "b"; "c" ) will return "b".
If the index value returned by test exceeds the number of results available, the Choose function will not return any result—The field will be blank as opposed to having a “?” in it. There is no way to define a default value to use when the index value exceeds the number of results available.
The following formula converts decimal values to fractional notation, rounded to the nearest eighth. Assume an input from a field (or parameter), myNumber.
Let ([
n = myNumber;
int = int ( n );
decimal = mod ( n; 1 );
numberOfEighths = Round ( decimal/.125; 0 );
intDisplay = Case ( Abs ( int ) > 0; int & Case ( Abs ( decimal ) > 0; " - "; "" ); "" );
fraction = Choose( numberOfEighths;
Floor ( n );
intDisplay & "1/8";
intDisplay & "1/4";
intDisplay & "3/8";
intDisplay & "1/2";
intDisplay & "5/8";
intDisplay & "3/4";
intDisplay & "7/8";
Ceiling ( n )
) // end choose
]; // end variables
fraction
)
If myNumber contained 3.45, this function would return 3 - 1/2.
Combination()
Category: Number
Syntax: Combination ( setSize; numberOfChoices )
Parameters:
setSize—Non-negative numeric value (or an expression that results in one).
numberOfChoices—Non-negative numeric value (or an expression that results in one).
Data type returned: Number
Description:
Returns the number of ways to uniquely choose numberOfChoices items from a set of size setSize.
The formula used to determine the Combination value is n! / (n-x)! * x!, where n = set size, x = number of choices.
The numbers returned by the Combination function are the coefficients of the binomial expansion series. Useful in statistics, combinatorics, and polynomial expansions, the values returned by this function are referred to as combination coefficients. They form Pascal’s triangle.
(x + y)4 = 1x4 + 4x3y + 6x2y2 + 4xy3 + 1y4
Combination ( 4; 0 ) = 1
Combination ( 4; 1 ) = 4
Combination ( 4; 2 ) = 6
Combination ( 4; 3 ) = 4
Combination ( 4; 4 ) = 1
Cos()
Category: Trigonometric
Syntax: Cos ( number )
Parameters:
number—Any expression that resolves to a numeric value that represents an angle measured in radians.
Data type returned: Number
Description:
Returns the cosine of the angle represented by the value of the parameter measured in radians. Cos is a periodic function with a range from -1 to 1.
In any right triangle, the cosine of the two non-right angles can be obtained by dividing the length of the side adjacent to the angle by the length of the hypotenuse.
You can convert an angle measured in degrees into radians by using the Radians() function, or by multiplying the value by Pi/180. One radian is slightly more than 57 degrees.
Cos (0) = 0
Cos (Pi / 4) = .707106781 (which is 1 / Sqrt (2))
Cos (Radians (60)) = .5
Count()
Category: Aggregate
Syntax: Count ( field {; field...} )
Parameters:
field—Any related field, repeating field, or set of non-repeating fields; or an expression that returns a field, repeating field, or set of non-repeating fields. Parameters in curly braces { } are optional and may be repeated as needed, separated by a semicolon.
Data type returned: Number
Description:
Returns a count of the fields (or repetitions, in the case of repeating fields) in the parameter list that contain non-blank values.
When the parameter list consists of two or more repeating fields, Count() returns a repeating field in which the corresponding repetitions from the specified fields are counted separately. So if a field Repeater1 has three values, 16, 20, and 24, and another field, Repeater2, has two values, 14 and 25, Count (Repeater1; Repeater2) would return a repeating field with values 2, 2, and 1.
FileMaker 8 modifies the Count() function to take portal and field context into account. For example, in a scenario where a Customer table occurrence is related one-to-many with an Invoice table occurrence that is then related one-to-many to a LineItem table occurrence, evaluating a Count() function from Customer to LineItem will yield all LineItem records for the current Customer record if the user’s context is on the Customer TO; but if the user’s context is on the Invoice TO (if a user clicks in an Invoice portal row, say, or a script navigates into the portal using a Go To Field script step), Count(LineItem::field) will return a count of just those line items related to the currently selected invoice. Given that calculation fields explicitly specify their evaluation context, this issue is most likely to arise in scripting.
DatabaseNames
Category: Design
Syntax: DatabaseNames
Parameters:
None
Data type returned: Text
Description:
Returns a carriage return–delimited list of currently open databases (filenames), whether open as a client of another machine or open locally.
Note that on Windows, the .fp7 extension is not returned. This means that the function is consistent across both platforms.
Use caution when checking for hard-coded strings in calculations. If someone renames a file, any calculation containing the old value will no longer behave as before. Get (FileName) can be used in conjunction with a startup script to see whether a filename has been changed, and developers may want to consider establishing a centrally controlled custom function or variable for such checks.
Examples:
In a circumstance where three files are open, Customers, Invoices, and Invoice Line Items, DatabaseNames() will return Customers¶Invoices¶Invoice_Line_Items.
PatternCount ( DatabaseNames; "Customers" )
Returns 1 if the Customers database is open.
Date()
Category: Date
Syntax: Date ( month; day; year )
Parameters:
month—The month of the year (a number from 1 to 12).
day—The day of the month (a number from 1 to 31).
year—The year (four digits between 0001 and 4000). The year parameter should be passed as a four-digit number; if not, FileMaker will not infer or prepend a century. (1/10/05 = January 10, 0005.)
Note that regardless of your system settings, this function requires that its parameters be listed strictly in order: month, day, year. Localizations settings will not apply.
Data type returned: Date
Returns a valid date of data type date represented by the three parameters.
Values for month and day outside of normal ranges will be interpreted correctly. For instance, a month value of 13 returns a date in January of the following year. A day value of 0 returns the last day of the preceding month.
Parameters can be calculation expressions or fields; as long as the final result is valid, the date function will work correctly. Dates are stored internally as numbers (a unit of “1” represents one day); whole number math can be done on dates.
Be sure when returning dates as the result of calculation fields that you specify a calculation result of Date. If you were to define a field as Date ( 1; 1; 2000) and were to set the calculation result as Number, then you would see 730120 as the calculation value. Internally, FileMaker stores dates as the number of days since January 1, 0001, and that internal representation is returned if you incorrectly specify the return data type.
Examples:
Day()
Category: Date
Syntax: Day ( date )
Parameters:
Date—Any valid date (1/1/0001–12/31/4000), expression that returns a date, or field that contains a date.
Data type returned: Number
Description:
Returns the day of month (1–31) for any valid date.
Examples:
Day ( "1/15/2000" ) = 15
Other functions can be referenced in Day():
Day ( Get ( CurrentDate ))
returns the day of month for today.
Parameters in Day() can be calculated:
Day ( Get ( CurrentDate ) - 90 )
Returns the day number for the date 90 days before today, which may not be the same as today’s day number.
DayName()
Category: Date
Syntax: DayName ( date )
Parameters:
Date—Any valid date (1/1/0001–12/31/4000), expression that returns a date, or field that contains a date. The parameter can also be the numeric representation of a date (1–1460970).
Description:
Returns a text string containing the name of a weekday for any valid date (1/1/0001–12/31/4000).
Note that the year is optional. DayName ( "12/1" ) will return the day name for December 1st in the current year.
Examples:
DayNameJ()
Category: Date
Syntax: DayNameJ ( date )
Parameters:
Date—Any calendar date.
Data type returned: text (Japanese)
Description:
Returns a text string in Japanese that is the full name of the weekday for date.
To avoid errors when using dates, always use four-digit years. FileMaker will not infer or prepend a century on two-digit dates. (1/10/05 = January 10, 0005.)
Examples:
DayOfWeek()
Category: Date
Syntax: DayOfWeek ( date )
Parameters:
Date—Any valid date (1/1/0001–12/31/4000), expression that returns a date, or field that contains a date. The parameter can also be the numeric representation of a date (1–1460970).
Data type returned: Number
Description:
Returns a number from 1 to 7, representing the day of week (Sunday = 1, Saturday = 7) for any valid date (1/1/0001–12/31/4000).
DayOfWeek() can be used to perform conditional tests on days of week without concern for localization issues. The number returned is always the same regardless of what language version of FileMaker Pro the user is using. The number value returned by DayOfWeek() can also be used in mathematical calculations.
Note that the year is optional. DayOfWeek ("12/1") will return the appropriate integer for December 1st in the current year.
Examples:
DayOfYear()
Category: Date
Syntax: DayOfYear ( date )
Parameters:
Date—Any valid date (1/1/0001–12/31/4000), expression that returns a date, or field that contains a date. The parameter can also be the numeric representation of a date (1–1460970).
Data type returned: Number
Description:
Returns a number representing the day of year (1–366) for any valid date (1/1/0001–12/31/4000).
You can use the DayOfYear function to check whether a particular year is a leap year. Given a field Year, the formula DayOfYear ( Date ( 12; 31; Year )) would return 366 if Year was a leap year, and 365 if it wasn’t.
Note that the year is optional. DayOfYear ("12/1") will return the appropriate integer for December 1st in the current year.
Examples:
Degrees()
Category: Trigonometric
Syntax: Degrees ( number )
Parameters:
number—A number representing an angle measured in radians.
Data type returned: Number
Description:
Converts an angle measured in radians to its equivalent in degrees. There are 2×Pi radians in 360°, so 1 radian is just over 57°.
Another way to convert radians to degrees is to multiply by 180/Pi.
Examples:
Div()
Category: Number
Syntax: Div ( number; divisor )
Parameters:
number—Any expression that resolves to a numeric value.
divisor—Any expression that resolves to a numeric value.
Data type returned: Number
Description:
Returns the quotient resulting from the division of the numerator number by the denominator divisor.
The Div() function is equivalent to Floor ( numerator / denominator ).
To obtain the remainder when a numerator is divided by denominator, use the Mod function.
Examples:
Evaluate()
Category: Logical
Syntax: Evaluate ( expression {; [ field1; field2; ...]} )
Parameters:
expression—Any valid calculation formula, field containing a valid formula, or expression returning a valid formula.
field(n)—A list of optional fields that can then serve to trigger a re-evaluation; the expression re-evaluates when any of the included fields are updated.
Parameters in curly braces { } are optional. The optional field list must be enclosed by square brackets when there are multiple parameters.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
The Evaluate() function returns the results obtained by evaluating expression.
The optional second parameter is a list of fields on which the calculation becomes dependent. When any of those fields are modified, the Evaluate() function re-evaluates the expression specified by the first parameter.
→ For more in-depth examples of the Evaluate() function, see Special Edition Using FileMaker 8, Chapter 14, “Advanced Calculation Techniques.”
The Evaluate() function expects that the first parameter passed to it is a string that contains a formula of some sort. If you are passing a literal string, as in the fourth of the following examples, using the Quote() function ensures that any quotation marks in the formula itself are properly encoded. If the first parameter is a field name or an expression, that field or expression is expected to return a formula, which the Evaluate() function then evaluates. In a nutshell, if the first parameter is not surrounded by quotation marks, the result of whatever field or expression is provided is evaluated.
Note that the execution of the expression does occur—in other words, do not think of Evaluate() as a “testing function” making use of its own memory space. If your expression modifies a global or local variable (using the Let() function), any applicable changes will be applied. If you need a calculation “scratch pad,” consider using the Evaluate Now function of the FileMaker Advanced Data Viewer—though there again, any “side-effect” modifications of variables will occur “for real,” a good example of why modifying variables from within calculations can be a questionable practice.
Examples:
EvaluationError()
Category: Logical
Syntax: EvaluationError ( expression )
Parameters:
expression—Any FileMaker calculation formula.
Data type returned: Number
Description:
Returns whatever error code an expression may generate if executed. If the expression executes properly, a zero (no error) will be returned.
Note that the expression is executed if it is syntactically correct. If your expression manipulates local or global variables, they will be affected by this EvaluationError() check. Note also that there are two kinds of errors returned: syntax errors, where the expression cannot be executed (and will not be executed by EvalutionError()), and runtime errors, where the expression is valid but, for example, a field or record may be missing.
Important: The EvaluationError() function must enclose the Evaluate() function to return any syntax errors.
Examples:
Exact()
Category: Text
Syntax: Exact ( originalText; comparisonText )
Parameters:
originalText—Any text expression, text field, or container field.
comparisonText—Any text expression, text field, or container field.
Data type returned: Number
Description:
Compares the contents of any two text or container expressions. This function is case sensitive. If the expressions match, the result is 1 (True); otherwise, the result is 0 (False). For container fields, not only must the data be the same, but it must also be stored in the same manner (either embedded or stored by file reference). Note that for container fields it is the text representations of the file references being compared.
Remember that Exact() considers the case of the two strings, whereas the = operator does not. If you need to compare two values in a conditional test, consider using
If ( Exact ( A; B ); ... instead of If ( A = B; ....
Examples:
Exp()
Category: Number
Syntax: Exp ( number )
Parameters:
number—Any expression that resolves to a numeric value.
Data type returned: Number
Description:
Returns the value of the constant e raised to the power of number. The Exp() function is the inverse of the Ln() function.
To return the value of the constant e itself, use Exp (1), which returns 2.7182818284590452. You can use the SetPrecision() function to return e with up to 400 digits of precision.
Examples:
Extend()
Category: Repeating
Syntax: Extend ( non-repeatingField )
Parameters:
non-repeatingField—Any non-repeating field (a field defined to contain only one value).
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
Allows a value in non-repeatingField to be applied to every repetition within a repeating field (most commonly a calculation field). Without using the Extend() function, only the first repetition of a repeating calculation field will properly reference the value in non-repeatingField.
Examples:
Given a number field RepCommission, defined to hold three repetitions, a non-repeating number field (SalePrice), and repeating calculation field (SalesCommission), defined as follows:
Round ( RepCommision * Extend ( SalePrice ); 2 )
Without the Extend() function, only the first repetition of SalesCommission would have returned the correct value.
External()
Category: External
Syntax: External ( nameOfFunction; parameter )
Parameters:
nameOfFunction—The name of the external function being called.
parameter—The parameter that is being passed to the external function.
Data type returned: Depends on the external function
Description:
The External() function is used to call functions defined within a plug-in. A plug-in must be installed (located in the Extensions folder) and enabled (under the Plug-Ins tab of Preferences) for you to have access to its functions.
→ For more detail on plug-ins, see Special Edition Using FileMaker 8, Chapter 24, “Deploying and Extending FileMaker."
The function name and parameter syntax for an external function is defined by the plug-in developer. When calling external plug-ins, be sure to use the exact syntax specified in the documentation for the plug-in. The external function parameter can generally be passed as a field, as long as the contents of the field conform to the requirements set forth by the plug-in developer. Because only a single parameter may be passed to a function, parameters often consist of delimited lists of data, which are then parsed and interpreted inside the plug-in.
Examples:
External ( "myPlugin"; "param1|param2|param3" )
External ( "myPlugin"; myParmField )
Factorial()
Category: Number
Syntax: Factorial (number {; numberOfFactors })
Parameters:
number—Any expression that resolves to a positive integer.
numberOfFactors—Any expression that resolves to a positive integer that represents how many factors to include in the multiplication.
Parameters in curly braces { } are optional.
Data type returned: Number
Description:
Returns the factorial of number, stopping either at 1 or stopping at the optional numberOfFactors. The factorial of a number n is defined as n×(n–1)×(n–2)×(n–3)...×1. Factorials are useful in statistics and combinatorics. In mathematics, factorials are usually represented by an exclamation mark. 4! = Factorial (4) = 4×3×2×1 = 24.
One application of factorials is to determine how many unique ways a set of objects can be ordered. For instance, a set of three objects {A, B, C} can be ordered 3! = 6 ways: {ABC, ACB, BAC, BCA, CAB, CBA}.
Examples:
FieldBounds()
Category: Design
Syntax: FieldBounds ( fileName; layoutName; fieldName )
Parameters:
fileName—Name of the file where the field resides.
layoutName—Name of the layout where the field resides.
fieldName—Name of the field open.
Data type returned: Text
Description:
Returns the physical position and rotation of a field that is described by the parameters. Note that the parameters are text and must either be references to fields or enclosed in quotation marks. Results are returned as a space-delimited text string in the form of “Left Top Right Bottom Rotation.” The first four of these values represent the distance in pixels from either the left margin of the layout (in the case of Left and Right) or the top margin (in the case of Top and Right). The Rotation value will be 0, 90, 180, or 270, depending on the field’s orientation on the layout.
The values returned are delimited by spaces; the MiddleWords() function can easily be used to parse them.
Be aware that changing the name of a file, layout, or field may cause literal references to them to be broken in functions that use FieldBounds.
The field name that is passed to FieldBounds() must be the name from Define Database (not the field label); if a field appears on layout more than once, the one that is furthest in the rear in the layering order will be used.
Related fields must be referenced by RelationshipName::FieldName or FileName::FieldName.
Examples:
FieldComment()
Category: Design
Syntax: FieldComment ( fileName; fieldName )
Parameters:
fileName—The name of the open file where the field is located.
fieldName—The name of the field for which to return comments.
Data type returned: Text
Description:
Returns the contents of any comment that has been entered in Define Database for the specified field. The syntax Table::fieldName is required to reference fields outside of the current table context. (The safest approach is to use this method in all cases.)
FieldComment() is useful for documenting a database. Care must be taken, however, because literal references to fields can be broken when file, table, or field names are changed. FieldNames() and TableNames() can be used to dynamically investigate all field names and load the results from FieldComment() into tables for browsing.
A field comment may contain a maximum of 30,000 characters, though it’s doubtful such a large comment would constitute a sound development practice.
Examples:
FieldIDs()
Category: Design
Syntax: FieldIDs ( fileName; layoutName )
Parameters:
fileName—The name of the open FileMaker database from which to return IDs.
layoutName—The name of the layout from which to return field IDs.
Data type returned: Text
Description:
Returns a list of all FileMaker internal field IDs in fileName and layoutName, separated by carriage returns. Fields outside the current table context are returned as TableID::RelatedFieldID. If layoutName is empty, then the field IDs of the first table created (the “default” table) are returned.
Calls to FieldIDs() can be broken when file and layout names are changed. Field IDs are assigned by FileMaker and cannot be changed. In our practice we do not recommend using Field IDs when other means may exist to accomplish your needs.
Examples:
FieldNames()
Category: Design
Syntax: FieldNames ( fileName; layout/tableName )
Parameters:
fileName—The name of an open FileMaker database from which to return field names.
layout/tableName—The name of the layout or table to reference.
Data type returned: Text
Description:
Returns a carriage return–delimited list of field names.
When a table name is given (and is not also serving as a layout name), all fields for that table are returned. If a layout has the same name as a table, FileMaker will turn first to the layout in question and return only those fields that have been placed on that layout.
FieldNames() can be used to dynamically generate database structure information about any open FileMaker database.
When information about fields in a table is returned, the results are ordered according to the creation order of the fields. When the names of the fields on a particular layout are returned, the results are ordered according to the stacking order of the fields, from back to front. If an object appears on a layout more than once, it appears multiple times in the result list. Related fields appear as RelationshipName::FieldName.
Examples:
FieldRepetitions()
Category: Design
Syntax: FieldRepetitions ( fileName; layoutName; fieldName )
Parameters:
fileName—The name of the open file where the field to be referenced is located.
layoutName—The name of the layout where the field to be referenced is located.
fieldName—The name of the field for which to return repetition information.
Data type returned: Text
Description:
Returns a space-delimited text string that indicates the number of repetitions and orientation of the field in question. Note that you must pass a layout name. (A table name does not work.) The data is returned in the format of “NumRepetitions Orienation.”
The MiddleWords() function can be used to extract either component of the result.
If literal names of objects are used, calls to the function may be broken when file or object names are changed. Functions such as Get(FileName), LayoutNames(), and FieldNames() can be used to dynamically return information about a database. Also remember that only the number of repetitions that appear on the layout are returned, not the number of repetitions defined in Define Database. Use FieldType() to return the number of repetitions specified in Define Database.
Examples:
FieldStyle()
Category: Design
Syntax: FieldStyle ( fileName; layoutName; fieldName )
Parameters:
fileName—The name of the open file where the field is located.
layoutName—The name of the layout where the field is used.
fieldName—The name of the field for which to return results.
Data type returned: Text
Description:
Returns a space-delimited string indicating the field style and any associated value list. The field styles are
StandardScrollingPopuplistPopupmenuCheckboxRadioButtonCalendar
Calls to FieldStyle() that rely on literal object names may be broken if file, layout, or field names are changed.
Examples:
FieldType()
Category: Design
Syntax: FieldType ( fileName; fieldName )
Parameters:
fileName—The name of the open file where the field is located.
fieldName—The name of the field for which to return results.
Data type returned: Text
Description:
Returns a space-delimited string indicating the field type of the field specified by fieldName. There are four components to the string, each of which can contain several possible values. The possible values for each item are
Item 1: standard, storedcalc, summary, unstoredcalc, or global
Item 2: text, number, date, time, timestamp, or container
Item 3: indexed or unindexed
Item 4: Number of repetitions (1 for a non-repeating field)
fieldName must be specified as Table::Field when referencing fields in tables outside of the current table context.
Using the Table::Field method for referencing fields as a matter of course avoids broken references when the current table context changes.
Examples:
Filter()
Category: Text
Syntax: Filter ( textToFilter; filterText )
Parameters:
textToFilter—Any expression that resolves to a text string.
filterText—The characters to preserve in the specified text.
Data type returned: Text
Description:
Strips from textToFilter all the characters not explicitly listed in filterText. All remaining characters are returned in the order in which they exist in textToFilter, including duplicates. If filterText doesn’t have any characters, an empty string is returned, as opposed to a question mark. The Filter() function is case sensitive.
The Filter() function is frequently used to ensure that users have entered valid data into a field. The textToFilter parameter should contain any valid characters; the order of the characters within textToFilter isn’t important.
Examples:
FilterValues()
Category: Text
Syntax: FilterValues ( textToFilter; filterValues )
Parameters:
textToFilter—A return-delimited text string or expression that generates a return-delimited text string.
filterValues—A return-delimited text string or expression that generates a return-delimited text string representing values that you want to preserve in the specified text.
Data type returned: Text
Description:
FilterValues() produces a return-delimited list of items in textToFilter that are included among the specified filterValues. The items must match exactly in order to be returned, with the exception of case; FilterValues() is not case sensitive.
Values are returned in the order they appear in textToFilter. If filterValues is an empty string, or if no items in textToFilter are contained in the filterValues list, then an empty string is returned.
FilterValues() can be used to determine whether a particular item is part of a return-delimited array. For instance, the WindowNames() function produces a return-delimited list of windows. If you wanted to know whether a window named Contact Detail existed, you could use the following formula:
ValueCount ( FilterValues ( WindowNames; "Contact Detail" ))
If the value count is anything other than zero, that means the window name was found. The benefit of using FilterValues for this rather than PatternCount() is that Contact Detail - 2 would not be returned if Contact Detail were the filter.
Examples:
Floor()
Category: Number
Syntax: Floor ( number )
Parameters:
number—Any expression that resolves to a numeric value.
Data type returned: Number
Description:
Returns number rounded down to the next lower integer.
For positive numbers, Floor() and Int() return the same results; however, for negative numbers, Int() returns the next larger integer, whereas Floor() returns the next smaller integer.
Examples:
FV()
Category: Financial
Syntax: FV ( payment; interestRate; periods )
Parameters:
payment—The nominal amount of the payment.
interestRate—The per-period interest rate.
periods—The number of periods in the duration of the investment.
Data type returned: Number
Description:
Returns the future value of a periodic investment based on the payments and interest rate for the number of periods specified.
The FV() function doesn’t account for the present value of your investment, and it assumes that payments are made at the end of each period.
Examples:
If the investment compounds monthly, divide the annual interestRate by 12 to express the periods as a number of months.
To figure out the future value of monthly investments of $250, earning 8% interest, for 10 years, you would use the formula
Get(AccountName)
Category: Get
Syntax: Get ( AccountName )
Parameters: None
Data type returned: Text
Description:
Returns the name of the authenticated account being used by the current user of the database file. If a user is logged in under the default Admin account, Admin is returned. If a user is using the FileMaker Pro guest account, then [Guest] is returned.
For external server authentication, Get(AccountName) returns the name of the authenticated account being used by the current user of the database file, not the group to which the user belongs. (The group name appears in the Account list when you define accounts and privileges in FileMaker Pro.) If an individual belongs to more than one group (account), the first group name listed when you View By Authentication Order while defining accounts and privileges determines access for the user.
Get ( AccountName ) can be used to retrieve the account name of the current user for purposes of logging or auditing database access.
Examples:
Get(ActiveFieldContents)
Category: Get
Syntax: Get ( ActiveFieldContents )
Parameters: None
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
Returns the contents of the field in which the cursor is currently placed. The contents of the field need not be highlighted.
Get ( ActiveFieldContents ) can return the contents of fields of any data type, but the field in which you place those contents may need to be of the same data type for it to display properly.
Note that in the case where the cursor is not placed in a field, a blank value is returned, as opposed to a question mark.
When using Get() functions within field definitions, in most cases you should set the storage option to be “unstored” so that the field always displays current data.
Examples:
Get(ActiveFieldName)
Category: Get
Syntax: Get ( ActiveFieldName )
Parameters: None
Data type returned: Text
Description:
Returns the name of the field in which the cursor is currently placed.
Even when the active field is a related or unrelated field from another table, Get ( ActiveFieldName ) simply returns the field’s name. It does not use the double-colon syntax “relationshipName::FieldName.”
Note that in the case where the cursor is not placed in a field, a blank value is returned, as opposed to a question mark.
When using Get() functions within field definitions, in most cases you should set the storage option to be “unstored” so that the field always displays current data.
Get(ActiveFieldTableName)
Category: Get
Syntax: Get ( ActiveFieldTableName )
Parameters: None
Data type returned: Text
Description:
Returns the name of the table occurrence for the field in which the cursor is currently placed.
Note that the table occurrence name (from the Relationships Graph) is returned, rather than the source table name.
Note that in the case where the cursor is not placed in a field, a blank value is returned, as opposed to a question mark.
When using Get() functions within field definitions, in most cases you should set the storage option to be “unstored” so that the field always displays current data.
Examples:
Get(ActiveModifierKeys)
Category: Get
Syntax: Get ( ActiveModifierKeys )
Data type returned: Number
Description:
Returns the sum of the constants that represent the modifier keys that the user is pressing on the keyboard. The constants for modifier keys are as follows:
1—Shift
2—Caps lock
4—Control
8—Alt (Windows) or Option (Mac OS)
16—Command key (Mac OS only)
When using Get() functions within field definitions, in most cases you should set the storage option to be “unstored” so that the field always displays current data.
Examples:
Get ( ActiveModifierKeys )
Returns 4 if the Control key is being held down, and returns 7 (1+2+4) if the Shift, Caps Lock, and Control keys are being held down.
The following formula can be used to show text values for keys being held down; it can be used in a calculated field or a custom function:
If the user is holding down the Shift, Caps, and Control keys when this function is evaluated, the text values for those keys are returned in the form of Shift, Caps Lock, and Control.
Get(ActiveRepetitionNumber)
Category: Get
Syntax: Get ( ActiveRepetitionNumber )
Parameters: None
Data type returned: Number
Description:
Returns the number of the active repetition (the repetition in which the cursor currently resides) for a repeating field. Repetition numbers start with 1.
If the cursor is not in a field, 0 is returned.
When using Get() functions within field definitions, in most cases you should set the storage option to be “unstored” so that the field always displays current data.
Examples:
Get(ActiveSelectionSize)
Category: Get
Syntax: Get ( ActiveSelectionSize )
Parameters: None
Data type returned: Number
Returns the number of characters that are highlighted in the current field. The function returns 0 if no characters are highlighted, and returns a blank value if no field is active. When multiple windows are open (which leads to the possibility of multiple highlighted selections), only the active window is considered.
Carriage returns, tabs, spaces, and other invisible characters are counted by Get ( ActiveSelectionSize ).
When using Get() functions within field definitions, in most cases you should set the storage option to be “unstored” so that the field always displays current data.
Examples:
Get(ActiveSelectionStart)
Category: Get
Syntax: Get ( ActiveSelectionStart )
Parameters: None
Data type returned: Number
Description:
Returns the position of the first character in the highlighted text of the current field. If no text is highlighted (that is, the user has simply clicked into a block of text), then the current position of the cursor is returned. It returns a blank value if no field is active. When multiple windows are open, only the active window is considered.
Remember that carriage returns, tabs, spaces, and other invisible characters are taken into account when evaluating Get ( ActiveSelectionStart ).
Used in conjunction with Get ( ActiveSelectionSize ), you can determine the string that a user has highlighted in any field, using the formula
Middle ( Get (ActiveFieldContents); Get (ActiveSelectionStart) ;
Get ( ActiveSelectionSize ))
Examples:
Get(AllowAbortState)
Category: Get
Syntax: Get ( AllowAbortState )
Parameters: None
Data type returned: Number
Description:
Returns 1 if Allow User Abort is On; returns 0 if Allow User Abort is Off.
If the setting for User Abort hasn’t been explicitly set, a script runs as if Allow User Abort is On. Get (AllowAbortState) returns 1 in such cases.
Examples:
In the following script
Allow User Abort [Off]
Show Custom Dialog [Get ( AllowAbortState ) ]
the custom dialog would display 0.
Allow User Abort [On]
Show Custom Dialog [Get ( AllowAbortState ) ]
the custom dialog would display 1.
Get(AllowToolbarState)
Category: Get
Syntax: Get ( AllowToolbarState )
Parameters: None
Data type returned: Number
Description:
Developers may control whether or not users can make toolbars visible or not via the Allow Toolbars script step. This companion function returns a Boolean value representing whether toolbars are allowed to be visible. Returns 1 if toolbars are allowed, otherwise returns 0.
By default, toolbars are allowed.
Examples:
Get(ApplicationLanguage)
Category: Get
Syntax: Get ( ApplicationLanguage )
Parameters: None
Data type returned: Text
Description:
Returns a text string representing the current application language. The possible results are as follows:
English
French
Italian
German
Swedish
Spanish
Dutch
Japanese
The string returned will always be in English, even in versions of the product based on other languages. That is, it returns German, not Deutsch, in the German language version of FileMaker.
Examples:
Get(ApplicationVersion)
Category: Get
Syntax: Get ( ApplicationVersion )
Parameters: None
Data type returned: Text
Description:
Returns a text string representing the application and version:
Pro (version) for FileMaker Pro.
ProAdvanced (version) for FileMaker Advanced.
Runtime (version) for FileMaker Runtime.
Web (version) for FileMaker Web Client in cases where IWP is being hosted from FileMaker Pro or Pro Advanced.
Server (version) for FileMaker Web Server.
If you have allowed web access to a database, you may want to add conditional tests within some of your scripts so that they will behave differently for web and FileMaker Pro clients. To identify web users, use either of the following formulas:
PatternCount ( Get ( ApplicationVersion ); "Web")
Position ( Get ( ApplicationVersion); "Web"; 1; 1)
If either of these return anything other than 0, the user is a web client.
Examples:
Get ( ApplicationVersion )
Returns ProAdvanced 8.0v1 for FileMaker Pro Advanced 8.0v1.
If you add an unstored calculation field that performs Get(ApplicationVersion) to a database, and then extract the data as XML via Custom Web Publishing, you may get
<field name="AppVersion">
<data>Web Publishing Engine 8.0v1</data>
</field>
Get(CalculationRepetitionNumber)
Category: Get
Syntax: Get ( CalculationRepetitionNumber )
Parameters: None
Data type returned: Number
Description:
Returns the current repetition number of a calculation field. If the calculation is not set to allow more than one value, Get (CalculationRepetitionNumber) returns 1.
Get (CalculationRepetitionNumber) is nothing more than the repetition number.
You can use the repetition number in conditional tests involving repeating fields. For instance, the following formula
If (Get (CalculationRepetitionNumber) < 4; "foo"; "boo")
returns a repeating calculation field with values foo, foo, foo, boo, boo, and so on.
Examples:
A calculation field defined to hold five repetitions and has the following formula
Get (CalculationRepetitionNumber) ^ 2
returns the repetition values 1, 4, 9, 16, 25.
Get(CurrentDate)
Category: Get
Syntax: Get ( CurrentDate )
Parameters: None
Data type returned: Date
Description:
Returns the current date according to the operating system calendar.
The format of the result varies based on the date format that was in use when the database file was created. In the United States, dates are generally in the format MM/DD/YYYY. You can change a computer’s date format in the Regional Settings Control Panel (Windows 2000), the Date and Time Control Panel (Windows XP), or the Date & Time System Preference (Mac OS X).
If the result is displayed in a field, it is formatted according to the date format of the field in the current layout.
Examples:
Get(CurrentHostTimestamp)
Category: Get
Syntax: Get ( CurrentHostTimestamp )
Parameters: None
Data type returned: Timestamp
Description:
Returns the current timestamp (date and time) according to the host’s system clock, to the nearest second.
Get (CurrentHostTimeStamp) returns the date and time from the host machine, regardless of the date and time settings on the client machine. Get (CurrentHostTimestamp) is therefore useful for storing information about when records are created or edited because it disregards differences in time zones or improper settings of the client machines.
Function calls that run on the server may impact a network user’s performance, especially when they are used in unstored calculations.
Examples:
Get(CurrentTime)
Category: Get
Syntax: Get ( CurrentTime )
Parameters: None
Data type returned: Time
Returns the current time from the local system clock (on the client machine).
Note that the Time data type is stored internally as the number of seconds since midnight. Math can be performed on all Time() functions using multiples of seconds (60 = 1 minute, 3600 = 1 hour).
Remember that the time returned by Get (CurrentTime) is the local time on the system clock of the client machine. In cases where clients are accessing a database from different time zones, or someone has their clock set wrong, this data may be less useful than time extracted from the host machine’s system clock with the Get (CurrentHostTimeStamp) function.
Examples:
Get(CurrentTimestamp)
Category: Get
Syntax: Get ( CurrentTimestamp )
Parameters: None
Data type returned: Timestamp
Description:
Returns the current timestamp (date and time) according to the local system clock to the nearest second.
Note that a timestamp is stored internally as an integer that represents the number of seconds since 1/1/0001. Therefore, calculations that use seconds as the base unit can be performed on timestamp data types.
Get (CurrentTimeStamp) uses the date and time settings of the local machine (client) and may be less useful or accurate than Get (CurrentHostTimeStamp) in a database that is accessed by clients from different time zones.
Examples:
Get(CustomMenuSetName)
Category: Get
Syntax: Get ( CustomMenuSetName )
Parameters: None
Data type returned: Text
Description:
Returns the name of the active custom menu set. If the active menu set is the Standard FileMaker Menus (which is the initial default for all files), a blank value is returned.
Examples:
Get(DesktopPath)
Category: Get
Syntax: Get ( DesktopPath )
Parameters: None
Data type returned: Text
Description:
Returns the path to the desktop folder for the current user’s computer. In Windows, the path format is /Drive:/Documents and Settings/UserName/Desktop/. In the Mac OS, the path format is /DriveName/Users/UserName/Desktop/.
Note that the user in this case is the operating system user account and should not be confused with the account with which a user logged in to a given database.
Examples:
Get(DocumentsPath)
Category: Get
Syntax: Get ( DocumentsPath )
Parameters: None
Data type returned: Text
Returns the path to the documents folder for the current user. In Windows, the path format is /Drive:/Documents and Settings/UserName/My Documents/. In the Mac OS, the path format is /DriveName/Users/UserName/Documents/.
Note that the user in this case is the operating system user account and should not be confused with the account with which a user logged in to a given database.
Examples:
Get(ErrorCaptureState)
Category: Get
Syntax: Get ( ErrorCaptureState )
Parameters: None
Data type returned: Number
Description:
Returns 1 if Set Error Capture has been set to On, and 0 if Set Error Capture was either not set or set to Off.
It is not possible to tell with the Get (ErrorCaptureState) function whether Error Capture was explicitly turned off or simply not set.
Examples:
In the following script
Set Error Capture [Off]
Show Custom Dialog [ Get (ErrorCaptureState) ]
the custom dialog displays 0.
Set Error Capture [On]
Show Custom Dialog [ Get (ErrorCaptureState) ]
the custom dialog displays 1.
Get(ExtendedPrivileges)
Category: Get
Syntax: Get ( ExtendedPrivileges )
Parameters: None
Data type returned: Text
Description:
Returns a delimited list of extended privileges, separated by carriage returns, currently assigned for use by the active account in a given database file. Extended privileges are additional access rights assigned to a privilege set; by default they control such things as access via the Web, via ODBC/JDBC, and via FileMaker Networking, but developers can add their own extended privileges as well.
If the user’s privilege set doesn’t have any extended privileges enabled, Get( ExtendedPrivileges ) returns an empty list.
To test whether a user has a certain extended privilege, use one of the following formulas:
PatternCount (Get (ExtendedPrivileges); "salesNorthWestRegion")
If this function returns anything other than 0, then the user has the salesNorthWestRegion extended privilege.
The Position() function can also be used:
Position (Get (ExtendedPrivileges); "fmiwp"; 1; 1)
In this case, any value greater than 0 indicates the presence of the fmiwp privilege.
Examples:
If the currently logged-in account uses a privilege set that includes the extended privileges of Access via Instant Web Publishing (keyword fmiwp) and Access via FileMaker Network (keyword fmapp).
Get(FileMakerPath)
Category: Get
Syntax: Get ( FileMakerPath )
Parameters: None
Data type returned: Text
Description:
Returns the path to the folder of the currently running copy of FileMaker Pro. In Windows, the path format is /Drive:/Program Files/FileMaker/FileMaker Pro 8/. In the Mac OS, the path format is /DriveName/Applications/FileMaker Pro 8/. (The actual path may vary if FileMaker was installed in a non-standard location.)
Examples:
Get(FileName)
Category: Get
Syntax: Get ( FileName )
Parameters: None
Data type returned: Text
Description:
Returns the filename of the current database file without the file extension.
Get(FileName) is useful in function calls that require a filename, even if the current file is being referenced. This way, if the filename changes, you don’t need to change any of your calculation formulas.
If a field in file Alpha.fp7 contains the formula Get (FileName), and that field is displayed on a layout in another file, Beta.fp7, via an external table occurrence, the field value will still return Alpha. Its context is rightly applied.
Examples:
Get(FilePath)
Category: Get
Syntax: Get ( FilePath )
Parameters: None
Description:
Returns the full path to the currently active database file, including the file extension.
Returns file:/driveletter:/databaseName for local files in Windows.
Returns file://volumename/myfoldername/databaseName for remote files in Windows.
Returns file:/path/databaseName for local and remote files in the Mac OS.
Returns fmnet:/networkaddress/databaseName for FileMaker Pro networked files.
Remember that Get (FilePath) includes the filename and extension. Text parsing functions can be used to extract just the file path from the results returned by Get (FilePath). This can be useful for building dynamic paths to objects that are in the same directory as the current file.
If a field in file Alpha.fp7 contains the formula Get (FilePath), and that field is displayed on a layout in another file, Beta.fp7, via an external table occurrence, the field value will still return the file path for file Alpha. Its context is rightly applied.
Examples:
Get(FileSize)
Category: Get
Syntax: Get ( FileSize )
Parameters: None
Data type returned: Number
Description:
Returns the size of the current file in bytes.
Examples:
If the current file size is 1,404,928 bytes Get (FileSize) returns 1404928.
Get(FoundCount)
Category: Get
Syntax: Get ( FoundCount )
Parameters: None
Data type returned: Number
Description:
Returns a number that represents the number of records in the current found set.
If multiple windows are open in the current database file, each window can have its own found set. If the Get (FoundCount) function is used in a script, it returns the found count of the active layout in the active window.
Get (FoundCount) is often used in scripts, following finds, to determine navigation paths. In the following script, if one record is found, the Detail layout is shown. If multiple records are found, the List layout is shown. Finally, if no records are found, the script notifies the user with a dialog box. If[Get(FoundCount)=1]
Go To Layout[Detail]
Else If[Get(FoundCount)>1]
Go To Layout[List]
Else
Show Custom Dialog ["Empty Set"; "No Records Found"]
End If
Examples:
If 240 records are in the found set, Get (FoundCount) returns 240.
Get(HighContrastColor)
Category: Get
Syntax: Get ( HighContrastColor )
Parameters: None
Data type returned: Text
Description:
Windows only: Returns the name of the current high-contrast default color scheme. Returns an empty value (null) if Use High Contrast is unavailable, inactive, or if the function is called on the Mac OS.
Use High Contrast is an option under Control Panel, Accessibility Options, Display tab. The standard options increase default font sizes and heighten screen contrast to assist users with impaired vision.
Get(HighContrastState)
Category: Get
Syntax: Get ( HighContrastState )
Parameters: None
Data type returned: Number
Description:
Windows only: Returns a number representing the state of the Use High Contrast option in the Accessibility Options control panel.
Returns:
0 if Use High Contrast is unavailable, inactive, or if the function is used on the Mac OS.
1 if Use High Contrast is available and active.
If you have users with impaired vision, you might create alternate versions of your layouts that are easier for them to use. If so, you can test in your navigation scripts whether Use High Contrast is active and go to an appropriate layout or zoom the window. If [ Get (HighContrastState) = 1 ]
Go to Layout ["ContactDetail (HC)"]
Else
Go to Layout ["ContactDetail"]
End If
Get(HostIPAddress)
Category: Get
Syntax: Get ( HostIPAddress )
Parameters: None
Data type returned: Text
Description:
Returns the IP address of the host computer for the current database. If the current database is open as a single-user non-hosted file, an empty string is returned.
Examples:
Returns 14.156.13.121 (as an example) when the current database is being hosted.
Get(HostName)
Category: Get
Syntax: Get ( HostName )
Data type returned: Text
Description:
Returns the registered name of the computer hosting the database file.
To change the registered name on a computer:
On Windows, the computer name is found on the Network Identification tab of the System Properties control panel. The Full Computer Name option displays the current registered name.
On Mac OS, the computer name is found within System Preferences, under the Sharing settings.
If a client connects to a file hosted by FileMaker Server, Get (HostName) returns the name of the server. The host name can be configured with the Server Administration tool. By default, FileMaker Server uses the system’s name, but a custom name can be supplied instead.
We find it helpful in our practice to place a Get(HostName) display on a prominent layout within our solutions so that we can see during development whether or not we’re working on a live version, a development version, or a scratch file on our local laptops.
Examples:
If the computer is named “Maturin” Get(HostName)
results in Maturin.
Get(LastError)
Category: Get
Syntax: Get ( LastError )
Parameters: None
Data type returned: Number
Returns the number of the error generated by the most recent script step. If there was no error, then Get (LastError) returns 0. Use this function in combination with Set Error Capture [On] to trap and handle errors raised in scripts.
A common source of bugs in scripts is not remembering that the Get (LastError) function returns the error code from only the most recently executed script step. For example, in this script Perform Find
If (Get(ErrorCaptureState) = 1)
Show Custom Dialog (Get (LastError))
End If
the Get (LastError) step returns the result of the execution of the If statement, not the error code generated by the Find step.
→ For a complete listing of error codes, see Chapter 11, “FileMaker Error Codes.”
→ For more discussion on debugging and troubleshooting, see Special Edition Using FileMaker 8, "Debugging and Troubleshooting."
Note that if a script is running on the Mac OS and calls an AppleScript routine, any errors generated will also be passed through to and presented via this function.
Note too that if an error occurs in FileMaker while performing an SQL query, an SQLSTATE error will be returned by ODBC.
Examples:
Consider the following script: Set Error Capture [On]
Print Setup[Restore]
SetVariable [$error; value: Get (LastError)]
If the user cancels out of the Print Setup dialog, Get (LastError) returns 1 (user canceled action). If the Print Setup step executes successfully, Get (LastError) returns 0.
Get(LastMessageChoice)
Category: Get
Syntax: Get ( LastMessageChoice )
Parameters: None
Data type returned: Number
Description:
Returns a number corresponding to the button clicked as a result of the Show Custom Dialog script step.
Though it has a value of 1, the default button on a dialog is always on the far right side. For example, if there are three buttons, they will appear in 3-2-1 (Cancel, Maybe, OK) order.
Returns:
1 for the default button.
2 for the second button.
3 for the third button.
Examples:
For the following script step, where the default button is labeled OK, the second button is labeled Maybe, and the third button is labeled Cancel:
Show Custom Dialog ["test";"Proceed?"]
If the user chooses OK, Get (LastMessageChoice) returns 1.
If the user chooses Maybe, Get (LastMessageChoice) returns 2.
If the user chooses Cancel, Get (LastMessageChoice) returns 3.
You can then use an If() statement to handle each possibility appropriately.
Note that if a custom dialog has input fields, it is only when the user clicks the default right-most button will the data be inserted into the input field.
Get(LastODBCError)
Category: Get
Syntax: Get ( LastODBCError )
Parameters: None
Data type returned: Text
Description:
Returns a string that shows the ODBC error state (SQLSTATE), as published by ODBC standards, based on ISO/IEF standards.
The ODBC error state is cleared at the time the next ODBC-related script step is performed. Anytime before that happens, you can check to see whether an ODBC error was generated.
By setting the Set Error Capture script step to On, you can suppress the error messages that a user sees during execution of a script that uses ODBC functions.
Examples:
When attempting to execute a SQL statement with an invalid field name, Get (LastODBCError) returns S0022.
If no error is encountered, Get (LastODBCError) returns 00000.
Get(LayoutAccess)
Category: Get
Syntax: Get ( LayoutAccess )
Parameters: None
Data type returned: Number
Returns a number that represents the current user’s record access privileges level for the current layout. Privileges are assigned in the Custom Layout Privileges dialog box.
The Get (LayoutAccess) function can be used to alert users of restricted privileges at the layout level. Note that Get (LayoutAccess) returns information about only the current layout. Record access privileges for any other layout are not represented.
Note also that Get (LayoutAccess) does not return information about whether or not the layout itself is accessible, but rather what access the user has to edit record data via the current layout.
The Get (RecordAccess) function evaluates record access privileges independent of the Get (LayoutAccess) function. To fully evaluate record access, evaluate the return values of both the Get (LayoutAccess) and Get (RecordAccess) functions.
Examples:
Get(LayoutCount)
Category: Get
Syntax: Get ( LayoutCount )
Parameters: None
Data type returned: Number
Returns the total number of layouts in the current file.
Get(LayoutCount) returns the total number of layouts within a file, including hidden layouts and layouts the user doesn’t have privileges to see.
Examples:
Get(LayoutName)
Category: Get
Syntax: Get ( LayoutName )
Parameters: None
Data type returned: Text
Description:
Returns the name of the layout currently displayed in the active window.
To change the name of a layout, in Layout mode, go to the Layouts menu, select the Layout Setup menu item, and then click the General tab. Layouts do not need to be uniquely named.
Examples:
Get(LayoutNumber)
Category: Get
Syntax: Get ( LayoutNumber )
Parameters: None
Data type returned: Number
Description:
Returns the number of the layout currently displayed in the active window. The order of layouts can be set in Layout mode by going to the Layouts menu and selecting the Set Layout Order menu item.
Get (LayoutNumber) can be used to keep track of the last layout a user visited. The following script takes a user from one layout to another, allows the user to complete other tasks, and then returns the user to the original layout:
Set Field [temp; Get(LayoutNumber)]
Go to Layout ["Other Layout"]
[perform script, process, etc]
Go to Layout [temp]
Because the layout you navigate to might be associated with a different table than the calling layout, the temp field used to store the layout number should be set to have global storage.
Examples:
Get(LayoutTableName)
Category: Get
Syntax: Get ( LayoutTableName )
Parameters: None
Description:
Returns the name of the table occurrence (not the source table) from which the current layout shows records.
Because there is no way of retrieving the name of the source table with which a layout is associated, consider prefixing the names of table occurrences with an abbreviation that represents the source table. For instance, you might name a table occurrence INV_CustomerInvoices. You can then use text parsing functions to retrieve the source table name from the results returned by the Get (LayoutTableName) function.
Note that when no windows are active or visible, an empty string is returned.
Examples:
Get(LayoutViewState)
Category: Get
Syntax: Get ( LayoutViewState )
Parameters: None
Data type returned: Number
Description:
Returns a number that represents the view mode for the current layout in the active window.
Get (LayoutViewState) is useful in scripts to test the state of the current layout. Unless a layout has been restricted not to be viewable in another state, users can manually change the state of the current layout, provided they have access to menu commands. You can detect whether the layout is in the proper state, and if necessary, change it with the View As script step.
Examples:
Get (LayoutViewState) returns 0, 1, or 2, depending on the current layout’s view state:
0 = View as Form
1 = View as List
2 = View as Table
Get(MultiUserState)
Category: Get
Syntax: Get ( MultiUserState )
Parameters: None
Data type returned: Number
Description:
Returns a number that represents the FileMaker sharing/networking status for the current file.
Return 0 when network sharing is off, or when network sharing is on but no privilege sets have the [fmapp] Extended Privilege keyword enabled.
Returns 1 when network sharing is on, the database file is accessed from the host computer, and some or all users have the [fmapp] keyword enabled.
Returns 2 when network sharing is on, the database file is accessed from a client computer, and some or all users have the [fmapp] keyword enabled.
Get(NetworkProtocol)
Category: Get
Syntax: Get ( NetworkProtocol )
Parameters: None
Data type returned: Text
Description: Returns the name of the network protocol that FileMaker Pro is using on the current machine.
Unlike in previous versions of FileMaker, the only network protocol supported by FileMaker Pro 8 is TCP/IP. Get (NetworkProtocol) always returns TCP/IP, even if FileMaker Network sharing is off.
Examples:
Get(PageNumber)
Category: Get
Syntax: Get ( PageNumber )
Parameters: None
Data type returned: Number
When printing or previewing a document, this function returns the current page number. If nothing is being printed or previewed, Get (PageNumber) returns 0.
If you are printing a report of unknown length and you want to determine the number of pages, you can have a script go to the last page in Preview mode and capture the value returned by Get (PageNumber) in a global field. This would then allow you to have something like “Page 2 of 5” appear in the footer of your report.
Examples:
Imagine you have an unstored calculation with the formula:
"Page " & Get (PageNumber)
When printing a multi-page report, this field could be placed in the footer of the layout and it would return the proper page number when the report was previewed or printed.
Get(PortalRowNumber)
Category: Get
Syntax: Get ( PortalRowNumber )
Parameters: None
Data type returned: Number
Description:
Returns the number of the currently selected portal row—A case where either the row itself is selected (highlighted in black) or the cursor is actively sitting within a field in the portal.
When no portal row is selected, Get (PortalRowNumber) returns 0.
Get(PortalRowNumber) should be used to determine with which row a user is working.
→ See also "GetNthRecord()" later in this chapter.
When the user clicks on the third row of a portal,
Get (PortalRowNumber)
returns 3.
When the user clicks out of the portal onto the layout itself,
Get (PortalRowNumber)
returns 0.
Get(PreferencesPath)
Category: Get
Syntax: Get ( PreferencesPath )
Parameters: None
Data type returned: Text
Description:
Returns the operating system path to a user’s preferences and default options folder. In Windows, the path format is /Drive:/Documents and Settings/UserName/Local Settings/Application Data/. In the Mac OS, the path format is /DriveName/Users/UserName/Library/Preferences/.
Note that the user in this case is the operating system user account and should not be confused with the account with which a user logged in to a given database.
Examples:
Returns /C:/Documents and Settings/Nate/Local Settings/Application Data/ for a user named Nate in Windows.
Returns /MacintoshHD/Users/Eleanor/Library/Preferences/ for a user named Eleanor in the Mac OS.
Get(PrinterName)
Category: Get
Syntax: Get ( PrinterName )
Parameters: None
Data type returned: Text
Description:
Returns information about the currently selected printer.
In Windows, Get (PrinterName) returns a text string containing the printer name, driver name, and printer port, separated by commas.
In Mac OS, Get (PrinterName) returns a text string with the name or IP address of the printer, and the name of the print queue, as it appears in the Print Center.
If in either operating system the printer information is unavailable for whatever reason, <Unknown> is returned.
If there are certain print jobs that require that a specific printer be selected, you can test for Get (PrinterName) within a script and ask the user to select a different printer if necessary.
Examples:
In Windows,
Get (PrinterName)
might return \server1Lexmark Optra M412 PS3, winspool, Ne02:.
In Mac OSX,
Get (PrinterName)
may return the IP address 255.5.5.255.
Or it may return the name of the current printer (if the printer is not networked). For example, hp Laserjet 4200.
Get(PrivilegeSetName)
Category: Get
Syntax: Get ( PrivilegeSetName )
Parameters: None
Data type returned: Text
Description:
Returns the name of the privilege set assigned to the active account.
Every account must be assigned one, and only one, privilege set.
Examples:
Get(RecordAccess)
Category: Get
Syntax: Get ( RecordAccess )
Parameters: None
Data type returned: Number
Description:
Returns a number that represents the current account’s access privileges for the current record. Record privileges are assigned via privilege set.
Returns 0 if the user does not have View or Edit privileges for the current record.
Returns 1 if the user has view-only access to the current record. This could mean the View is set to Yes for the current table, or that View is set to Limited and that the calculation defined for Limited access returns a value of True.
Returns 2 if the user has edit access for the current record. This could mean that Edit is set to Yes for the current table, or that Edit is set to Limited and that the calculation defined for Limited access returns a value of True.
The Get (RecordAccess) function can be used to alert users of restricted privileges at the record level. Note that Get (RecordAccess) returns information only about table record privileges. Record access may be restricted through the layout access as well.
To fully evaluate current record access, evaluate both the return values of Get (LayoutAccess) and the Get (RecordAccess) function.
Examples:
Get(RecordID)
Category: Get
Syntax: Get ( RecordID )
Parameters: None
Data type returned: Number
Description: Returns the unique, internal ID number of the current record. This number is generated automatically by FileMaker Pro and does not change.
The record ID is assigned sequentially within each table, beginning at 1. Record IDs are not reused; if a record is deleted, its ID is not reassigned.
When files are converted from previous versions, record IDs from the original file are preserved.
The record ID is required for editing and deleting records via Custom Web Publishing, as this is how the record to be changed or deleted must be identified.
Examples:
Get(RecordModificationCount)
Category: Get
Syntax: Get ( RecordModificationCount )
Parameters: None
Data type returned: Number
Description:
Returns the total number of times the current record has been modified. A record change must be committed before the modification count updates.
Committing multiple field changes at once is considered a single record modification. Each time a change is committed, the modification count increases.
Get (RecordModificationCount) can be used by custom web applications to ensure that one user’s changes do not overwrite another’s. At the time the record is loaded into the web browser, the record modification count can be stored. When the record is saved, the current record modification count can be checked against the stored one to see whether another user has updated the record in the meantime.
Duplicated records retain the same record modification count as the record from which they were created; the count is not reset to zero. There’s no way to alter or reset the modification count.
Get(RecordNumber)
Category: Get
Syntax: Get ( RecordNumber )
Parameters: None
Data type returned: Number
Description:
Returns a number that represents the position of a record in the current found set. This value changes depending on the found set and the sort order.
Get (RecordNumber) tells you a record’s position within the found set. This is useful if you want to create a calculation to display “X of Y records” on a given layout.
To determine FileMaker’s unique internal record ID, use Get (RecordID).
Examples:
Get(RecordOpenCount)
Category: Get
Syntax: Get ( RecordOpenCount )
Parameters: None
Data type returned: Number
Description:
Returns the total number of open, uncommitted records in the current found set. An open record is one in which changes have been made but not yet saved by the user or currently executing script.
This function is particularly useful when batch processes are necessary. For example, when exporting records, first check that Get(RecordOpenCount) is zero to ensure having the latest data.
Examples:
Returns 4 if there are four open records in the current found set that haven’t been saved.
Get(RecordOpenState)
Category: Get
Syntax: Get ( RecordOpenState )
Parameters: None
Data type returned: Number
Description:
Returns a number representing the open/committed status of the current record.
Returns 0 for a closed or committed record.
Returns 1 for a new record that hasn’t been committed.
Returns 2 for a modified record that hasn’t been committed.
This is particularly useful for cases where you may have a script looping through a set of records making a change of some sort. If a user is editing a record, your script could skip over the record in question and, depending on the script and its error capture state, may make no note of it.
Examples:
Returns 1 if the current record is a new record that hasn’t been saved.
Get(RequestCount)
Category: Get
Syntax: Get ( RequestCount )
Parameters: None
Data type returned: Number
Description:
Returns the total number of find requests defined in the current window.
Get (RequestCount) can be used in scripted find routines to see whether the user has added any find requests to the default request. It is also useful as a boundary condition if you ever need to loop through all the find requests and either capture or set search parameters.
Examples:
If the current find request asks for invoices with values greater than $200.00,
Get (RequestCount)
returns 1.
If the current find request asks for invoices with values greater than $200 or invoices with dates after 1/1/2004,
Get (RequestCount)
returns 2.
Get(RequestOmitState)
Category: Get
Syntax: Get ( RequestOmitState )
Parameters: None
Data type returned: Number
Description:
Returns 1 if the Omit checkbox is selected in Find mode, otherwise returns 0.
Examples:
Returns 1 when the Omit checkbox is selected in the current find request.
Get(ScreenDepth)
Category: Get
Syntax: Get ( ScreenDepth )
Parameters: None
Data type returned: Number
Description:
Returns the number of bits needed to represent the color or shade of gray of a pixel on the user’s monitor. A value of 8 represents 256 (equal to 2^8) colors or shades of gray.
Use Get (ScreenDepth) to alert users if their monitor color settings are set too low to view images correctly. For example,
If[Get(ScreenDepth)<32]
Show Custom Dialog ["Color";"Your monitor should be set to "Millions
of colors" to display images correctly"]
End If
Get(ScreenHeight)
Category: Get
Syntax: Get ( ScreenHeight )
Parameters: None
Data type returned: Number
Description:
Returns the number of pixels that are displayed vertically on the current screen. This corresponds to a user’s operating system settings for display resolution.
Note when the active window spans more than one screen, this function calculates the value for the screen that contains the largest percentage of the window.
Use Get (ScreenHeight) and Get (ScreenWidth) to check minimum resolution settings on a user’s computer.
If [Get (ScreenHeight)<1200 OR Get (ScreenWidth)<1600]
Show Custom Dialog ["Resolution";"This application requires a minimum
of 1600 x 1200 screen resolution."]
Perform Script ["Close Solution Files"]
End If
Get(ScreenWidth)
Category: Get
Syntax: Get ( ScreenWidth )
Parameters: None
Data type returned: Number
Description:
Returns the number of pixels that are displayed horizontally on the active screen. This corresponds to a user’s operating system settings for display resolution.
Note when the active window spans more than one screen, this function calculates the value for the screen that contains the largest percentage of the window.
See Get (ScreenHeight).
Examples:
Get(ScriptName)
Category: Get
Syntax: Get ( ScriptName )
Parameters: None
Description:
Returns the name of the current script even if paused. When no script is running, Get (ScriptName) returns an empty string.
One use of Get (ScriptName) is to capture errors. In this example, the Log Error script takes the script name as a parameter.
If [Get(LastError) <> 0]
Perform Script ["Log Error"; Parameter: Get (ScriptName)]
End If
Passing the current script’s name as a script parameter can be useful anytime a subscript can be called by multiple scripts.
Examples:
Get(ScriptParameter)
Category: Get
Syntax: Get ( ScriptParameter )
Parameters: None
Data type returned: Text
Description:
Retrieves the parameter that was passed to a currently running script.
The value of a script parameter can be retrieved anywhere within a script, regardless of subscript calls. Script parameters cannot be altered during execution of a script.
Subscripts do not inherit the script parameter of the calling script. Rather, they can be passed parameters of their own that exist only for the duration of the subscript. If you want a subscript to inherit a script parameter, pass Get (ScriptParameter) as the subscript’s parameter.
Only one value can be passed as a script parameter, but that value can contain a delimited list, thus allowing multiple values to be passed.
→ For more information on how to pass and parse multivalued script parameters, see Special Edition Using FileMaker 8, Chapter 15 “Advanced Scripting Techniques.”
Script parameters can be specified when scripts are performed via buttons and via subscripts, but not when scripts are called manually from the Scripts menu or via a startup or shutdown script.
Examples:
In this example, the Navigate script is called, with the parameter "West":
Perform Script ["Navigate"; "West"]
Within the Navigate script, the script parameter value ("West") is assigned to a variable ("$direction") through the use of the following script step:
Set Variable ["$direction"; Get (ScriptParameter)]
$direction now equals West.
Get(ScriptResult)
Category: Get
Syntax: Get ( ScriptResult )
Parameters: None
Data type returned: Text, Number, Date, Time, Timestamp, Container
This function allows subscripts to pass results to their calling ("parent") script. Get(ScriptResult) returns whatever value was set by the Exit Script script step.
Note that once all scripts complete execution, no value is stored and Get(ScriptResult) will return a null (or blank) value.
Examples:
Consider a subscript that checks for duplicate records within a found set and passes a count of duplicates as a script result. Any number of scripts within a given solution can then call that subscript and perform a check like so:
Case ( Get(ScriptResult) > 1; "duplicates exist"; "no duplicates" )
Get(SortState)
Category: Get
Syntax: Get ( SortState )
Parameters: None
Data type returned: Number
Description:
Returns a number that represents the sort state of the active window.
Get (SortState) can be used in a customized interface where the Status Area is normally hidden from the user. Also, Get (SortState) can be used to correctly display sort icons in a customized interface.
A sorted found set becomes semi-sorted if new records are created. Omitting or deleting records does not cause the sort status to change, however. Subsummary reports may not show expected results when the found set is semi-sorted.
Get(StatusAreaState)
Category: Get
Syntax: Get ( StatusAreaState )
Parameters: None
Data type returned: Number
Description:
Returns a number that represents the state of the Status Area of the active window.
If you want a single test that will tell you whether the Status Area is hidden (regardless of whether it’s locked or not), use Mod (Get (StatusAreaState); 3). When this returns 0, the Status Area is hidden; when it’s anything else, the Status Area is visible.
Examples:
Get(SystemDrive)
Category: Get
Syntax: Get ( SystemDrive )
Parameters: None
Data type returned: Text
Description:
Returns the drive letter (Windows) or volume name (Mac OS) where the currently running operating system is located.
Examples:
Returns /C:/ in Windows when the operating system is on the C: drive.
Returns /MyDrive/ in the Mac OS when the operating system is on a volume named MyDrive.
Get(SystemIPAddress)
Category: Get
Syntax: Get ( SystemIPAddress )
Parameters: None
Data type returned: Text
Description:
Produces a return-delimited list of the IP addresses of all the machines connected to a NIC (Network Interface Controller) card.
Examples:
Returns 202.27.78.34, for example, when only one machine is connected.
Get(SystemLanguage)
Category: Get
Syntax: Get ( SystemLanguage )
Parameters: None
Data type returned: Text
Description:
Returns the language setting of the user’s machine. The returned text is the English language name for a language, regardless of system settings.
Examples:
Get(SystemNICAddress)
Category: Get
Syntax: Get ( SystemNICAddress )
Parameters: None
Data type returned: Text
Description:
Produces a return-delimited list containing the hardware addresses of all the NIC (Network Interface Controller) cards connected to the machine.
In Windows, you can find this address by typing ipconfig /All from a command prompt. On Mac OS X, you can find the NIC address by using the Apple System Profiler utility.
If the user’s machine has multiple NIC cards, Get (SystemNICAddress) generates a return-delimited list of all their addresses. You might, for instance, have both a built-in Ethernet card and a wireless networking card installed in a laptop. Or, a server might have multiple built-in Ethernet ports. In both of these cases, Get (SystemNICAddress) returns the addresses of both devices.
Examples:
Get(SystemPlatform)
Category: Get
Syntax: Get ( SystemPlatform )
Parameters: None
Data type returned: Number
Description:
Returns a number that represents the current platform on a user’s computer.
Because FileMaker tends to change or add to the values in the platform-checking function (as new versions of operating systems become supported), checks against this function should be performed in a single, central location for ease of future updates. The results of the function may be stored in a global variable during startup, and then referred to for subsequent platform checks throughout the rest of the database. We recommend using a custom function for this purpose.
The reason that this function returns negative numbers is for backward compatibility. Positive 1 and 2 were used for operating systems that are no longer supported by FileMaker Pro.
Get(SystemVersion)
Category: Get
Syntax: Get ( SystemVersion )
Parameters: None
Data type returned: Text
Description:
Returns the current operating system version level.
The values returned by Get (SystemVersion) will change as new versions of operating systems become available. As with checks against Get (SystemPlatform), you should try to perform tests of the system version in a single, central location within your files so that it will be easy to update in the future. We recommend using a custom function for this purpose.
Examples:
Get(TextRulerVisible)
Category: Get
Syntax: Get ( TextRulerVisible )
Parameters: None
Data type returned: Number
Description:
Returns a Boolean value by which to determine whether or not the text ruler is visible. Returns 1 if the text ruler is displayed, otherwise returns 0.
Examples:
Returns 1 when the text ruler is visible.
Get(TotalRecordCount)
Category: Get
Syntax: Get ( TotalRecordCount )
Parameters: None
Data type returned: Number
Description:
Returns the total number of records in the current table (not table occurrence), regardless of the state of the found set.
The Get (TotalRecordCount) function is most often used in unstored calculations and scripts. Be sure to navigate to a layout that establishes the correct table context before referencing the function.
The total record count includes records that have been created but not yet committed. If such records are reverted, the total record count is decreased.
Get(UserCount)
Category: Get
Syntax: Get ( UserCount )
Parameters: None
Data type returned: Number
Description:
Returns the number of clients currently accessing the file, including the current user.
Only FileMaker Pro client connections are counted by the Get (UserCount) function. Web, ODBC, and JDBC connections are not counted.
Examples:
If a file is hosted by FileMaker Server 7, Get (UserCount) returns the number of client connections to the current file and does not count the server.
Get(UserName)
Category: Get
Syntax: Get ( UserName )
Parameters: None
Data type returned: Text
Description:
Returns the username that has been established for the current user’s copy of FileMaker Pro. This username is specified on the General tab of the Preferences dialog and can be set to return either the system name or a custom name.
The returned name is the same for anyone opening any database on the machine, regardless of what account name and password they’ve used. It’s an application-level setting, not a document-level setting. The username can always be manually changed, regardless of whatever security you’ve set up. For these reasons we recommend against using it.
For greater security, use Get (AccountName) to track and manage user access; a user cannot change the account name used to log in to a database file.
Examples:
Get(UseSystemFormatsState)
Category: Get
Syntax: Get ( UseSystemFormatsState )
Parameters: None
Data type returned: Number
This function is used to determine if the option to Use System Formats (in the File Menu, File Options dialog, Text tab) is explicitly turned on. It returns a Boolean value representing the state of the Use System Formats option: 1 if Use System Formats is on, otherwise returns 0.
Note that developers can use the Set Use System Formats script step to control this setting as well.
Examples:
Returns 1 when Use System Formats is on.
Get(WindowContentHeight)
Category: Get
Syntax: Get ( WindowContentHeight )
Parameters: None
Data type returned: Number
Description:
Returns the height, in pixels, of the content area of the current window. The content area is the area inside a window’s frame, and doesn’t include the title bar, scroll bars, or the Status Area.
The relationship of the content area dimensions to the overall window dimensions are different on each platform.
→ For a thorough discussion on the differences, see Special Edition Using FileMaker 8, Chapter 15, “Advanced Scripting Techniques.”
Examples:
Get(WindowContentWidth)
Category: Get
Syntax: Get ( WindowContentWidth )
Parameters: None
Data type returned: Number
Description:
Returns the width, in pixels, of the content area of the current window. The content area is the area inside a window’s frame, and doesn’t include the title bar, scroll bars, or the Status Area.
The relationship of the content area dimensions to the overall window dimensions are different on each platform.
→ For a thorough discussion on the differences, see Special Edition Using FileMaker 8, Chapter 15, “Advanced Scripting Techniques.”
Examples:
Get(WindowDesktopHeight)
Category: Get
Syntax: Get ( WindowDesktopHeight )
Parameters: None
Data type returned: Number
Returns the height, in pixels, of the desktop space.
In Windows, the desktop space is the FileMaker Pro application window. Get (WindowDesktopHeight) measures the total vertical space used by the application window. If the application is maximized, the application window height is the screen height, minus the height of the Start menu (if it’s placed on the bottom of the screen).
On Mac OS X, the desktop space includes everything on the screen except the top menu bar and the Dock.
You cannot programmatically set the window desktop height or width, nor on Windows can you tell where the application window has been positioned on the user’s monitor.
Examples:
Get(WindowDesktopWidth)
Category: Get
Syntax: Get ( WindowDesktopWidth )
Parameters: None
Data type returned: Number
Description:
Returns the width, in pixels, of the desktop space.
In Windows, the desktop space is the FileMaker Pro application window. Get (WindowDesktopWindow) measures the total horizontal space used by the application window. If the application is maximized, the application window width is the screen width, minus the width of the Start menu (if it’s placed on the side of the screen).
On Mac OS X, the desktop space includes everything on the screen except the top menu bar and the Dock.
You cannot programmatically set the window desktop height or width, nor on Windows can you tell where the application window has been positioned on the user’s monitor.
Examples:
Get(WindowHeight)
Category: Get
Syntax: Get ( WindowHeight )
Parameters: None
Data type returned: Number
Description:
Returns the total height, in pixels, of the current window. The current window is usually the active window, but it’s also possible for a script to run in a window that isn’t the active foreground window.
The window height and width return the outside dimensions of a window. So, if you make a new window and specify a height and width of 300, then the Get (WindowHeight) and Get (WindowWidth) would both return 300.
Be aware that the window height and width are different from the window content height and width, which return the inside dimensions of a window.
Examples:
Get(WindowLeft)
Category: Get
Syntax: Get ( WindowLeft )
Parameters: None
Data type returned: Number
Description:
Returns the horizontal distance, in pixels, from the outer left edge of a window to the left edge of the application window on Windows or screen on Mac OS.
See Get (WindowDesktopHeight) for a discussion of how the application window is defined for each platform.
If any docked toolbars are placed along the left edge of the application window, the position of the origin shifts inward. The Get (WindowLeft) function is relative to the inside edge of the application window, inclusive of docked toolbars.
Get (WindowLeft) can return negative numbers. This may indicate the window is located on a second monitor positioned to the left of the first, or it may mean that a portion of the left side of the window is hidden.
Examples:
Get(WindowMode)
Category: Get
Syntax: Get ( WindowMode )
Parameters: None
Description:
Returns a number that indicates in what mode the active window is.
Returns 0 for Browse mode.
Returns 1 for Find mode.
Returns 2 for Preview mode.
Returns 3 if printing is in progress.
Notice that this function can never return a value indicating that a window is in Layout mode. If a script ever attempts to operate within the context of a window that is in Layout mode, the window is automatically switched to Browse mode and the script continues as expected.
Examples:
Get(WindowName)
Category: Get
Syntax: Get ( WindowName )
Parameters: None
Data type returned: Text
Description:
Returns the name of the active window.
The name of a window is the text string that appears in the window’s title bar. A window’s name can be specified when it is created with the New Window script step. It can also be altered with the Set Window Title script step.
Window names do not need to be unique. If a user manually creates a new window, the name of the new window will be the same as the active window at the time the user selected New Window, but will have a - 2 (or higher number if necessary) appended to it.
Examples:
Get(WindowTop)
Category: Get
Syntax: Get ( WindowTop )
Parameters: None
Data type returned: Number
Description:
Returns the vertical distance, in pixels, from the top edge of a window to the inside of the top of the application window.
See Get (WindowDesktopHeight) for a discussion of how the application window is defined for each platform.
If any docked toolbars are placed along the top of the application window, this shifts the location of the inside edge of the application window.
Get (WindowTop) can return negative numbers. This may indicate the window is located on a second monitor positioned above the first, or it may mean that a portion of the top of the window is hidden.
Examples:
Get(WindowVisible)
Category: Get
Syntax: Get ( WindowVisible)
Parameters: None
Data type returned: Number
Description:
Returns a number indicating whether the current window is visible or hidden.
Returns 1 if the window is visible.
Returns 0 if the window is hidden.
When you call a subscript in another file, it operates from the context of the front-most window in that file, but that window does not need to become the active window. The current window can therefore be different from the active, foreground window, and it can either be hidden or visible.
Examples:
Get(WindowWidth)
Category: Get
Syntax: Get ( WindowWidth )
Parameters: None
Data type returned: Number
Returns the total width, in pixels, of the current window. A window retains all its properties, such as height and width, even if it is hidden.
The window height and width measures the outside dimensions of a window, whereas the window content height and window content width measure the inside dimensions of a window.
Window height and width can be assigned when creating and resizing windows.
Examples:
Get(WindowZoomLevel)
Category: Get
Syntax: Get ( WindowZoomLevel )
Parameters: None
Data type returned: Text
Description:
Returns the zoom state (percentage) for the current window.
In Windows, an asterisk appears next to the zoom percentage when the Enlarge Window Contents to Improve Readability option is selected in the General tab of the Preferences dialog box.
Examples:
Returns 400 when the current window’s zoom percentage is set to 400.
Returns 200* in Windows when the current window’s zoom percentage is set to 200 and the Enlarge Window Contents to Improve Readability option is selected.
GetAsBoolean()
Category: Data
Syntax: GetAsBoolean ( data )
Parameters:
data—Any text, number, date, time, timestamp, or container, or a string containing text, a number, date, time, timestamp, or container.
Data type returned: Number
Description:
Returns a 1 if the expression or data passed into the function is not zero nor empty, otherwise zero and null values return 0.
GetAsBoolean() is somewhat different than the also common test not IsEmpty ( data ). The important distinction is that a GetAsBoolean() test will treat zero as a false result.
Examples:
GetAsCSS()
Category: Text
Syntax: GetAsCSS ( text )
text—Any expression that resolves to a text string.
Data type returned: Text
Description:
GetAsCSS() returns a representation of the specified text string, marked up with CSS (Cascading Style Sheet) tags. CSS can capture rich text formatting that has been applied to a text string.
Representing formatted text as CSS means that you can export stylized text and have it rendered properly by CSS-aware applications, such as web browsers.
GetAsCSS() is also useful within FileMaker itself because you can determine what special formatting, if any, has been applied to a field.
Examples:
The field myField contains Go Team and has manually been formatted as follows:
Font = Helvetica, Font Size = 36 points, Font Color = red, Font Style = bold.
GetAsCSS (myField) returns
<SPAN STYLE= "font-size: 36px;color: #AA0000;font-weight: bold; >
text-align: left;"Go Team</SPAN>
GetAsDate()
Category: Text
Syntax: GetAsDate ( text )
Parameters:
text—Any text expression or text field that returns a date, formatted the same as the date format on the system where the file was created.
Description:
GetAsDate() interprets a text string that contains a date as an actual date. Anytime you use a date constant within a calculation formula, you should use the GetAsDate() or Date() functions to ensure that the date is interpreted correctly.
Note: In order to avoid errors, we recommend always using four-digit years; however, GetAsDate ( “1/1” ) will resolve to 1/1/2006 assuming the current year is 2006. GetAsDate ( “1/1/05” ) will resolve to 1/1/0005.
Examples:
GetAsNumber()
Category: Text
Syntax: GetAsNumber ( text )
Parameters:
text—Any valid text expression that contains numbers.
Data type returned: Number
Description:
GetAsNumber() returns only the numbers from a text string, as a data type number. All non-numeric characters are dropped from the string.
Use GetAsNumber() to strip all non-numeric characters out of a text string. For instance, you might have a phone number field to which you want to apply some formatting. GetAsNumber (PhoneNumber) returns just the numeric characters from the field, stripping all punctuation and spaces, so that you can then apply whatever new formatting you wish.
GetAsNumber() can also be applied to date and time fields to coerce the data into its integer representation. For instance,
GetAsNumber (Get (CurrentDate))
returns 731689 when the date is 4/18/2004.
Examples:
GetAsSVG()
Category: Text
Syntax: GetAsSVG ( text )
Parameters:
text—Any expression that resolves to a text string.
Data type returned: Text
Description:
GetAsSVG() returns a representation of the text string, marked up in SVG (Scalable Vector Graphics) format. SVG can capture rich text formatting that has been applied to a text string.
SVG format can be used to transfer formatted text from FileMaker to other applications. You can also test an SVG-formatted version of a text string to determine what, if any, formatting has been applied to the string.
The field myField contains two phrases, Go Team and Hello World!, each with some text formatting applied to it.
GetAsSVG (myField)
Might return:
<StyleList>
< Style#0>"font-size: 36px;color: #AA0000;font-weight: bold;text-align: left;",
Begin: 1, End: 8</Style>
<Style#1>"color: #000000;font-weight: normal;font-style:normal;text-align:
left;", Begin: 9, End: 20</Style>
</StyleList>
<Data>
<Span style="0">Go Team </Span>
<Span style="1">Hello World!</Span>
</Data>
GetAsText()
Category: Text
Syntax: GetAsText ( data )
Parameters:
data—Any field or expression that returns a number, date, time, timestamp, or container.
Data type returned: Text
Description: GetAsText() returns the text equivalent of data in any other data type. You can then manipulate the data as you would any other text string.
When applied to a container field that stores a reference to an object, GetAsText() returns the path to the container data. If the container data is embedded in the database, GetAsText() returns a question mark.
One frequent use of GetAsText() is to get the path and filename information for container data stored as a reference. You can then parse the path information and build links to other objects in the same location.
In most cases, you do not need to explicitly coerce number, date, and time data into text before performing text operations on the data. Text functions operate on numbers, dates, and times as if they were text strings, even if you don’t wrap the data with GetAsText().
Examples:
GetAsTime()
Category: Text
Syntax: GetAsTime ( text )
Parameters:
text—Any text expression or text field containing a time.
Data type returned: Time
Description:
GetAsTime() returns the data specified in the text string as data-type time. The value can then be manipulated like any other time data.
GetAsTime() can be used to convert an integer representing a number of seconds into an elapsed time, as demonstrated in the third of the following examples.
Examples:
Using GetAsTime() when working with literal time strings in calculation formulas
GetAsTime ("15:30:00") - FinishTime
would yield the elapsed time between 3:30 p.m. and the FinishTime.
GetAsTimestamp()
Category: Text
Syntax: GetAsTimestamp ( text )
Parameters:
text—Any text string or expression that returns a text string that contains a timestamp.
Data type returned: Timestamp
Description:
GetAsTimestamp() converts a timestamp contained in a text string into a data-type timestamp. It can then be used in formulas as any other timestamp would be.
GetAsTimestamp() also converts numbers into timestamps. See the Timestamp() function for more information on how timestamps can be represented as numbers.
Use GetAsTimestamp() anytime you include a literal string containing a timestamp. For instance, to find out the amount of time that has elapsed between a fixed time in the past and now, you would use the following formula:
Timestamp (Get(CurrentDate); Get (CurrentTime) - GetAsTimestamp
("6/29/1969 4:23:56 PM"))
GetField()
Category: Logical
Syntax: GetField ( fieldName )
Parameters:
fieldName—A text string, or field or expression that returns a text string, which contains the name of a field.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
Returns the contents of the fieldName field.
Essentially, GetField() provides a level of abstraction when retrieving the contents of a field. Instead of saying “Give me the value of the FirstName field,” for instance, it’s like saying “Give me the value of the field whose name is in the gWhatField field.” By putting a different field name in the gWhatField field, you can retrieve the contents of a different field.
The Evaluate() function can always be used in place of the GetField(). In this example, for instance, Evaluate (gSelectColumn) and GetField (gSelectColumn) both return the same result. The opposite is not true, however. Evaluate() can perform complex evaluations and can have trigger conditions defined, whereas GetField() can retrieve only the contents of a field.
GetNextSerialValue()
Category: Design
Syntax: GetNextSerialValue ( fileName; fieldName )
Parameters:
fileName—A string or text expression that represents the name of an open file.
fieldName—A string or text expression that represents the name of the field for which to return results.
Data type returned: Text
Description:
Returns the next value for a field defined to auto-enter a serialized value.
It’s good practice to use the TableOccurrence::FieldName syntax to reference the field in this formula so that it can be evaluated in any context. Without explicitly naming a table occurrence, this function assumes the field can be found in the current context, which may not be the case. Because the auto-entered serial number is defined at the table level, it doesn’t matter which of a table’s occurrences you reference, as they will all return the same result.
GetNthRecord()
Category: Logical
Syntax: GetNthRecord ( fieldName; recordNumber )
Parameters:
fieldName—Any related field or repeating field, or an expression that returns a field or a repeating field.
recordNumber—An integer representing the record number from which you want data.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
Returns the contents of fieldName from the provided recordNumber.
Note that the rules governing storage and indexing for related calculation values apply to GetNthRecord() just as they do for other functions: The result of an expression containing GetNthRecord() will not update when a related value is referenced unless it is set to be an unstored calculation or unless the relationship is reset/refreshed somehow.
Examples:
GetRepetition()
Category: Repeating
Syntax: GetRepetition ( repeatingField; repetitionNumber )
Parameters:
repeatingField—Any repeating field, or an expression that returns a reference to a repeating field.
repetitionNumber—A positive integer representing the repetition number to retrieve.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
Returns the contents of the specified repetition of a repeating field.
A shorthand notation can be used in place of the GetRepetition() function. The repetition number can be placed in square brackets after the name of the repeating field. For instance, GetRepetition (myField; 6) is the same as myField[6].
Examples:
If you had a repeating text field called QuoteOfTheDay that contained 20 repetitions, you could extract a random quote using the following formula:
Let (repNumber = Ceiling (Random * 20) ;
GetRepetition (QuoteOfTheDay; repNumber)
)
GetSummary()
Category: Summary
Syntax: GetSummary ( summaryField; breakField )
Parameters:
summaryField—A field of type summary, or an expression that returns a reference to one.
breakField—A field, or an expression that returns a reference to one.
Data type returned: Number, Date, Time, Timestamp
Description:
The GetSummary() function returns the value of summaryField when summarized by breakField. The found set must be sorted by breakField for GetSummary() to return the proper value.
GetSummary() returns the same values that you would see if you were to place the specified summary field in a subsummary report. GetSummary() is necessary when you need to use summarized values in calculation formulas or for display purposes while in Browse mode.
Note, you could also achive similar results using a self-join relationship and aggregate functions.
To calculate a grand summary value, use the same summary field for both the summary field and the break field parameters.
→ For more detail on reporting and subsummary parts, see Special Edition Using FileMaker 8, Chapter 10, “Getting Started with Reporting.”
Examples:
Given the following record set, sorted by Country, and a summary field called Sum_Sales defined as the Total of Sales:
A field SalesByCountry defined as
GetSummary (Sum_Sales; Country)
returns 100,000 for the two U.S. records and returns 75,000 for the two China records.
GetValue()
Category: Text
Syntax: GetValue ( listOfValues; valueNumber )
Parameters:
listOfValues—A list of carriage return–delimited values.
valueNumber—The value to return from the list.
Data type returned: Text
Description:
Returns from a list of carriage return–delimited values a single value. Value lists in FileMaker are comprised of any line of data followed by a pilcrow or paragraph return character (¶). You can treat value lists as simple arrays and extract lines as needed using GetValue().
Examples:
Hiragana()
Category: Text
Syntax: Hiragana ( text )
text—Any text expression or text field.
Data type returned: Text (Japanese)
Description:
Converts written Japanese Katakana (Hankaku and Zenkaku) text to Hiragana.
Japanese has four written alphabets where it is possible to represent a syllable in a number of different ways. The Hiragana() function, along with the KanaHankaku(), KanaZenkaku(), and Katakana() functions, all enable conversion from one set of alphabetic glyphs to another.
Examples:
Hiragana (
) returns 
Hour()
Category: Time
Syntax: Hour ( time )
Parameters:
time—Any valid time value or expression that returns a valid time value.
Data type returned: Number
Description:
The Hour() function returns an integer representing the number of hours specified by the time parameter.
When its parameter represents a specific time of day, the Hour() function returns a value from 0 to 23. To map this into the more familiar 1 to 12 range, you can use the following formula:
Mod ( Hour (time) -1; 12 ) + 1
The Hour() function can return an integer value outside of the 0 to 23 range when its parameter represents a duration rather than a specific time of day. For instance, Hour (“65:12:53”) returns 65.
Examples:
If()
Category: Logical
Syntax: If ( test; result1; result2 )
Parameters:
test—A logical expression that returns True (1) or False (0); result1—The expression to evaluate if test is true; result2—The expression to evaluate if test is false.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
The If() function returns one of two possible results depending on whether the test supplied as the first parameter is true or false. Result1 is returned if the test is true; Result2 is returned if the test is false.
The test parameter should be an expression that returns a numeric or Boolean result. For numeric results, zero and null are both considered false; all other values are considered true.
If the test contains multiple conditions separated by “and” or “or,” FileMaker stops evaluating the conditions as soon as it can determine the overall truthfulness of the test. For instance, if the test parameter is IsEmpty(FieldA) and IsEmpty(FieldB), if Field A is not empty, there’s no way that the entire expression could be true. FileMaker will not evaluate the other condition involving FieldB, and will return the false result.
You can nest If() statements within one another, but it is usually more efficient to use a Case() statement rather than an If() in such cases.
Examples:
If( DayOfWeek (Get (CurrentDate))) = 1 ;
"It's Sunday, no work!"; // true result
"Get back to work!" // false result
)
If ( myFlagField; graphicTrue; graphicFalse)
Looks for a true value (non-zero, non-blank) in myFlagField, and displays the correct graphic container.
If (not IsEmpty (Taxable) and TaxRate > 0;
Price + (Price * TaxRate);
Price
)
Int()
Category: Number
Syntax: Int ( number )
Parameters:
number—Any expression that resolves to a numeric value.
Data type returned: Number
Returns the whole number (integer) part of the number parameter without rounding. Digits to the right of the decimal point are dropped.
Note that for positive numbers, Floor() and Int() return the same results; however, for negative numbers, Int() returns the next larger integer, whereas Floor() returns the next smaller integer.
There are many practical uses for the Int() function. For instance, given any date, to find the date of the Sunday preceding it, use the formula GetAsDate (Int (myDate/7) * 7). Similarly, to test whether an integer is odd or even, you can test whether Int (num/2) = num/2.
Examples:
IsEmpty()
Category: Logical
Syntax: IsEmpty ( expression )
Parameters:
expression—Typically a field name, but can be any valid FileMaker Pro calculation formula.
Data type returned: Number
Description:
Returns 1 (True) if the referenced field is empty or if the expression returns an empty string. Returns 0 (False) if the field or expression is not empty.
Remember that zero is a valid entry for a number field. If a number field contains a 0, it is not considered to be empty. If you need to test for zero or null, use GetAsBoolean().
Examples:
IsValid()
Category: Logical
Syntax: IsValid ( expression )
Parameters:
expression—Typically a field name, but can be any valid FileMaker Pro calculation formula.
Data type returned: Number
Description:
Returns either a 1 (True) or a 0 (False), depending on whether the field or expression returns valid data.
IsValid returns a 0 if there is a data type mismatch (for example, text in a date field) or if FileMaker cannot locate the table or field that is referenced.
Otherwise it returns 1, indicating that the data is valid.
Examples:
IsValidExpression()
Category: Logical
Syntax: IsValidExpression ( expression )
Parameters:
expression—A text string containing a calculation expression, or a field or expression that returns a text string that contains a calculation expression.
Data type returned: Number
Description:
Returns 1 (True) if the expression syntax is correct. Returns 0 (False) if the expression has a syntax error.
The IsValidExpression() function is often used in conjunction with the Evaluate() function to ensure that Evaluate() is passed a valid expression. For instance, if users are allowed to enter a formula into a field called myFormula, and you want to have another field express the results of that formula, you could use the following:
If (IsValidExpression (myFormula); Evaluate (myFormula); "Invalid formula: "
& TextColor (myFormula; RBG (255; 0; 0)))
An expression is considered invalid if it contains syntax errors or if any of the referenced fields cannot be found. Errors that might occur only during execution of the expression, such as record access restrictions, are not detected by the IsValidExpression() formula.
Examples:
KanaHankaku()
Category: Text
Syntax: KanaHankaku ( text )
Parameters:
text—Any text expression or text field.
Data type returned: Text (Japanese)
Description:
Converts Zenkaku Katakana to Hankaku Katakana.
Japanese has four written alphabets where it is possible to represent a syllable in a number of different ways. The KanaHankaku() function, along with the KanaZenkaku(), Hiragana(), and Katakana() functions, all enable conversion from one set of alphabetic glyphs to another.
Examples:
KanaHankaku (
) returns 
KanaZenkaku()
Category: Text
Syntax: KanaZenkaku ( text )
Parameters:
text—Any text expression or text field.
Data type returned: Text (Japanese)
Description:
Converts Hankaku Katakana to Zenkaku Katakana.
Japanese has four written alphabets where it is possible to represent a syllable in a number of different ways. The KanaZenkaku() function, along with the KanaHankaku(), Hiragana(), and Katakana() functions, all enable conversion from one set of alphabetic glyphs to another.
Examples:
KanaZenkaku (
) returns 
KanjiNumeral()
Category: Text
Syntax: KanjiNumeral ( text )
Parameters:
text—Any text expression or text field.
Data type returned: Text (Japanese)
Description:
Converts Arabic numerals to Kanji numerals.
In Japanese, numbers are represented by either the Arabic “123... etc.” character glyphs or by their Kanji equivalents. KanjiNumeral enables converting from Arabic to Kanji.
Note to convert in the opposite direction from Kanji to Arabic, one can use the GetAsNumber() function.
Examples:
KanjiNumeral (
) returns 
Katakana()
Category: Text
Syntax: Katakana ( text )
Parameters:
text—Any text expression or text field.
Data type returned: Text (Japanese)
Description:
Converts from Hiragana to Zenkaku Katakana.
Japanese has four written alphabets where it is possible to represent a syllable in a number of different ways. The Katakana() function, along with the Hiragana(), KanaHankaku(), and KanaZenkaku() functions, all enable conversion from one set of alphabetic glyphs to another.
Examples:
Katakana (
) returns 
Last()
Category: Repeating
Syntax: Last ( field )
Parameters:
field—Any repeating field or related field.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
If the specified field is a repeating field, Last() returns the value from the last valid, non-blank repetition. If the specified field is a related field, it returns the last non-blank value from the set of related records. The order of the set of related records is determined by the sort order of the relationship. If no sort order has been specified, then the creation order is used.
Examples:
When RepPercentage is a repeating field with the values .04, .05, and .06, Last (RepPercentage) returns .06.
Last (PhoneNumber::Number)
returns the most recent phone number entry, assuming no sort is specified for the relationship.
LayoutIDs()
Category: Design
Syntax: LayoutIDs ( fileName )
Parameters:
fileName—A string or text expression that represents the name of an open file. It can include a file extension, but doesn’t need one.
Data type returned: Text
Description:
Returns a carriage return–delimited list of all the internal layout IDs for the specified file. The list is ordered according to the current layout order, not the creation order.
LayoutIDs are assigned in sequential order beginning at 1. The original file’s LayoutIDs are retained when older databases are converted to FileMaker Pro 8.
LayoutNames()
Category: Design
Syntax: LayoutNames ( fileName )
Parameters:
fileName—A string or text expression that represents the name of an open file. It can include a file extension, but doesn’t need one.
Data type returned: Text
Description:
Returns a carriage return–delimited list of layout names for the specified file.
As with the LayoutIDs() function, the order of the layout names is determined by the current order of the layouts, not their creation order.
If you wanted to find out a particular layout’s ID (say, the Contact_Detail layout), you can use the LayoutNames() and LayoutIDs() functions together, as follows:
Let ([
LNs = LayoutNames (Get(FileName));
LIs = LayoutIDs (Get(FileName));
pos = Position (LNs; "Contact_Detail"; 1; 1);
num = PatternCount (Left(LNs, pos); "¶") + 1 ] ;
GetAsNumber (MiddleValues (LIs; num; 1))
)
Examples:
Left()
Category: Text
Syntax: Left ( text; numberOfCharacters )
Parameters:
text—Any expression that resolves to a text string.
numberOfCharacters—Any expression that resolves to a positive integer.
Data type returned: Text
Returns a string containing the first n characters from the specified text string, where n is the number specified in the numberOfCharacters parameter. If the string is shorter than numberOfCharacters, the entire string is returned. If numberOfCharacters is less than 1, an empty string is returned.
The Left() function is commonly used in text parsing routines to extract portions of a text string. It is often used in conjunction with other text functions. For example, to extract the City portion of a field (called “CSZ”) containing “City, State Zip” data, you could use the following formula:
Let (commaPosition = Position (CSZ; ","; 1; 1); Left (CSZ;
commaPosition - 1))
Examples:
LeftValues()
Category: Text
Syntax: LeftValues ( text; numberOfValues )
Parameters:
text—A return-delimited text string or expression that returns a return-delimited text string.
numberOfValues—Any positive number or expression that returns a positive number.
Data type returned: Text
Description:
Returns the specified number of items from the beginning of the text parameter.
The LeftValues() function returns the first n items from a return-delimited array, where n is the number specified in the text parameter. The items will themselves be a return-delimited array, and there will always be a trailing return at the end of the last item.
You can remove the trailing return in a number of ways. If you are extracting a single item from the beginning of a list, you can use the Substitute() function to remove any return characters—for instance, Substitute (LeftValues (text; 1); “¶” “”). You would not use this method when returning multiple items because the internal delimiters would be lost as well. Instead, the following function returns everything except the last character of the extracted list:
Let (x = LeftValues (text; n); Left (x; Length (x) - 1 ))
Another option is the following:
LeftWords (LeftValues (text; n); 999999)
This function takes advantage of the fact that the LeftWords() function ignores any leading or trailing delimiters. Be aware that this function also ignores leading or trailing delimiters (including punctuation symbols) from the actual items in the array, so in some cases this function does not return the desired result. The safest function to use in all cases is the Let() function (described ahead).
Examples:
LeftWords()
Category: Text
Syntax: LeftWords ( text; numberOfWords )
Parameters:
text—Any expression that resolves to a text field.
numberOfWords—Any positive number or expression that returns a positive number.
Data type returned: Text
Description:
Returns the first n number of words in a text expression, where n is the number specified in the numberOfWords parameter.
Be aware of what symbols are considered to be word breaks by FileMaker Pro. Spaces, return characters, and most punctuation symbols are considered to be word breaks. Multiple word breaks next to each other (for example, two spaces, a comma, and a space) are considered as a single word break.
Certain punctuation symbols are word breaks when separating alpha characters, but not when separating numeric characters. These include the colon (:), slash (/), period (.), comma (,), and dash (-). For instance, LeftWords (“54.6”; 1) returns 54.6, but LeftWords (“x.y”; 1) returns x. The reason for this behavior is that those symbols are valid date, time, and number separators.
Leading and trailing punctuation around a word may be ignored by the LeftWords() function. For example, LeftWords (“John Q. Public, Jr.”; 2) returns John Q, but LeftWords (“John Q. Public, Jr.”; 3) returns John Q. Public.
Examples:
Length()
Category: Text
Syntax: Length ( text )
Parameters:
text—Any expression that resolves to a text string.
Data type returned: Number
Description:
Returns the number of characters in the specified text string. Numbers, letters, punctuation, spaces, and carriage returns are all considered as characters.
Length() also serves a second function in that it returns the byte size of the object data found in a container field.
The Length() function is often used as part of data validation rules. For instance, if you want to make sure that users enter phone numbers with either 7 or 10 digits, you could set up a validation by calculation rule as follows:
Length ( GetAsNumber ( Phone )) = 7 or Length ( GetAsNumber ( Phone )) = 10
Examples:
Length()
Category: Logical
Syntax: Let ( {[} var1=expression1 {; var2=expression2 ...] }; calculation )
Parameters:
var(n)—Any valid variable name. The rules for naming variables are the same as for defining fields.
expression(n)—Any calculation formula, the results of which are assigned to the var(n) variable.
calculation—Any calculation formula.
Parameters in curly braces { } are optional and may be repeated as needed, separated by a semicolon.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
The Let() function enables you to declare local variables within a calculation formula. The variables exist only within the boundaries of the Let() function itself.
The first parameter of the Let() function is a list of variable names and expressions. If multiple variables are declared, the list needs to be enclosed in square brackets. The items in the list are separated by semicolons. The variables are set in the order in which they appear. This means that you can use previously defined variables as part of the expression to define another variable.
The final parameter, calculation, is some expression that you want to evaluate. That formula can reference any of the variables declared in the first half of the function.
Duplicate variable names are allowed and variables can be named the same as existing fields. If this happens, the value assigned to the variable, not the field, will be used in future references to the variable within the Let() function.
Let() can be used to simplify complex, nested calculation formulas. We cannot advocate its use strongly enough.
In the case where a subexpression is used many times within a formula, the Let() function may also provide a performance benefit because the subexpression is evaluated only once when it is assigned to the variable.
You can also use the Let() function to set script variables like so:
Let ([
$var = 100;
$$var = 500
];
expression
)
Keeping track of functions that overlap scope—in this case overlapping the scope of a calculation expression with that of script or global variables—can often lead to code that is extrememly difficult to work with and maintain. While the preceding is entirely possible, we generally do not recommend it as a practice.
Note also that it is possible to use the Let() function to set a local script variable while no script is running. This in effect allows you to manipulate variables in a “null script” space that then do not exist while scripts run. A fairly obscure point, but interesting nonetheless.
Examples:
The following formula extracts the domain name from an email address:
Let([
start = Position (eMail ;"@" ;1 ;1);
numberOfCharacters = Length (eMail) - start];
Right ( eMail; numberOfCharacters)
)
The following example produces a summary of a student’s grades:
Let ([
TotalGradePoints = Sum (Grades::GradePoints);
CreditPoints = Sum (Classes::CreditPoints);
GPA = Round (TotalGradePoints/CreditPoints; 2)] ;
"Total Grade Points: "& TotalGradePoints & "¶" &
"Available Credit Points: " & CreditPoints & "¶" &
"Your GPA is: " & GPA
)
The final example formula returns the volume of a pyramid:
Let(
SideOfBase = 2 * Sqrt(2 * SlantHeight^2 - Height^2;
SideOfBase^2 * Height/3
)
Lg()
Category: Number
Syntax: Lg ( number )
Parameters:
number—Any expression that resolves to a positive numeric value or a field containing a numeric expression.
Data type returned: Number
Description:
Returns the base-2 logarithm of number. Negative values for number return an error.
The base-2 logarithm (often called the binary logarithm) of a number is the power of 2 that you would need to generate the number. Thus, if 2^x = y, then Lg(y) = x. The value returned by the Lg() function is increased by 1 every time that x is doubled.
Lg (1) = 0
Lg (2) = 1
Lg (32) = 5
Ln()
Category: Number
Syntax: Ln ( number )
Parameters:
number—Any positive number or expression that returns a positive number.
Data type returned: Number
Description:
Returns the natural logarithm of the specified number. The natural logarithm uses the transcendental number e as its base. The value of e is approximately 2.71828.
Exp() and Ln() are inverse functions of one another.
The Log() and Lg() functions produce base-10 and base-2 logarithms, respectively. The Ln() function produces base-e logarithms, but it can also be used to solve a logarithm of any base. Log (base-x) of y is equivalent to Ln (y) / Ln (x).
Examples:
Log()
Category: Number
Syntax: Log ( number )
Parameters:
number—Any positive number or expression that returns a positive number.
Data type returned: Number
Description:
Returns the base-10 logarithm of number.
Logarithms are used to determined the power to which a number must be raised to equal some other number. If xn = y, then n = Logx(y). The Log() function assumes a base (the variable x in the preceding formula) of 10. The Lg() function uses a base of 2, whereas the Ln() function uses a base of e.
The Log() and Lg() functions produce base-10 and base-2 logarithms, respectively. The Ln() function produces base-e logarithms, but it can also be used to solve a logarithm of any base. Log (base-x) of y is equivalent to Ln (y) / Ln (x).
Examples:
Lookup()
Category: Logical
Syntax: Lookup ( sourceField {; failExpression })
Parameters:
sourceField—Any related field.
failExpression—An expression to evaluate and return if the lookup fails. This is an optional parameter.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
Returns the contents of sourceField, or if no related record is found, the result of the failExpression. The table containing the sourceField must be related to the table where the Lookup() is defined.
A calculation field that contains a Lookup() function can be stored or unstored. If it is unstored, then anytime the sourceField changes, the calculation field updates. If the calculation is stored, which is typically why you want to use a Lookup in the first place, then changes to the sourceField do not cascade automatically through to the calculation field. Lookup() is retriggered when any of the relationship’s match fields (in the current table, not the source table) are modified, or when a relookup is triggered on any of those fields.
Lookup() is invaluable for addressing performance issues caused by interacting with related (and hence unindexed) values.
Examples:
Imagine you have a stored calculation field in an Invoice table called CustomerNameLookup, defined as follows:
Lookup (Customer::CustomerName;"<Missing Customer>")
Assume that the Invoice and Customer tables are related on the CustomerID. Whenever the CustomerID field is modified in the Invoice table, this triggers the lookup, and the name of the customer is copied into CustomerNameLookup. If an invalid CustomerID is entered, <Missing Customer> is returned. Because CustomerNameLookup is stored, indexed searches can be performed on it.
Be aware, however, that if the CustomerName field is updated in the Customer table, the change does not cascade automatically through to the Invoice table.
LookupNext()
Category: Logical
Syntax: LookupNext ( sourceField; lower/higher Flag )
Parameters:
sourceField—Any related field.
lower/higher Flag—Keyword that indicates whether to take the next lower or higher value if no direct match is found.
Data type returned: Text, Number, Date, Time, Timestamp, Container
Description:
Returns the contents of sourceField, or if no related record is found, the next lower or higher match value. The table containing the sourceField must be related to the table where the LookupNext() function is defined.
The LookupNext() function is very similar to the Lookup() function; they differ only in how they handle the case of no matching record. The Lookup() function returns a fail expression in such cases, whereas the LookupNext() returns the value associated with the next lower or higher match.
The Lower and Higher flags are keywords and should not be placed in quotation marks.
See the Lookup() function to learn about how a lookup is triggered and how the storage options determine how often the LookupNext() function is to be refreshed.
Looking up a value from the next higher or lower matching record is desirable when mapping a continuous variable onto a categorical variable. Think, for instance, of how student grades typically map to letter grades. A grade of 90 to 100 is considered an A, 80 to 89 is a B, 70 to 79 is a C, and so on. The percentage value is a continuous variable, whereas the letter grades are categorical.
Using the Lookup() function, if you wanted to use the student’s percentage score to retrieve the letter grade, you would need to have records for every possible combination of percentage and letter grade.
The LookupNext() function makes it possible to have records representing only the border conditions. For student grades, you would need to have five records in your lookup table: 90 is an A, 80 is a B, 70 is a C, 60 is a D, and 0 is an F. You could then relate a student’s percentage score to this table, and define the following formula as the StudentLetterGrade:
LookupNext (GradeLookup::LetterGrade; Lower)
Given a percentage score of 88, which has no exact match, the next lower match would return a letter grade of B.
Examples:
Lower()
Category: Text
Syntax: Lower ( text )
Parameters:
text—Any expression that resolves to a text string.
Data type returned: Text
Description:
Returns an all-lowercase version of the specified text string.
The Lower() function is one of three functions FileMaker has for changing the case of a text string. The other two are Upper() and Proper().
The following formula can be used to test whether a given text string is already written with all lowercase characters:
Exact (text; Lower(text))
Max()
Category: Aggregate
Syntax: Max ( field {; field...} )
Parameters:
field—Any related field, repeating field, or set of non-repeating fields that represent a set of numbers. Parameters in curly braces { } are optional and may be repeated as needed, separated by a semicolon.
Data type returned: Text, Number, Date, Time, Timestamp
Description:
Returns the largest valid, non-blank value from the set of values specified by the field parameter.
When the parameter list consists of two or more repeating fields, Max() returns a repeating field in which the corresponding repetitions from the specified fields are evaluated separately. So, if a field Repeater1 has three values, 16, 20, and 24, and another field, Repeater2, has two values, 14 and 25, Max (Repeater1; Repeater2) would return a repeating field with values 16, 25, and 24.
Because dates, times, and timestamps are represented internally as numbers, the Max() function can be used to compare data of these data types. For instance, to return the later of two dates, you could use the following type of formula:
GetAsDate (Max (Date (4; 1; 2005); Get (CurrentDate)))
This would return either 4/1/2005 or the current date, whichever is greater.
Middle()
Category: Text
Syntax: Middle ( text; startCharacter; numberOfCharacters )
Parameters:
text—Any expression that resolves to a text string.
startCharacter—Any expression that resolves to a numeric value.
numberOfCharacters—Any expression that resolves to a numeric value.
Data type returned: Text
Description:
Returns a substring from the middle of the specified text parameter. The substring begins at startCharacter and extracts the numberOfCharacters characters following it. If the end of the string is encountered before the specified number of characters has been extracted, the function returns everything from the start position though the end of the string.
The Middle() function is often used in conjunction with other text functions as part of text parsing routines. For instance, if you had a field containing city, state, and ZIP data where the entries were consistently entered as “city, state zip”, you could extract the state portion of the string with the following formula: Let ([commaPosition = Position(CSZ; ","; 1; 1); Middle
(CSZ; commaPosition + 2; 2))
MiddleValues()
Category: Text
Syntax: MiddleValues ( text; startingValue; numberOfValues )
Parameters:
text—Any return-delimited string or expression that generates a return-delimited string.
startingValue—Any positive integer or expression that returns a positive integer.
numberOfValues—Any positive number or expression that returns a positive integer.
Data type returned: Text
Description:
Returns the specified number of items from the middle of the text parameter, starting at the value specified in the startingValue parameter.
The MiddleValues() function returns a slice from the middle of a return-delimited array. The output itself will be a return-delimited array, and there will always be a trailing return at the end of the last item.
See the LeftValues() function for a discussion of methods to remove the trailing return from the output of the MiddleValues() function.
The MiddleValues() function is used frequently in scripts to loop over the items in an array. Each time through the loop, you can extract the next value from the array by incrementing a counter. For instance: Set Variable [ $counter; 1 ]
Loop
Set Variable [$value; MiddleValues ( $myArray; $counter; 1 )
{ ... some set of operations involving the extracted item }
Exit Loop If [$counter = ValueCount ( $myArray )]
End Loop
Examples:
MiddleWords()
Category: Text
Syntax: MiddleWords ( text; startingWord; numberOfWords )
Parameters:
text—Any expression that resolves to a text string.
startingWord—Any positive number or expression that returns a positive number.
numberOfWords—Any positive number or expression that returns a positive number.
Data type returned: Text
Description:
The MiddleWords() function extracts a substring from the middle of a text string. The substring begins with the nth word of the text string (where n represents the startingWord parameter) and extends for the number of words specified by the third parameter.
MiddleWords( text; 1; 1) and LeftWords( text; 1) are equivalent functions.
Be aware of what symbols are considered to be word breaks by FileMaker Pro. Spaces, return characters, and most punctuation symbols are considered to be word breaks. Multiple word breaks next to each other (for example, two spaces, a comma, and a space) are considered as a single word break.
Certain punctuation symbols are word breaks when separating alpha characters but not when separating numeric characters. These include the colon (:), slash (/), period (.), comma (,), and dash (-). The reason for this behavior is that those symbols are valid date, time, and number separators.
Leading and trailing punctuation around a word may be ignored by the MiddleWords() function. For example, MiddleWords (“John Q. Public, Jr.”; 2; 1) returns Q, but MiddleWords (“John Q. Public, Jr.”; 2; 1) returns Q. Public.
Examples:
Min()
Category: Aggregate
Syntax: Min ( field {; field...} )
Parameters:
field—Any related field, repeating field, or set of non-repeating fields that represent a set of numbers. Parameters in curly braces { } are optional.
Data type returned: Text, Number, Date, Time, Timestamp
Description:
Returns the lowest valid, non-blank value from the set of values specified by the field parameter.
When the parameter list consists of two or more repeating fields, Min() returns a repeating field in which the corresponding repetitions from the specified fields are evaluated separately. So, if a field Repeater1 has three values, 16, 20, and 24, and another field, Repeater2, has two values, 14 and 25, Min (Repeater1; Repeater2) would return a repeating field with values 14, 20, and 24.
Because dates, times, and timestamps are represented internally as numbers, the Min() function can be used to compare data of these data types. For instance, to return the earlier of two dates, you could use the following type of formula:
GetAsDate (Min (Date (4; 1; 2005); Get (CurrentDate)))
This example would return either 4/1/2005 or the current date, whichever is less.
Examples:
Minute()
Category: Time
Syntax: Minute ( time )
Parameters:
time—Any valid time value or expression that returns a valid time value.
Data type returned: Number
Description:
The Minute() function returns an integer representing the number of minutes from the given time value.
The Minute() function always returns an integer in the range from 0 to 59. If you want the output of this function to be expressed always as a two-character string (for example, 07 instead of 7 when the time is 4:07 p.m.), use the following formula:
Right ("00" & Minute (time); 2)
Examples:
Mod()
Category: Number
Syntax: Mod ( number; divisor )
number—Any expression that resolves to a numeric value.
divisor—Any expression that resolves to a numeric value.
Data type returned: Number
Description:
Returns the remainder after number is divided by divisor.
Mod() is related to the Div() function; Div() returns the whole number portion of x divided by y, whereas Mod() returns the remainder.
There are many practical uses for the Mod() function. For instance, when x is an integer, Mod (x; 2) returns 0 if x is even, and 1 if x is odd. Mod (x; 1) returns just the decimal portion of a number.
Examples:
Month()
Category: Date
Syntax: Month ( date )
date—Any valid date (1/1/0001–12/31/4000). The parameter should be a string containing a date (for example, "3/17/2004"), an expression with a date result (for example, Date (6, 29, 1969)), or an integer that represents a serialized date value (for example, 718977).
Data type returned: Number
Description:
Returns the month number (1–12) for any valid date (1/1/0001–12/31/4000).
The numeric value returned by Month() can be used in mathematical calculations as well as within the Date() function to construct a new date.
One common use of the Month() function is to build a formula that returns the quarter of a given date: Case(
Month(myDate) < 4, "First Quarter",
Month(myDate) < 7, "Second Quarter",
Month(myDate) < 9, "Third Quarter",
"Fourth Quarter"
)
Examples:
