FileMaker Pro 11 ® Functions Reference
© 2007–2010 FileMaker, Inc. All Rights Reserved. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker and Bento are trademarks of FileMaker, Inc. registered in the U.S. and other countries. The file folder logo and the Bento logo are trademarks of FileMaker, Inc. All other trademarks are the property of their respective owners. FileMaker documentation is copyrighted.
Contents Aggregate functions Average . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Count . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Max . . . . . . . . . . . . . . . .
External functions External . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Financial functions FV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 NPV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 PMT . . . . . . . . . .
Get(LayoutCount) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Get(LayoutName) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Get(LayoutNumber) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 Get(LayoutTableName) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Logical functions Case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Choose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Evaluate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 EvaluationError . . . . . . . . . . . . . . . . . .
Exact . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 FilterValues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 GetAsCSS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
TextStyleAdd . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238 TextStyleRemove . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 Time functions Hour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242 Minute . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
About functions About functions Working with formulas and functions A function is a predefined, named formula that performs a specific calculation and returns a single, specific value. Most functions include three basic parts: • the function • a set of parentheses, if the function takes parameters • the parameters required by the function Each function returns a result of field type text, number, date, time, timestamp, or container. A formula calculates a single value, based on constants (such as 1.
Functions reference (alphabetical list) Functions reference (alphabetical list) A, B, C Abs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Acos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 Asin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Functions reference (alphabetical list) G Get(AccountExtendedPrivileges) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 Get(AccountName) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Get(AccountPrivilegeSetName) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Get(ActiveFieldContents) . . . . . . . . . . . . . . . . . . . . .
Functions reference (alphabetical list) Get(QuickFindText) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Get(RecordAccess) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Get(RecordID) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Get(RecordModificationCount) . . . . . . . . . . . .
Functions reference (alphabetical list) GetLayoutObjectAttribute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 GetNextSerialValue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 GetNthRecord . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 GetRepetition . . . . . . . . . . . . . . . . . . . .
Functions reference (alphabetical list) PatternCount . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 Pi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250 PMT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Position . . . . . . . . . .
Functions reference (alphabetical list) Trim . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 TrimAll . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Truncate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 Upper . . . . . . . . . . .
Alphabetical headings for functions reference: A, B, C D E F G H, I, J, K L, M, N, O P, Q R S T, U V, W, X, Y, Z Aggregate functions Aggregate functions perform statistical analysis on numbers (and also dates or times for some functions) in: • several fields in a record. • related fields whether displayed in a portal or not. • repeating fields. For example, you can use the Sum function to add the values listed in a portal, as an alternative to creating a report with grouped data and subtotals.
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. Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Field can be any of the following: • a repeating field (repeatingField). • a field in matching related records specified by (table::field), whether or not these records appear in a portal.
Count Purpose Returns the number of valid, non-blank values in field. Format 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. Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Field can be any of the following: • a repeating field (repeatingField).
Count(Field2) returns 4 when the calculation isn’t a repeating field. Count(Field1;Field2;Field3) returns 3, 2, 1, 1 when the calculation is a repeating field. Note When a referenced field is a repeating field, the Count function returns the total number of valid, non-blank values in the first repetition field, then the number of valid, non-blank values in the second repetition field, and so on.
Examples In the following examples: • Field1 contains white. • Field2 contains black. • Field3 contains three repetitions with values of red, green, blue. • Related::Field4 refers to three records that contain 100, 200, 300. Note When referencing multiple repeating fields, List() returns the list of values across the first repetition in the calculation's first repetition, then the list of values across the second repetition in the second repetition, and so on.
Max Purpose Returns the highest valid value in field. Format Max(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. Data type returned text, number, date, time, timestamp Originated in FileMaker Pro 6.0 or earlier Description Field can be any of the following: • a repeating field (repeatingField).
Note When a referenced field is a repeating field, the Max function returns the maximum value in the first repetition field, then the maximum value in the second repetition field, and so on. Related topics Contents Functions reference (alphabetical list) Min Purpose Returns the smallest valid non-blank value in field. Format Min(field{;field...
• Field1 contains two repetitions with values of 1 and 2. • Field2 contains four repetitions with values of 5, 6, 7, and 8. • Field3 contains 6. Min(Field2) returns 5 when the calculation isn’t a repeating field. Min(Field1;Field2;Field3) returns 1, 2, 7, 8 when the calculation is a repeating field. Note When a referenced field is a repeating field, the Min function returns the minimum value in the first repetition field, then the minimum value in the second repetition field, and so on.
Examples A portal displays the related values 5, 6, 7, and 8 in a field called Scores. StDev(table::Scores) returns 1.29099444.... In the following examples: • Field1 contains two repetitions with values of 1 and 2. • Field2 contains four repetitions with values of 5, 6, 7, and 8. • Field3 contains four repetitions with values of 6, 0, 4, and 4. • Field4 contains one repetition with a value of 3. StDev(Field4) results in an error because standard deviation of a single number is not defined.
• a field in matching related records specified by (table::field), whether or not these records appear in a portal. • several non-repeating fields in a record (field1;field2;field3...). • corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats. • several fields in the first matching record specified by (table::field1;table::field2;...).
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. Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Field can be any of the following: • a repeating field (repeatingField). • a field in matching related records specified by (table::field), whether or not these records appear in a portal.
Related topics Contents Functions reference (alphabetical list) Variance Purpose Returns the variance of a sample represented by a series of non-blank values in field. Format Variance(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. Data type returned number Originated in FileMaker Pro 7.
• Field1 contains two repetitions with values of 1 and 2. • Field2 contains four repetitions with values of 5, 6, 7, and 8. • Field3 contains four repetitions with values of 6, 0, 4,and 4. • Field4 contains one repetition with a value of 3. Variance(Field4) results in an error since the variance of a single value is not defined. Variance(Field1;Field2;Field3) returns 7, 9.33333333..., 4.5, 8 if the calculation is a repeating field. Student example: Two classes of students take an exam.
• a field in matching related records specified by (table::field), whether or not these records appear in a portal. • several non-repeating fields in a record (field1;field2;field3...). • corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats. • several fields in the first matching record specified by (table::field1;table::field2;...).
Date functions Date functions calculate dates and manipulate date information. Important To avoid errors when using dates, always use four-digit years. Note System formats affect the way dates are displayed. You can use zero (0) and negative numbers as Date function arguments. For example, the following formula returns 5/31/2010: Date(6;0;2010) Click a function name for details. This function Returns Date The calendar date for the specified month, day, and year.
year - the year (four digits between 0001 and 4000. For example, 2010 but not 10). Important The order of the parameters in the Date function is always Month, Day, Year, no matter what operating system or FileMaker Pro date formats you are using. Data type returned date Originated in FileMaker Pro 6.0 or earlier Description The format of the result depends 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.
Parameters date - any calendar date Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Use Day, for example, to identify the day of the month on which payments are due. Important To avoid errors when using dates, always use four-digit years. Examples Day(“5/15/2010”) returns 15. This example assumes that the system date format is MM/DD/ YYYY. Day(DateSold) returns the day of the month stored in DateSold.
Examples DayName(Date(10;5;2010)) returns Tuesday. DayName(ProjectDue) returns Tuesday when ProjectDue is 10/5/2010. DayName(“10/5/2010”) returns Tuesday. “Return your selection by ” & DayName(DueDate) displays the text Return your selection by followed by the name of the day stored in DueDate. Important To avoid errors when using dates, always use four-digit years.
DayOfWeek Purpose Returns a number representing the day of the week that date falls on. Format DayOfWeek(date) Parameters date - any calendar date Data type returned number Originated in FileMaker Pro 6.0 or earlier Description The number 1 represents Sunday, 2 represents Monday, 3 represents Tuesday, and so on. For example, you can find out on what day of the week a holiday occurs. Important To avoid errors when using dates, always use four-digit years. Examples DayOfWeek(“10/6/2010”) returns 4.
Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples DayOfYear(Billing Date) returns 32, when Billing Date is 2/1/2010. The following formulas return the total number of days in the current year: DayOfYear(Date(12;31;Year(Get(CurrentDate)))) DayOfYear(Date(1;1;Year(Get(CurrentDate)) + 1) -1) Important To avoid errors when using dates, always use four-digit years.
Important To avoid errors when using dates, always use four-digit years. Contents Functions reference (alphabetical list) MonthName Purpose Returns the full name of the month for date. Format MonthName(date) Parameters date - any calendar date Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples MonthName(“6/6/2010”) returns June.
Format MonthNameJ(date) Parameters date - any calendar date Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples MonthNameJ(“6/6/2010”) returns Important To avoid errors when using dates, always use four-digit years. Related topics Contents Functions reference (alphabetical list) WeekOfYear Purpose Returns the number of weeks after January 1 of the year of date.
Important To avoid errors when using dates, always use four-digit years. WeekOfYear(“1/1/2010”) returns 1. WeekOfYear(ProjectDue) returns 6, when ProjectDue is 2/4/2010. Related topics Contents Functions reference (alphabetical list) WeekOfYearFiscal Purpose Returns a number between 1 and 53 representing the week containing date, figured according to startingDay.
WeekOfYearFiscal(Date(1;7;2008);1) returns 1. WeekOfYearFiscal(Date(1;1;2009);5) returns 1. Related topics Contents Functions reference (alphabetical list) Year Purpose Returns a number representing the year in which date occurs. Format Year(date) Parameters date - any calendar date Data type returned number Originated in FileMaker Pro 6.0 or earlier Description You can, for example, extract the year from a field containing the date an item was sold.
Format YearName(date;format) Parameters date - any calendar date format - a number (0, 1, or 2) that describes the display format Data type returned text Originated in FileMaker Pro 6.0 or earlier Description If the value for format is blank or other than 0, 1, or 2, then 0 is used. 08 (Meiji 8), xxxx [before 1868.9.8]) 1- 8 (Mei 8), 8 (Taisho 8), 8 (Tai 8), 8 (Sho 8), 8 (Showa 8), 8 (Hei 8), 8 (Heisei 8), (Seireki xxxx (Sei xxxx [before 1868.9.8]) 2 - M8, T8, S8, H8, A.D.xxxx (before 1868.9.
Design functions Design functions return information about the structure of open database files. For example, you could determine the names of all the layouts or fields in an open database file. Note FileMaker Pro limits the information returned by a design function, according to the privilege set in effect when the function evaluates a database file.
This function Returns TableNames A list of the names of all defined tables in the specified database file, separated by carriage returns. ValueListIDs A list of all value list IDs in the specified database file, separated by carriage returns. ValueListItems A list of the values in the specified value list, separated by carriage returns. ValueListNames A list of the names of all value lists in the specified database file, separated by carriage returns.
Related topics Contents Functions reference (alphabetical list) FieldBounds Purpose Returns in a non-repeating text field the location in pixels of each side of fieldName and its rotation in degrees on layoutName in the fileName file. Format FieldBounds(fileName;layoutName;fieldName) Parameters fileName - the name of an open database file (local or remote). layoutName - the name of a layout in the specified database file. fieldName - the name of a field on the specified layout.
48 65 36 295 Related topics Contents Functions reference (alphabetical list) FieldComment Purpose Returns the specified field’s comment. Format FieldComment(fileName;fieldName) Parameters fileName - the name of an open database file (local or remote). fieldName - the name of a field in the specified database file. Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 7.
Examples FieldComment("Customers"; "Phone Number") returns “Customer's home telephone number” if it was entered as a comment for the Phone Number field. FieldComment("Customers"; "Accounts::Current Balance") returns “Customer's current balance” if it was entered as a comment for the Current Balance field in the Accounts table. Related topics Contents Functions reference (alphabetical list) FieldIDs Purpose Returns a list of all field IDs in fileName and layoutName, separated by carriage returns.
FieldNames Purpose Returns a list of the names of all fields on layoutName, in fileName file, separated by carriage returns. Format FieldNames(fileName;layoutName) Parameters fileName - the name of an open database file (local or remote). layoutName - the name of a layout or table in the specified database file. Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 6.
FieldRepetitions Purpose Returns the number of repetitions of the repeating field fieldName as it is currently formatted on layoutName (which could be different from the number of repetitions when the field was defined), and the orientation of the field repetitions (horizontal or vertical) on the layout. Format FieldRepetitions(fileName;layoutName;fieldName) Parameters fileName - the name of an open database file (local or remote). layoutName - the name of a layout in the specified database file.
fieldName - the name of a field on the specified layout. Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 6.0 or earlier Description If the field has a value list associated with it, the FieldStyle function also returns the name of the value list. • A standard field returns Standard. • A standard field with a vertical scroll bar returns Scrolling. • A drop-down list returns Popuplist. • A pop-up menu returns Popupmenu.
Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Field names must be in the format tablename::fieldname to specify a field that exists in a table different from the current table. The result has four values separated by spaces: • The first value is either Standard, StoredCalc, Summary, UnstoredCalc, or Global. • The second value is the field type: text, number, date, time, timestamp, or container.
fieldName - the name of the field whose next serial number you want to determine. Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Field names must be fully qualified in the format tablename::fieldname to specify a field that exists in a table different from the current table. Examples GetNextSerialValue(“Customers”;”CustID”) returns the next serial number for the CustID field.
Examples LayoutIDs(“Customers”) returns a list of all the layout IDs in the Customers database file. Related topics Contents Functions reference (alphabetical list) LayoutNames Purpose Returns a list of the names of all layouts in fileName, separated by carriage returns. Format LayoutNames(fileName) Parameters fileName - the name of an open database file (local or remote). Important See Design functions for information about literal text parameters.
Parameters fileName - the name of an open database file (local or remote). layoutName - the name of a layout in the specified database file. Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 8.5 Description Layout objects without object names are not returned. If layoutName isn’t specified, then no object names are returned.
Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Values in a list are separated by carriage returns, and lists are separated by two carriage returns. For each additional relationship connected to tableName, an additional list of four values is output. The four values are: • Source: Data Source Name of the database table connected to tableName. • Table: the name of the table connected to tableName.
Addresses::DateMovedIn >= Employees::DateOfHire Related topics Contents Functions reference (alphabetical list) ScriptIDs Purpose Returns a list of all script IDs in fileName, separated by carriage returns. Format ScriptIDs(fileName) Parameters fileName - the name of an open database file (local or remote). Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 6.
Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples ScriptNames(“Customers”) returns a list of all the scripts in the Customers database file. Related topics Contents Functions reference (alphabetical list) TableIDs Purpose Returns a list of all table IDs in fileName, separated by carriage returns. Format TableIDs(fileName) Parameters fileName - the name of an open database file (local or remote).
1065090 for the University Database database file if two tables have been defined for the file. Related topics Contents Functions reference (alphabetical list) TableNames Purpose Returns a list of all table occurrences in the relationships graph for fileName, separated by carriage returns. Format TableNames(fileName) Parameters fileName - the name of an open database file (local or remote). Important See Design functions for information about literal text parameters.
Format ValueListIDs(fileName) Parameters fileName - the name of an open database file (local or remote). Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples ValueListIDs(“Customers”) returns a list of all the value list IDs in the Customers database file.
Examples ValueListItems(“Customers”;“Code”) returns a list of all the items in the Code value list in the Customers database file. Related topics Contents Functions reference (alphabetical list) ValueListNames Purpose Returns a list of the names of all value lists in fileName, separated by carriage returns. Format ValueListNames(fileName) Parameters fileName - the name of an open database file (local or remote). Important See Design functions for information about literal text parameters.
Parameters {fileName} - the name of an open database file (local or remote). Parameters in curly braces { } are optional. Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Use the optional fileName parameter to only return windows that are based on the specified file. The window could be visible, hidden, or minimized. The order of the names in the list matches the current stacking order of the windows.
External functions Use external functions to access FileMaker Pro plug-ins. Plug-ins add features to FileMaker Pro. External functions are only available if FileMaker Pro plug-ins are installed and enabled on your computer. If no FileMaker Pro plug-ins are installed, you see only the generic external function definition in the Specify Calculation dialog box: External (nameOfFunction; parameter) Plug-ins written for version 7.0 and later Each plug-in defines its own functions and parameters.
Description Plug-ins created for FileMaker Pro version 7.0 and later do not use the External(“function name”, parameter) syntax. For more information, see External functions and the FILEMAKER PRO ADVANCED DEVELOPMENT GUIDE included with FileMaker Pro Advanced.
Financial functions Financial functions calculate financial information, such as net present value and payments. For example, you can calculate the monthly payments required to buy a car at a certain loan rate using the PMT function. Click a function name for details. This function Returns FV The future value of an initial investment, based on a constant interest rate and payment amount for the number of periods in months.
( 1 + interestRate ) periods – 1 FV = payment * ----------------------------------------------------------------------------interestRate Examples FV(50;.11/12;5 * 12) returns 3975.90398429.... FV(2000;.12;30) + 5000 * (.12 + 1) ^ 30 returns 632464.97928640.... FV(500;.11/5;60) returns 61141.65130790.... To set the decimal precision of the returned value, enclose the current formulas with the Round function. For example, Round(Current Formula;2).
Examples NPV(Loan;.05) returns 156.91277445..., when the repeating field, Loan, contains -2000 (the initial payment), 600, 300, 500, 700, and 400. The result (156.91277445...) is the actual profit in today’s dollars that will be realized from this transaction. NPV(Amounts;.10) returns 16758.35604870..., when the repeating field, Amounts, contains 5000 (the initial investment), 10,000, 0, 10,000, and 10,000.
“Your payment will be ” & PMT(150000;.13/12;Months) & “.” returns Your payment will be, followed by the payment amount, based on a total cost of $150,000, at a 13 percent annual percentage rate, over the duration stored in Months. Related topics Contents Functions reference (alphabetical list) PV Purpose Returns the present value (PV) of a series of equal payments made at regular intervals (periods), assuming a fixed interestRate per interval.
Related topics Contents Functions reference (alphabetical list) FILEMAKER FUNCTIONS REFERENCE 66
Get functions Use Get functions in scripts for error checking and prevention, or to capture information about the status of a database file or elements in it, or an action being performed. Many Get functions return information that changes on a regular basis. For example, when the Get(CurrentTime) function is placed in a stored calculation field, the time will only update when a new record is created.
This function Returns Get(ApplicationVersion) Text representing the FileMaker Pro application version. Get(CalculationRepetitionNumber) A number representing the repetition of the calculation field that is currently being calculated. Get(CurrentDate) The current date according to the system calendar. Get(CurrentExtendedPrivileges) A list of keywords for the enabled extended privileges of the account that is being used to evaluate the calculation.
This function Returns Get(LastODBCError) A string that shows the error state published by ODBC standards, based on ISO/IEF standards. Get(LayoutAccess) A number corresponding to the layout access privileges assigned through the Manage Security dialog box. Get(LayoutCount) The total number of layouts in the database file. Get(LayoutName) The name of the layout currently displayed.
This function Returns Get(StatusAreaState) A number representing whether the status toolbar is hidden, visible, visible and locked, or hidden and locked. Get(SystemDrive) The drive letter (Windows) or the volume name (Mac OS) where the currently running operating system is located. Get(SystemIPAddress) The IP addresses of all the machines connected to a NIC (Network Interface Controller) card. Get(SystemLanguage) The language currently set on the current system.
This function Returns Get(WindowVisible) A Boolean value representing whether or not the current window is visible. Get(WindowWidth) A number representing the width, in pixels, of the current window of the file in which the calculation is defined. Get(WindowZoomLevel) The zoom level of the current window. Get functions example This script uses the function Get(CurrentDate) to check each record in the found set to see if an account is past due.
Format Get(AccountExtendedPrivileges) Parameters None Data type returned text Originated in FileMaker Pro 11.0 Description Extended privileges are additional access rights assigned to an account’s privilege set. Returns an empty list if a user doesn’t have extended privileges assigned to the account used to open the database file.
Parameters None Data type returned text Originated in FileMaker Pro 7.0 Description Use this function for FileMaker authentication. If a user is using the default Admin account, Get(AccountName) returns Admin. If a user is using the FileMaker Pro guest account then [Guest] will be returned.
Parameters None Data type returned text Originated in FileMaker Pro 11.0 Description If a user is using the default Admin account and you haven’t modified access privileges for the database file, this function returns [Full Access]. Notes • If you specify the context for the current calculation, this function will be evaluated based on that context; otherwise, it will be evaluated based on the context of the current window.
Originated in FileMaker Pro 6.0 or earlier Description When the focus is in a repeating field, this function returns the contents of the active repetition. The result type of the active field depends upon the data type of the active field and the result type assigned to the Get(ActiveFieldContents) calculation function. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Related topics Contents Functions reference (alphabetical list) Get(ActiveFieldTableName) Purpose Returns the name of the table that contains the active field (the field that has the focus). Format Get(ActiveFieldTableName) Parameters None Data type returned text Originated in FileMaker Pro 7.0 Description If there is no active field, this function returns an empty string.
Format Get(ActiveLayoutObjectName) Parameters None Data type returned text Originated in FileMaker Pro 8.5 Description For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples There is a named button on the current layout called cancelButton. When the focus is on the button, Get(ActiveLayoutObjectName) returns cancelButton.
Description The number returned is calculated by summing numbers representing each modifier key being pressed. The values assigned to the keys are: • Shift = 1 • Caps Lock = 2 • Ctrl (Windows) and Control (Mac OS) = 4 • Alt (Windows) and Option (Mac OS) = 8 • Command (Mac OS) = 16 Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Description When no portal row contains the focus, this function returns 0. If there are multiple windows open in the current database file, each window can have its own portal row number value, but results are returned for only the foreground window. If a user navigates to a portal without selecting a specific portal row and without making an object active within a specific portal row, Get(ActivePortalRowNumber) returns row 0 rather than row 1.
Related topics Contents Functions reference (alphabetical list) Get(ActiveSelectionSize) Purpose Returns a number representing how many characters are selected. Format Get(ActiveSelectionSize) Parameters None Data type returned number Originated in FileMaker Pro 7.0 Description Returns 0 if there is no selection. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Parameters None Data type returned number Originated in FileMaker Pro 7.0 Description Returns the cursor's current position if no text is selected. If there are multiple windows open in the current database file, a result is returned for only the foreground window. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns 5 when the selection starts at character 5.
Examples Returns 1 if Allow user abort script step is on. Related topics Contents Functions reference (alphabetical list) Get(AllowToolbarState) Purpose Returns a Boolean value representing whether toolbars are allowed to be visible. Format Get(AllowToolbarState) Parameters None Data type returned number Originated in FileMaker Pro 8.0 Description Returns 1 if toolbars are allowed; otherwise, returns 0. The Allow Toolbars script step sets the toolbar state.
Format Get(ApplicationLanguage) Parameters None Data type returned text Originated in FileMaker Pro 7.0 Description The text that is returned by this function is in the English language. FileMaker Pro supports: • English • French • Italian • German • Swedish • Spanish • Dutch • Japanese Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Parameters None Data type returned text Originated in FileMaker Pro 6.
Originated in FileMaker Pro 7.0 Description The first repetition returned is 1. If the current field isn’t a repeating field, the function returns 1. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns 5 when FileMaker Pro is calculating the fifth repetition of a repeating field.
Examples Returns 2/2/2010 when the system date is set to February 2, 2010. Related topics Contents Functions reference (alphabetical list) Get(CurrentExtendedPrivileges) Purpose Returns a list of keywords, separated by carriage returns, for the enabled extended privileges of the account that is being used to evaluate this calculation. Format Format Get(CurrentExtendedPrivileges) Parameters None Data type returned text Originated in FileMaker Pro 6.
Related topics Contents Functions reference (alphabetical list) Get(CurrentHostTimestamp) Purpose Returns the host’s current date and time (to the nearest second) according to the system clock. Format Get(CurrentHostTimestamp) Parameters None Data type returned timestamp Originated in FileMaker Pro 7.0 Description The format of the value returned is determined by the database file’s settings. You can use your client system’s settings in the operating system.
Related topics Contents Functions reference (alphabetical list) Get(CurrentPrivilegeSetName) Purpose Returns the name of the privilege set that is being used to evaluate this calculation in the database file. Format Get(CurrentPrivilegeSetName) Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier Description If a user is using the default Admin account and you haven’t modified access privileges for the database file, this function returns [Full Access].
Get(CurrentTime) Purpose Returns CurrentTime (to the nearest second) according to the system clock. Format Get(CurrentTime) Parameters None Data type returned time Originated in FileMaker Pro 6.0 or earlier Description The format of the value returned is determined by the operating system settings. Note In client/server and peer-to-peer environments, Get(CurrentTimestamp) evaluates the status of the client machine running the script (not the host machine).
Data type returned timestamp Originated in FileMaker Pro 7.0 Description The format of the value returned is determined by the operating system settings. Note In client/server and peer-to-peer environments, Get(CurrentTimestamp) evaluates the status of the client machine running the script (not the host machine). For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Examples Returns Custom Menu Set #1 when this custom menu set is active. Returns an empty string when the [Standard FileMaker Menus] menu set is active. Related topics Contents Functions reference (alphabetical list) Get(DesktopPath) Purpose Returns the path to the desktop folder for the current user. Format Get(DesktopPath) Parameters None Data type returned text Originated in FileMaker Pro 8.0 Description In Windows, the path format is /Drive:/Documents and Settings/UserName/Desktop/.
Get(DocumentsPath) Purpose Returns the path to the Documents folder for the current user. Format Get(DocumentsPath) Parameters None Data type returned text Originated in FileMaker Pro 8.0 Description In Windows XP, the path format is /Drive:/Documents and Settings/UserName/My Documents/. In Windows Vista, the path format is /Drive:/Users/UserName/Documents/. In the Mac OS, the path format is /DriveName/Users/UserName/Documents/.
Get(DocumentsPathListing) Purpose Returns a list of all the files and folders in the Documents folder returned by the Get(DocumentsPath) function. Format Get(DocumentsPathListing) Parameters None Data type returned text Originated in FileMaker Pro 10.0 Description Each pathname in the Documents folder is listed separated by a line break. Files and folders are named according to FileMaker Pro naming conventions.
Related topics Contents Functions reference (alphabetical list) Get(DocumentsPath) Get(ErrorCaptureState) Purpose Returns 1 if the Set Error capture script step is on; otherwise, returns 0. Format Get(ErrorCaptureState) Parameters None Data type returned number Originated in FileMaker Pro 7.0 Description For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Data type returned text Originated in FileMaker Pro 8.0 Description In Windows, the path format is /Drive:/Program Files/FileMaker/FileMaker Pro 11.0/. In the Mac OS, the path format is /DriveName/Applications/FileMaker Pro 11.0/. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns /C:/Program Files/FileMaker/FileMaker Pro 11.0/ in Windows.
Examples Returns Contacts when Contacts is the active file. Related topics Contents Functions reference (alphabetical list) Get(FilePath) Purpose Returns the full path indicating the location of the currently active database file. Format Get(FilePath) Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier Description In Windows, the full path is file:/drive:/folder/filename for local files. For remote files, the full path is file://volume/folder/filename.
Get(FileSize) Purpose Returns the size (in bytes) of the currently active database file. Format Get(FileSize) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description If the current calculation is stored and you specify its context, this function will be evaluated based on that context; otherwise, it will be evaluated based on the context of the current window.
Originated in FileMaker Pro 6.0 or earlier Description If there are multiple windows open in the current database file, each window can have its own found count value, but results are returned for only the foreground window. If you specify the context for the current calculation, this function will be evaluated based on that context; otherwise, it will be evaluated based on the context of the current window.
Examples Returns High Contrast White when the Windows Vista color scheme is set to High Contrast White. Related topics Contents Functions reference (alphabetical list) Get(HighContrastState) Purpose Returns a Boolean value representing the state of the Use High Contrast option in the Accessibility Options dialog box (Windows XP) or the Choose a High Contrast color scheme setting in the Ease of Access dialog box (Windows Vista).
Format Get(HostApplicationVersion) Parameters None Data type returned text Originated in FileMaker Pro 9.0 Description Displays a value when used with the same or higher version of FileMaker Pro or FileMaker Server software. If the current database is not shared or hosted, this function returns an empty string. Also returns an empty string when used from the host computer itself. Examples Returns Pro 11.0v1 when the host computer is running FileMaker Pro 11 version 1. Returns ProAdvanced 11.
Description Returns the IP address of the host machine for the current database. If the current database isn’t being hosted, an empty string is returned. If IPv4 and IPv6 addresses are available for remotely hosted files, the address is returned in the most common or default format. This might not be the same format that was used when connecting to the host.
Description On the computer that is hosting the database file: • Windows XP: Choose Start menu > Control Panel > Performance and Maintenance > System > and then click the Computer Name tab. Full computer name displays the current registered name. • Windows Vista: Choose Start menu > Control Panel > System and Maintenance > System. Full computer name displays the current registered name. • Mac OS: In the Sharing System Preference, Computer Name displays the current registered name.
Notes • Mac OS: In FileMaker Pro, if an error occurs while performing an AppleScript from the Manage Scripts feature, the AppleScript error code will be returned. • For ODBC imports and Execute SQL script steps, if an error occurs while performing a SQL query, returns FileMaker error 1408. For detailed information about the error, use the Get(LastODBCError) function. If there is no information about the error, returns FileMaker error 1409.
Description Returns: • 1 for the first button (by default, labeled OK) • 2 for the second button (by default, labeled Cancel) • 3 for the third button Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Related topics Contents Functions reference (alphabetical list) Get(LastODBCError) Purpose Returns a string that shows the error state published by ODBC standards, based on ISO/IEF standards.
Examples For ODBC imports and Execute SQL script steps, returns [DataDirect][Macintosh ODBC Driver Manager] Data source name not found and no default driver specified (-1) when a data source name wasn’t found and the driver wasn’t specified. Related topics Contents Functions reference (alphabetical list) Get(LayoutAccess) Purpose Returns a number based on record access privileges available through the current layout.
Examples Returns 1 when the layout allows view-only access to records. Related topics Contents Functions reference (alphabetical list) Get(LayoutCount) Purpose Returns the total number of layouts in the database file. Format Get(LayoutCount) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier Description If there are multiple windows open in the current database file, each window can have its own layout name value, but results are returned for only the foreground window. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns Product List when the Product List layout is displayed.
Description If there are multiple windows open in the current database file, each window can have its own layout number value, but results are returned for only the foreground window. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns 3 when the current layout is third in the list of layouts in Manage Layouts.
Get(LayoutViewState) Purpose Returns a number indicating the currently active database file view. Format Get(LayoutViewState) Parameters None Data type returned number Originated in FileMaker Pro 6.
Data type returned number Originated in FileMaker Pro 6.
Originated in FileMaker Pro 6.0 or earlier Description For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns TCP/IP. Related topics Contents Functions reference (alphabetical list) Get(PageNumber) Purpose Returns a number representing the current page being printed or previewed. Format Get(PageNumber) Parameters None Data type returned number Originated in FileMaker Pro 6.
Get(PreferencesPath) Purpose Returns the path to the preferences and default options folder for the current user. Format Get(PreferencesPath) Parameters None Data type returned text Originated in FileMaker Pro 8.0 Description In Windows XP, the path format is /Drive:/Documents and Settings/UserName/Local Settings/ Application Data/. In Windows Vista and Windows 7, the path format is /Drive:/Users/UserName/AppData/Local/. In Mac OS 10.5 and 10.
Parameters None Data type returned text Originated in FileMaker Pro 6.
Data type returned text Originated in FileMaker Pro 11.0 Description Returns the text that was entered the last time quick find was performed. Examples Returns New York if the last search that was entered in the Quick Find box was for New York. Related topics Contents Functions reference (alphabetical list) Get(RecordAccess) Purpose Returns a number based on the current record’s access privileges, assigned through the Custom Record Privileges dialog box.
Description Returns: • 0 if the custom record privileges of an account’s privilege set have neither View nor Edit privileges set to yes for the current record • 1 if the custom record privileges of an account’s privilege set have View set to yes for the current record, or if View is set to limited and the calculation defined for limited access returns a value of true Note If both View and Edit are set to yes, Get(RecordAccess) returns 2 • 2 if the custom record privileges of an account’s privilege set have
Originated in FileMaker Pro 6.0 or earlier Description The number returned is a decimal value (an integer) generated by FileMaker Pro when the record is created. It does not change. Notes • If the current calculation is stored and you specify its context, this function will be evaluated based on that context; otherwise, it will be evaluated based on the context of the current window.
Description To commit changes, you can, for example: • click out of all fields (exit the record) • go to a different record • enter Find mode If multiple windows are open, clicking in another window does not commit the record. Notes • If the current calculation is stored and you specify its context, this function will be evaluated based on that context; otherwise, it will be evaluated based on the context of the current window.
Description The value returned is determined by the relative place of the record in the found set, and it changes depending on the find criteria and the sort order. Notes • To return a value that uniquely and permanently identifies a record in this table, use Get(RecordID). • If you specify the context for the current calculation, this function will be evaluated based on that context; otherwise, it will be evaluated based on the context of the current window.
Examples Returns 4 if there are four open records in the current found set that haven’t been saved. Related topics Contents Functions reference (alphabetical list) Get(RecordOpenState) Purpose Returns a number representing the state of the current record. Format Get(RecordOpenState) Parameters None Data type returned number Originated in FileMaker Pro 8.
Get(RequestCount) Purpose Returns the total number of find requests defined for the current table. Format Get(RequestCount) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description If there are multiple windows open in the current database file, then results are returned for only the top-most window of the file in which the calculation is defined.
Originated in FileMaker Pro 8.0 Description Returns 1 if the Omit checkbox is selected; otherwise, returns 0. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns 1 when the Omit checkbox is selected in the current find request.
Examples Returns 32 on a display showing millions (232) of colors. Returns 16 on a display showing thousands (216) of colors. Returns 4 on a VGA display. Returns 1 on a black-and-white display. Related topics Contents Functions reference (alphabetical list) Get(ScreenHeight) Purpose Returns the number of pixels displayed vertically on the screen in which the window of the current file is open. Format Get(ScreenHeight) Parameters None Data type returned number Originated in FileMaker Pro 6.
Get(ScreenWidth) Purpose Returns the number of pixels displayed horizontally on the screen in which the window of the current file is open. Format Get(ScreenWidth) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description When the window spans more than one screen, this function uses the screen that contains the largest percentage of the window.
Data type returned text Originated in FileMaker Pro 6.0 or earlier Description For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns Print Report when the Print Report script is running. Returns Update Customer when the Update Customer script is running.
Examples Returns Print when “Print” was the value of the parameter passed into the current script. The following example shows how to pass named parameters using the Evaluate, Let, and Get(ScriptParameter) functions, allowing access only to variable “a” (the example returns 6): ScriptParameter = "a = 5; b = 10" Evaluate("Let ( [" & Get(ScriptParameter) & "]; a + 1 )" ) The following example shows how to pass named parameters, allowing access to both variable “a” and “b”.
Examples In the following example, the Find Customers script returns the results of a find request when it is called from the Do Reports script. Script Find Customers uses the optional script result of the Exit Script script step. Script Do Reports then uses Get(ScriptResult) to determine what other script steps should be performed based on the returned result stored in Get(ScriptResult).
Description Returns: • 0 if the records in the active table are not sorted • 1 if the records in the active table are sorted • 2 if the records in the active table are partially sorted (semi-sorted) Each window has its own sort state. Notes • When records are imported from another file to a previously found and sorted set, the records in a sorted set may exist in a semi-sorted state. To include the imported records in the sort order, sort the found set after importing.
Description Returns: • 0 (zero) if the status toolbar is hidden • 1 if the status toolbar is visible • 2 if the status toolbar is visible and locked • 3 if the status toolbar is hidden and locked If there are multiple windows open on the currently active database file, then results are returned for only the active window. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Examples Returns /C:/ in Windows when the operating system is on the C: drive. Returns /DriveName/ in the Mac OS when the operating system is on a volume named DriveName. Related topics Contents Functions reference (alphabetical list) Get(SystemIPAddress) Purpose Returns a list of the IP addresses of all computers connected to an active NIC (Network Interface Controller) card. Format Get(SystemIPAddress) Parameters None Data type returned text Originated in FileMaker Pro 7.
Suppose a machine has the following active physical interfaces: • an Ethernet card not connected to a network with an IP address of 2001::10 • a WiFi interface with an IP address of 3FFE:FFFF:101::230:6EFF:FE04:D9FF/48 • a VPN connection with an IP address of 2001:0DB8:85A3:08D3:1319:8A2E:0370:7334 The function returns: 3FFE:FFFF:101::230:6EFF:FE04:D9FF/48 2001:0DB8:85A3:08D3:1319:8A2E:0370:7334 Related topics Contents Functions reference (alphabetical list) Get(SystemLanguage) Purpose Returns the languag
Get(SystemNICAddress) Purpose Returns a list of the hardware addresses of all NIC (Network Interface Controller) cards connected to the computer. Format Get(SystemNICAddress) Parameters None Data type returned text Originated in FileMaker Pro 7.0 Description Values in the list returned by this function are separated by carriage returns. The address consists of 6 bytes displayed in hexadecimal separated by colons. In Windows, find this address by typing the command “ipconfig /All” in a DOS window.
Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Returns: • -1 if the current platform is PowerPC-based Macs • 1 if the current platform is Intel-based Macs • -2 if the platform is Windows XP, Windows Vista, or Windows 7 Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Get(SystemPlatform)returns -2 when the current platform is a Windows platform.
Description Use this function when running a script. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns 6.1 for Windows 7. Returns 6.0 for Windows Vista. Returns 5.1 for Windows XP (SP 2). Returns 10.5 for Mac OS X version 10.5. Returns 10.6 for Mac OS X version 10.6.
Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples In Windows, returns: /%HomeDrive%/Documents and Settings/{user id}/Local Settings/Temp/S or /%UserProfile%/AppData/Local/Temp/S or /%HomeDrive%/WINDOWS/Temp/S (Windows XP) where %HomeDrive% is an environment variable that returns the name of your home drive on your hard disk.
Description Returns 1 if the text ruler is displayed; otherwise, returns 0. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns 1 when the text ruler is visible. Related topics Contents Functions reference (alphabetical list) Get(TotalRecordCount) Purpose Returns the total number of records in the current table.
Get(TriggerKeystroke) Purpose Returns a string containing the characters that activated an OnObjectKestroke or OnLayoutKeystroke script trigger. Multiple characters may be returned when the input comes from an input method editor (IME). Format Get(TriggerKeystroke) Parameters None Data type returned text Originated in FileMaker Pro 10.
Format Get(TriggerModifierKeys) Parameters None Data type returned number Originated in FileMaker Pro 10.0 Description Returns a value only when called from a script activated by a script trigger or from a sub-script called from the triggered script; otherwise returns an empty string. • See Get(ActiveModifierKeys) for a description of the values assigned to the keyboard modifier keys. • See the Code function for a list of navigational keys and the codes returned to a script activated by this trigger.
Get(UserCount) Purpose Returns the number of clients currently accessing the file. Format Get(UserCount) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Returns: • 1 if FileMaker network sharing is turned off • 1 + the number of clients if FileMaker network sharing is turned on This function does not count clients accessing the database file via ODBC or JDBC.
Format Get(UserName) Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier Description The returned name is user-specified. Important 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. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb.
Originated in FileMaker Pro 8.0 Description Returns 1 if Use System Formats is on; otherwise, returns 0. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns 1 when Use System Formats is on. Related topics Contents Functions reference (alphabetical list) Get(WindowContentHeight) Purpose Returns a number representing the height, in pixels, of the FileMaker Pro content area.
Examples Returns 400 in the Mac OS when the current window height is 437 and the status toolbar isn’t showing. The example below combines Get(WindowContentHeight) with Get(WindowHeight) to determine the height of the title bar and horizontal scroll bar: Get(WindowHeight) - Get(WindowContentHeight) returns 37 in the Mac OS when the window height is 437 and the status toolbar isn’t showing.
Related topics Contents Functions reference (alphabetical list) Get(WindowDesktopHeight) Purpose Returns a number representing the height, in pixels, of the desktop space. Format Get(WindowDesktopHeight) Parameters None Data type returned number Originated in FileMaker Pro 7.0 Description In Windows, the desktop space is the area inside the MDI window (sometimes referred to as the client area). This doesn’t include any virtual space available through the scrolling of the MDI window.
Format Get(WindowDesktopWidth) Parameters None Data type returned number Originated in FileMaker Pro 7.0 Description In Windows, the desktop space is the space inside the MDI window (sometimes referred to as the client area). In the Mac OS, the desktop space is the area on the main monitor excluding the menu bars. The main monitor is where the menu bar is located.
Originated in FileMaker Pro 7.0 Description The height of the window is calculated from the top to bottom outer edges of the window. This position doesn’t include shadows or other effects applied to windows. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Get(WindowHeight) returns 300 when the current window’s height is 300 pixels.
Examples Returns 52 when the outer edge of the active window is 52 pixels from the left edge of the screen. Returns 0 when the active window is 0 pixels from the left edge of the screen. Related topics Contents Functions reference (alphabetical list) Get(WindowMode) Purpose Returns a number representing the mode FileMaker Pro is in at the time the function is evaluated. Format Get(WindowMode) Parameters None Data type returned number Originated in FileMaker Pro 6.
Related topics Contents Functions reference (alphabetical list) Get(WindowName) Purpose Returns the name of the window on which the script is acting (not necessarily the foreground window). Format Get(WindowName) Parameters None Data type returned text Originated in FileMaker Pro 7.0 Description Returns an empty string if there is no window. Notes • You can set the window name with the Set Window Title script step.
Get(WindowTop) Purpose Returns a number representing the vertical distance, in pixels, of the outer edge of the window on which the script is acting (not necessarily the foreground window) relative to the bottom edge of the menu bar. Format Get(WindowTop) Parameters None Data type returned number Originated in FileMaker Pro 7.0 Description The origin of the reference coordinate system is at the left-most corner below the menu bar.
Data type returned number Originated in FileMaker Pro 7.0 Description The current window is the window on which the script is acting (not necessarily the foreground window). Returns 1 if the window is visible. Returns 0 if the window is hidden using the Hide Window command. The window can be located outside of the visible screen space and still return 1. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.
Description The width of the window is calculated from the left-most to right-most outer edge of the window. This position doesn’t include shadows or other effects applied to windows. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at www.filemaker.com/kb. Examples Returns 300 when the current window is 300 pixels wide.
FILEMAKER FUNCTIONS REFERENCE 150
Logical functions Logical functions test for a condition to evaluate it as true or false. This is known as a Boolean value. If the condition is true, FileMaker Pro returns a 1; if the condition is false, FileMaker Pro returns a 0. You can use the keywords True and False with logical functions and operators when a Boolean value is needed. Keyword True returns 1 and keyword False returns 0.
Parameters test - any text or numeric expression. result - result corresponding to the expression. Parameters in curly braces { } are optional. Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 6.0 or earlier Description Case evaluates each test expression in order, and when a True expression is found, returns the value specified in result for that expression. You can include a default result at the end of the parameter list.
Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 6.0 or earlier Description FileMaker Pro evaluates test to obtain an index number, which is used to choose the corresponding ordinal result. Because Choose is a 0-based list, the first item on the list is indexed 0 and the second item on the list is indexed 1. For example, if test evaluates to 2, then result2 is chosen.
Description The optional fields parameter is a list of fields this calculation is dependent on. If a necessary field isn’t listed, modifying that dependent field won’t update the result of the calculation. Examples Evaluate(TextField) returns 4 when TextField contains 2 + 2. Evaluate("textfield") returns 2 + 2 when textfield contains 2 + 2. Evaluate(GetField("textfield")) returns 4 when textfield contains 2 + 2. Evaluate(TextField;[Amount]) returns .80 when TextField contains .
The dependent parameter can also be useful in other cases. For example, Evaluate(“Get(CurrentTimeStamp)”; [FieldB; FieldC]) will store a timestamp in the calculation field whenever FieldB or FieldC changes. Related topics Contents Functions reference (alphabetical list) EvaluationError Purpose Returns an error code, if any, from expression. Format EvaluationError(expression) Parameters expression - any calculation expression Data type returned number Originated in FileMaker Pro 7.
GetAsBoolean Purpose Returns 1 if data converts to a non-zero numeric value or if a container field contains data; returns 0 if data has a numeric value of 0 or is empty. Format GetAsBoolean(data) Parameters data - any text, number, date, time, timestamp or container expression, or a field containing text, a number, date, time, timestamp or container Data type returned number Originated in FileMaker Pro 8.0 Description Returns a Boolean value. Examples GetAsBoolean(“”) returns 0.
Important See Design functions for information about literal text parameters. Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 6.0 or earlier Description Use this function to get the contents of fieldName, or in any function that uses a field, such as NPV, GetSummary, GetRepetition, or the aggregate functions. Examples Suppose you have the fields Arrow and Target. Arrow contains the text string Target, and Target contains the text string Bullseye.
Data type returned text Originated in FileMaker Pro 10.0 Description Use this function to get the fully qualified name of fieldName (tableName::fieldName). Note If you specify the context for the current calculation, this function will be evaluated based on that context. Otherwise, it will be evaluated based on the context of the current window. Examples GetFieldName(x) returns the name of a field reference passed into a custom function as parameter x.
Originated in FileMaker Pro 8.5 Attributes objectType - returns the object's type as a text literal, in English. Valid return values are: field, text, graphic, line, rectangle, rounded rectangle, oval, group, button group, portal, tab panel, chart, web viewer, and unknown. hasFocus - returns 1 (True) if objectName is currently active, otherwise returns 0 (False). Objects that can have the focus are fields, portals, tab panels, and groups. Also returns 1 for a portal when a portal row is selected.
content - returns the content of the specified object as follows. For: web viewers - returns the current content (such as HTML code). fields - returns the field data formatted using the specified object's properties. text objects - returns the text (including text from merge fields). graphics - returns image data such as the name of a file in a Container field if the image is stored, or the reference to the file if the image is unstored. charts - returns the bitmap representation of a chart object.
Format GetNthRecord(fieldName;recordNumber) Parameters fieldName - any related field or repeating field, or an expression that returns a field or a repeating field recordNumber - the record number from which you want data Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 8.
Parameters test - any numeric value or logical expression result1 - expression or field name result2 - expression or field name Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 6.0 or earlier Description If test is True (any non-zero numeric result), FileMaker Pro returns result1. If test is False (0), result2 is returned. Test must be an expression that returns either a numeric or Boolean (True, False) result.
Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples IsEmpty(OrderNum) returns 1 if the OrderNum field is empty. If(IsEmpty(LastName);“Invalid record”;““) displays Invalid Record if the LastName field is blank, but displays nothing if there is an entry in LastName. IsEmpty(Payments::DatePaid) returns 1 if, for example, the Payments table has been moved or renamed. IsEmpty(“text”) returns 0.
• A field has been deleted from a related table, and therefore the references to that field in the parent table are invalid Otherwise, it returns 1 (the data is valid). Examples IsValid(Datefield) returns 0 if there is non-date data in Datefield, for example if text was imported into it. IsValid(Amount) returns 0 if there is only text in the number field Amount. IsValid(table::field) returns 0 if the related table was renamed and the relationship isn’t updated with the new filename.
Let Purpose Sets varX to the result of expressionX for the duration of calculation, until the script exits (local variables), or until the file is closed (global variables). Format Let({[}var1=expression1{;var2=expression2...]};calculation) Parameters var - any variable name, local variable name, or global variable name expression - any calculation expression, field, or constant. calculation - any calculation expression, field, or constant. Parameters in curly braces { } are optional.
Examples Let(x=5;x*x) returns 25. Let([x=5;squared=x*x;cubed=squared*x];cubed) returns 125. Let(City=“Paris”;Let(City=“San Francisco”;City&“-“)&City) returns San Francisco - Paris. The following example sets a local variable counter at repetition 50 with a value of 120: Let($counter[50]=120;$counter[50]*2) returns 240.
Originated in FileMaker Pro 7.0 Description For this function to access the contents of the source field, the tables containing the source field and calculation field need to be related. Calculations using the Lookup function won’t be forced to be unstored calculations. Note Lookup returns ? when the related table is an ODBC data source. Examples There are two tables, People and Company, in a database file containing the data shown below.
Parameters sourceField - the field from which the lookup value is taken lower/higherFlag - the keywords lower or higher denote whether the value from the next lower/higher matching record must be taken if no related record is found Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 7.0 Description Returns the value specified in sourceField using the relationships in the relationships graph.
The Rate Lookup calculation field will return 25, 25, 100, and 150 for records 1 to 4. Rate Lookup can then be used to get the correct rate code (A, A, C, and D respectively). Item Weight Rate Lookup Lamp 8 25 Chair 22 25 Desk 60 100 Bed 120 150 Related topics Contents Functions reference (alphabetical list) Self Purpose Returns the content of the object in which the calculation is defined.
Related topics Contents Functions reference (alphabetical list) FILEMAKER FUNCTIONS REFERENCE 170
Number functions Number functions are used to manipulate numeric data. Click a function name for details. This function Returns Abs The absolute value (a positive number) of a number. Ceiling A number rounded up to the next integer. Combination The number of ways to uniquely choose a specified number of items from a set of specified size. Div An integer of the specified number divided by the divisor. Exp The value of the constant e (the base of the natural logarithm, equal to 2.
Data type returned number, time Originated in FileMaker Pro 6.0 or earlier Description The absolute value of a number is positive. For example, if a negative number appears in a field, the Abs function removes the minus sign and changes it to a positive value. Examples Abs(-123) returns 123. Abs(PriceDifference) returns the positive value of the number in the PriceDifference field.
Related topics Contents Functions reference (alphabetical list) Combination Purpose Returns the number of ways to uniquely choose numberOfChoices items from a set of size setSize. Format Combination(setSize;numberOfChoices) Parameters setSize - any numeric expression or field containing a non-negative numeric expression numberOfChoices - any numeric expression or field containing a non-negative numeric expression Data type returned number Originated in FileMaker Pro 7.
Div Purpose Returns the next lowest integer value after dividing number by divisor. Equivalent to Floor(number/divisor). Format Div(number;divisor) Parameters number - any numeric expression or field containing a numeric expression divisor - any numeric expression or field containing a numeric expression Data type returned number Originated in FileMaker Pro 7.0 Examples Div(2.5;2) returns 1. Div(-2.5;2) returns -2.
Originated in FileMaker Pro 6.0 or earlier Description The Exp function is the inverse of the Ln function. Examples Exp(1) returns 2.71828182.... Exp(Ln(2)) returns 2. Exp(0) returns 1. Related topics Contents Functions reference (alphabetical list) Factorial Purpose Returns the factorial of number stopping at 1 or stopping at the optional numberOfFactors. Format Factorial(number{;numberOfFactors}) Parameters number - numeric expression or field containing a positive integer.
Examples Factorial(3) returns 6, which = 3 * 2 * 1. Factorial(10;3) returns 720, which = 10 * 9 * 8. Related topics Contents Functions reference (alphabetical list) Floor Purpose Returns number rounded down to the next lower integer. Format Floor(number) Parameters number - any numeric expression or field containing a numeric expression Data type returned number Originated in FileMaker Pro 7.0 Examples Floor(1.25) returns 1. Floor(-1.25) returns -2.
Parameters number - any numeric expression or field containing a numeric expression Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples Int(1.45) returns 1. Int(123.987) returns 123. Int(Players/3) returns 4, if Players contains 13. Related topics Contents Functions reference (alphabetical list) Lg Purpose Returns the base-2 logarithm of number.
Examples Lg(1) = 0 Lg(2) = 1 Lg(32) = 5 Related topics Contents Functions reference (alphabetical list) Ln Purpose Returns the base-e (natural) logarithm of number. Format Ln(number) Parameters number - any numeric expression or field containing a numeric expression Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Number can be any positive value. Negative values and 0 return an error. The Exp function is the inverse of the Ln function. Examples Ln(2.
Format Log(number) Parameters number - any positive numeric expression or field containing a numeric expression Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Number can be any positive value. Negative values return an error. For 0, the Log function returns nothing because this value is out of the acceptable range. Ln(number) Log = --------------------------------Ln(10) Examples Log(1) returns 0. Log(100) returns 2.
Originated in FileMaker Pro 6.0 or earlier Description Use the Mod function to test whether a number is even or odd by specifying a divisor of 2. If the result is zero, then the number is even; otherwise, it’s odd. The result has the same sign as divisor. Mod = number – ( Div(number;divisor) • divisor ) Examples Mod(13;4) returns 1. Mod(7;5) returns 2. Mod(7;-5) returns -3. Mod(-7;5) returns 3. Mod(-7;-5) returns -2. Mod(Participants;TeamSize) returns 4 if Participants contains 40 and TeamSize contains 9.
Description Returns a pseudo-random number in the range (0,1).
Round(123.456;-1) returns 120. Related topics Contents Functions reference (alphabetical list) SetPrecision Purpose Computes any math functions contained within expression that support extended precision to precision decimal places (from 16 up to 400). Format SetPrecision(expression;precision) Parameters expression - any numeric expression precision - any number or numeric expression Data type returned number Originated in FileMaker Pro 7.
Sign Purpose Returns one of three possible values: -1 when number is negative, 0 when it’s zero, and 1 when it’s positive. Format Sign(number) Parameters number - any numeric expression or field containing a numeric expression Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples Sign(15.12) returns 1. Sign(-175) returns -1. Sign(BalanceDue) returns 0, if BalanceDue is a number field containing 0.
Originated in FileMaker Pro 6.0 or earlier Description Use this function to calculate Sqrt. Sqrt = number Examples Sqrt(4) returns 2. Sqrt(SquareFeet) returns 6 if the SquareFeet number field contains 36. Related topics Contents Functions reference (alphabetical list) Truncate Purpose Returns number truncated to the specified precision (number of decimal places).
Examples Truncate(123.456;2) returns 123.45. Truncate(-14.6;0) returns -14. Truncate(29343.98;-3) returns 29000. Truncate(123.456;4) returns 123.456. Truncate(29343.98;5) returns 29343.98.
Repeating functions Repeating functions perform calculations on repeating fields. Click a function name for details. This function Returns Extend In a calculation involving both repeating and non-repeating fields, allows a value in a non-repeating field to be used with every repetition in a repeating field. GetRepetition The contents of the specified repetition of a repeating field. Last The last valid, non-blank value in the specified field.
Related topics Contents Functions reference (alphabetical list) GetRepetition Purpose Returns the contents of the repeating field specified by number. Format GetRepetition(repeatingField;number) Parameters repeatingField - any repeating field, or an expression that returns a reference to a repeating field number - the field repetition number Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 6.
Format Last(field) Parameters field - any repeating field or related field, or an expression that returns a reference to a repeating field or related field Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 6.0 or earlier Description If field specifies a repeating field then it returns the last non-blank repetition. If field specifies a related field, then it returns the last non-blank value in the related set.
Summary functions Summary functions produce a summary of all records in the found set, or subsummary values for records in different groups. Formulas can contain more than one summary function. Summary functions calculate more slowly than other functions because they generate values for a range of records. An alternative way to generate similar calculated results is to use Aggregate functions to summarize data in related records (whether or not they appear in a portal).
Calculations using the GetSummary function are unstored. Note You can get similar results using a self-join relationship and Aggregate functions. Examples GetSummary(Total Sales;Country) returns a summary of all records pertaining to the value in the Country field. GetSummary(Total Sales, if(Number of Countries > 1, Country, Sales Zone)) returns a summary of Total Sales by Country if Number of Countries is greater than 1. Otherwise, it returns a summary of Total Sales by Sales Zone.
Text functions Text functions can be used to analyze, rearrange, extract, and build text strings. For example, you could use the MiddleWords function to extract specific words from supplied text. Text functions operate on these parameters: • fields of type text • text constants (in quotation marks) • expressions having a text result Click a function name for details. This function Returns Char Returns the characters for the Unicode code points in the number.
This function Returns Lower All letters in the specified text as lowercase. Middle The specified number of characters in the text, starting at a specified character position. MiddleValues The specified number of values in the text, starting with a specified value. MiddleWords The specified number of words in the text, starting with a specified word. NumToJText Roman numbers converted from Japanese text. PatternCount The number of occurrences of a search string in the specified text.
Data type returned text Originated in FileMaker Pro 10.0 Description Each group of five digits in the number is treated as a Unicode code point, and the character for each five-digit group is returned in the text. If the number is 0, the function returns an empty string. If the number is between 1 and 99,999, the function returns a single character. If the number contains more than five digits, the function returns the string of characters represented by those code points.
Data type returned number Originated in FileMaker Pro 10.0 Description Returns the Unicode code points for the characters in the text. If zero characters are in the text, returns 0. If one character is in the text, the function returns the code point for that character.
Examples Code(“”) returns 0. Code(“a”) returns 97. Code(“b”) returns 98. Code(“ab”) returns 9800097. Code(“ä”) returns 228. Code(“ä”)(an a followed by a dieresis character entered in a separate keystroke) returns 77600097. Related topics Contents Functions reference (alphabetical list) Exact Purpose Compares the contents of any two fields. If the fields match, the result is 1 (True); otherwise, the result is 0 (False).
Exact(Upper(“McDonald”);Upper(“MCDONALD”)) returns 1 (True). Exact(“John”;“John ”) returns 0 (False). Exact(BillTo;ShipTo) returns 1 (True) when the value in BillTo is the same as the value in ShipTo. Exact(Recipient;Upper(Recipient)) returns 1 (True), when Recipient contains JOHNSON. Exact(Country;“Spain”) returns 1 (True) when the Country field contains Spain.
Related topics Contents Functions reference (alphabetical list) FilterValues Purpose Returns a text result containing only the values that were provided in filterValues, in the order they were originally entered in textToFilter. Format FilterValues(textToFilter; filterValues) Parameters textToFilter - any text expression or text field filterValues - values that you want to preserve in the specified text Important See Design functions for information about literal text parameters.
when a database file named Database has a value list Sizes that contains Small¶Medium¶Large. Related topics Contents Functions reference (alphabetical list) GetAsCSS Purpose Returns text converted to the CSS (Cascading Style Sheets) format. Format GetAsCSS(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 7.0 Description CSS format is an internet text format similar to HTML.
GetAsDate Purpose Returns dates in text as field type date, for use in formulas involving dates or date functions. Format GetAsDate(text) Parameters text - any text expression or text field containing text in the same format as the date on the system where the file was created Data type returned date Originated in FileMaker Pro 6.0 or earlier Description Use the GetAsDate or Date function to enter a date constant into a formula.
Format GetAsNumber(text) Parameters text - any text expression or text field containing numbers Data type returned number Originated in FileMaker Pro 6.0 or earlier Description This function drops all non-numeric characters from text. You can also use this function to convert a date to the number of days. The returned number is the number of days since 1/1/0001. Examples GetAsNumber(“FY98”) returns 98. GetAsNumber(“$1,254.50”) returns 1254.5. GetAsNumber(“2 + 2”) returns 22.
Originated in FileMaker Pro 7.0 Description SVG is an internet text format similar to HTML or CSS. SVG supports more text formats than HTML, so SVG will represent what you have typed more accurately. Note The GetAsSVG function does not return formats that are set in the Conditional Formatting dialog box.
Originated in FileMaker Pro 6.0 or earlier Description The data returned can be a field type number, date, time, timestamp, or container. For a container field, GetAsText returns external path information, text (when the container contains text that does not resolve into a valid path), or a question mark (?) if the container data is embedded in the database. Examples GetAsText(45) returns 45. “You are ” & GetAsText(DaysDelinquent) & “ days late.” returns You are 3 days late.
Examples GetAsTime(“02:47:35”) returns 2:47:35 when you select time as the calculation result. You can perform time calculations on this result. GetAsTime(“02:47:35”) returns 1/1/0001 2:47:35 when you select timestamp as the calculation result. Abs(GetAsTime(“12:15 pm”) - CheckOut) returns 3:00:00 when the CheckOut time field contains 3:15 PM.
GetAsURLEncoded Purpose Returns text as URL (Uniform Resource Locator) encoding, for use as a URL. Format GetAsURLEncoded(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 8.5 Description This function removes all styles from text. All characters are first converted to UTF-8 format.
Parameters listOfValues - a list of carriage return-delimited values valueNumber - the value to return from the list Data type returned text Originated in FileMaker Pro 8.0 Description This function is useful in looping scripts or recursive custom calculations. Values are text items separated by carriage returns. You can place several values together to create a carriage return-delimited list of values. A value can be empty, a single character, a word, a sentence, or a paragraph.
Originated in FileMaker Pro 6.0 or earlier Examples Hiragana(“ ”) returns Related topics Contents Functions reference (alphabetical list) KanaHankaku Purpose Converts Zenkaku Katakana to Hankaku Katakana. Format KanaHankaku(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.
Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples KanaZenkaku(“ “) returns Related topics Contents Functions reference (alphabetical list) KanjiNumeral Purpose Converts Arabic numerals to Kanji numeral. Format KanjiNumeral(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.
Katakana Purpose Converts from Hiragana to Zenkaku Katakana. Format Katakana(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples Katakana(“ “) returns Related topics Contents Functions reference (alphabetical list) Left Purpose Returns numberOfCharacters in text, counting from the left.
Examples Left(“Manufacturing”;4) returns Manu. Left(Name;Position(Name;“ “;1;1)) returns Sophie, when the Name field contains Sophie Tang. Left(PostalCode;3) & Upper(Left(LastName;4)) returns 481JOHN when the PostalCode field contains 48187 and LastName contains Johnson. Related topics Contents Functions reference (alphabetical list) LeftValues Purpose Returns a text result containing numberOfValues from the list of values in text, counting from the left.
LeftValues(list;1) returns Sophie when the text being evaluated contains • Sophie • Bill Related topics Contents Functions reference (alphabetical list) LeftWords Purpose Returns a text result containing numberOfWords in text, counting from the left. Format LeftWords(text;numberOfWords) Parameters text - any text expression or text field numberOfWords - any numeric expression or field containing a number Data type returned text Originated in FileMaker Pro 6.
Format Length(field) Parameters field - any text, number, date, time, timestamp, or container field, or any text expression or numeric expression Data type returned number Originated in FileMaker Pro 6.0 or earlier Description This function returns the number of characters in a specified field. For a container field, Length returns the total stored size of objects in bytes. Examples Length(“John”) returns 4. Length(Description) returns 12 when the value in Description is Modem for PC.
Examples Lower(“ABCD”) returns abcd. Lower(Course) returns history, when the Course field contains History. Lower(“YOUR BILL IS OVERDUE”) returns your bill is overdue. Related topics Contents Functions reference (alphabetical list) Middle Purpose Extracts the numberOfCharacters from text, starting at the character position specified by start.
MiddleValues Purpose Returns a text result containing the specified numberOfValues in text, starting with startingValue. Format MiddleValues(text;startingValue;numberOfValues) Parameters text - any text expression or text field startingValue - any numeric expression or field containing a number numberOfValues - any numeric expression or field containing a number Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 7.
MiddleWords Purpose Returns a text result containing the numberOfWords from text, beginning at startingWord. Format MiddleWords(text;startingWord;numberOfWords) Parameters text - any text expression or text field startingWord - any numeric expression or field containing a number numberOfWords - any numeric expression or field containing a number Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples MiddleWords(“Plaid Canvas Suitcase”;2;2) returns Canvas Suitcase.
Data type returned text Originated in FileMaker Pro 6.0 or earlier Description If the value for separator and characterType are blank or other than 0 to 3, then 0 is used.
Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples PatternCount(“Mississippi”;“is”) returns 2. PatternCount(“Mississippi”;“issi”) returns 1 (the function isn’t inclusive). PatternCount(Attending;“Guest”) returns 1 if the Guest checkbox is one of the items selected in the Attending field. Related topics Contents Functions reference (alphabetical list) Position Purpose Returns the starting position of the specified occurrence of searchString in text.
Examples Position(“Mississippi”;“iss”;1;1) returns 2. Position(“Mississippi”;“iss”;1;2) returns 5. Position(“Mississippi”;“iss”;3;1) returns 5. Left(Name;Position(Name;“ “;1;1)-1) returns William, when Name is a text field that contains William Smith. Right(Name;Length(Name) - Position(Name;“ “;Length(Name);-1)) returns Smith. Related topics Contents Functions reference (alphabetical list) Proper Purpose Returns the first letter of each word in text as uppercase and all other letters as lowercase.
Format Quote(text) Parameters text - any text expression or field Data type returned text Originated in FileMaker Pro 7.0 Description This function protects text from being evaluated by the Evaluate function. Special characters within text are escaped appropriately. Examples Quote(“hello”) returns “hello”. Quote(“abc\¶”) returns “abc\¶”. Quote(“say \”hello\” fred”) returns “say \”hello\” fred”. Evaluate(Quote(“1 + 2”)) returns 1 + 2. Evaluate(“1 + 2&” & Quote(“ - 1 + 2”)) returns 3 - 1 + 2.
Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Character replacement in text begins at the start character position and continues for numberOfCharacters characters. Compare to the Substitute function. Examples Replace(“1234567”;5;1;“X”) returns 1234X67. Replace(“1234567”;5;1;“XX”) returns 1234XX67. Replace(“1234567”;5;2;“X”) returns 1234X7. Replace(“William”;3;4;”NEW TEXT”) returns WiNEW TEXTm.
Examples Right(“Manufacturing”;4) returns ring. Right(Name;Length(Name) - Position(Name;“ “;1;1)) returns Cannon, when the Name field contains Michelle Cannon. Right(SerialNumber;3) & Upper(Left(LastName;4)) returns 187FERR when the SerialNumber text field contains 00-48-187 and LastName contains Ferrini. Related topics Contents Functions reference (alphabetical list) RightValues Purpose Returns a text result containing the specified numberOfValues in text, counting from the right.
Examples RightValues(“Plaid¶Canvas¶Suitcase”;2) returns Canvas Suitcase RightValues(names;1) returns John when the names field contains • Sophie • Bill • John Related topics Contents Functions reference (alphabetical list) RightWords Purpose Returns a text result containing the numberOfWords in text, counting from the right.
RomanHankaku Purpose Converts from Zenkaku alphanumeric and symbols to Hankaku alphanumeric and symbols. Format RomanHankaku(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples RomanHankaku(“M a c i n t o s h”) returns Macintosh. Related topics Contents Functions reference (alphabetical list) RomanZenkaku Purpose Converts from Hankaku alphanumeric and symbols to Zenkaku alphanumeric and symbols.
Related topics Contents Functions reference (alphabetical list) SerialIncrement Purpose Returns the combined text and numbers specified by text, with the numbers in text incremented by the specified amount. Format SerialIncrement(text;incrementBy) Parameters text - any text that also contains a number incrementBy - any numeric expression to increment the text by Data type returned text Originated in FileMaker Pro 7.
Substitute Purpose Returns a text string with every occurrence of searchString in text replaced by replaceString in text. Format Substitute(text;searchString;replaceString) Parameters text - any text expression or text field searchString - any text expression or text field replaceString - any text expression or text field Data type returned text Originated in FileMaker Pro 6.0 or earlier Description This function is case-sensitive. Compare to the Replace function.
Trim Purpose Returns text stripped of all leading and trailing spaces. Format Trim(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Use this function to remove unneeded spaces when you convert files from other programs or systems that require a fixed number of characters per field, or to remove spaces accidentally typed during data entry. Examples Trim(“ Tom ”) returns Tom.
Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Set trimSpaces to True (1) if you want to include the removal of full-width spaces between nonRoman and Roman characters. Set trimSpaces to False (0) if you do not. A character is considered Roman if its Unicode value is less than U+2F00. Any character whose Unicode value is greater than or equal to U+2F00 is considered non-Roman.
Examples TrimAll( ) returns if the value of TrimAll( field is ) returns Related topics Contents Functions reference (alphabetical list) Upper Purpose Returns all letters in text as uppercase. Format Upper(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Use the Upper function to ensure consistent data entry of such things as state abbreviations or postal codes. Examples Upper(“Ca”) returns CA.
ValueCount Purpose Returns a count of the total number of values in text. Format ValueCount(text) Parameters text - any text expression or text field Important See Design functions for information about literal text parameters. Data type returned number Originated in FileMaker Pro 7.0 Description Values are text items separated by carriage returns. You can place several items together to create a carriage-return-delimited list of values.
Format WordCount(text) Parameters text - any text expression or text field Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples WordCount(“The sun is rising.”) returns 4. WordCount(Letter) returns the total number of words in the Letter field. Note The ampersand (&) and hyphen (-) characters identify the beginning of a new word.
Text formatting functions Text formatting functions can be used to change the color, font, size, and style of the specified text. For example, you could use the TextFont function to change the font of the specified text from Arial to Courier. You can use these functions together to change the apperance of text on your layouts. Text formatting functions operate on these parameters: • fields of type text • text constants (in quotations) • expressions having a text result Click a function name for details.
Originated in FileMaker Pro 7.0 Description Numbers returned by this function can be passed as the color parameter in the TextColor or TextColorRemove functions. The RGB function uses the following formula to calculate the result: red * 2562 + green * 256 + blue where 2562 = 65536 Tip To determine the RGB value of a color: • (Windows) In Layout mode, click the Fill Color palette in the formatting bar and choose Other Color. Values are shown for each of the basic colors.
Originated in FileMaker Pro 7.0 Description Use this function to change the color of text. Note Text formatting options will be lost if the field type that is returned is something other than text. Tip To determine the RGB value of a color: • (Windows) In Layout mode, click the Fill Color palette in the formatting bar and choose Other Color. Values are shown for each of the basic colors. • (Mac OS) Start the DigitalColor Meter application in the Applications/Utilities folder.
Originated in FileMaker Pro 8.0 Description Use this function to revert text to the default font color for the field. If you don’t use the RGB function to specify a color, all of the text displays in the default font color that was set in Layout mode for the field. When the font color is specified by the RGB function, only the specified font color is removed from every portion of the text displayed in that color and these same portions of the text are then displayed in the field's default font color.
Description Spellings for font names must be correct and are case-sensitive. Text formatting options will be lost if the field type that is returned is something other than text. FileMaker Pro looks for a font that matches the specified font name and font script character set. If no matches exist, FileMaker Pro looks for a default font with the font script specified in the Fonts tab of the Preferences dialog box.
Parameters text - any text expression or text field. fontToRemove - any font name expressed in text. fontScript - the name of a character set that contains characters required for writing in the specified language. Parameters in curly braces { } are optional. Note The fontScript parameter is not enclosed in quotation marks (“ “), and can have any of the values listed below in Description. Data type returned text Originated in FileMaker Pro 8.
Examples TextFontRemove(“Arial Text and Courier Text”) returns Arial Text and Courier Text displayed in the field’s default font. TextFontRemove(“Arial Text and Courier Text”;“Arial”) returns Arial Text and Courier Text with the Arial font removed from the words Arial Text for all fontScripts that use the Arial font. TextFontRemove(“Arial Text and Courier Text”;“Arial”;Cyrillic) returns Arial Text and Courier Text with the Arial font removed from Cyrillic character sets.
TextSize Purpose Changes the font size of text to fontSize. Format TextSize(text;fontSize) Parameters text - any text expression or text or number field fontSize - any font size expressed as an integer Data type returned text, number Originated in FileMaker Pro 7.0 Description The font size is described in points (72 points to the inch). Text formatting options will be lost if the data type that is returned is something other than text or number.
sizeToRemove - any font size expressed as an integer. Parameters in curly braces { } are optional. Data type returned text Originated in FileMaker Pro 8.0 Description Use this function to revert text to the default font size for the field. If you don’t specify a size, all of the text displays in the default font size that was set in Layout mode for the field.
Originated in FileMaker Pro 7.0 Description You can add multiple styles by using the + operator between style names. Negative values are not valid. All styles will be removed, if the only style specified is Plain. Plain is ignored if mixed with other styles. Styles are not case-sensitive and do not contain spaces. Text formatting options will be lost if the field type that is returned is something other than text.
Substitute(ArticleBody;[“Phrase1”;TextStyleAdd(“Phrase 1”;Italic)];[“Phrase 2”;TextStyleAdd(“Phrase 2”;Bold)];) Related topics Contents Functions reference (alphabetical list) TextStyleRemove Purpose Removes the specified styles from text in a single action. Format TextStyleRemove(text;styles) Parameters text - any text expression or text field styles - any named style from the list of available styles Data type returned text Originated in FileMaker Pro 7.
• Uppercase • Lowercase • Titlecase • WordUnderline • DoubleUnderline • AllStyles (all available styles) Examples TextStyleRemove(“Plaid”;Italic) returns the word Plaid with the italics style removed. TextStyleRemove(FirstName;Bold + Underline) returns Sophie with the bold and underlined styles removed when the FirstName field contains Sophie. TextStyleRemove(FirstName;AllStyles) returns Sophie without any styles.
Time functions Time functions calculate times and manipulate time information. Click a function name for details. This function Returns Hour A number representing the number of hours in a time value. Minute A number representing the number of minutes in a time value. Seconds A number representing the number of seconds in a time value. Time A time result with the specified number of hours, minutes, and seconds. Hour Purpose Returns a number representing the number of hours in time.
Minute Purpose Returns a number representing the number of minutes in time. Format Minute(time) Parameters time - any time value or field of type time Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples Minute(“12:15:23”) returns 15. Hour(Duration) + (Minute(Duration)/60) returns 2.5, if the Duration time field contains 2:30:15. Related topics Contents Functions reference (alphabetical list) Seconds Purpose Returns a number representing the number of seconds in time.
Examples Seconds(“12:15:23”) returns 23. Hour(Duration) + (Minute(Duration)/60) + (Seconds(Duration)/3600 returns 2.504166, if the Duration time field contains 2:30:15. Related topics Contents Functions reference (alphabetical list) Time Purpose Returns a time result with the specified number of hours, minutes, and seconds.
Timestamp functions Timestamps are used for a wide variety of synchronization purposes, such as marking the exact date and time at which a particular event occurred. This function Returns Timestamp A timestamp containing a calendar date and time of day. Timestamp Purpose Returns a timestamp containing date as a calendar date and time as a time of day.
Trigonometric functions Trigonometric functions are used to calculate degrees, angles, and other geometric data. Note All trigonometric functions use radians as the unit of measure. Once you have a result, you can convert the radians into degrees using the Degrees function. Click a function name for details. This function Returns Acos The arccosine, or inverse cosine, of a number. Asin The arcsine, or inverse sine, of a number. Atan The trigonometric arc tangent, or inverse tangent, of a number.
Examples Acos(-0.5) returns 2.0943951. Acos(-0.5)*180/Pi returns 120. Degrees(Acos(-0.5)) returns 120. Acos(2.0)returns ? (not a number). Related topics Contents Functions reference (alphabetical list) Asin Purpose Returns the arcsine (Asin), or inverse sine, of number. Format Asin (number) Parameters number - any numeric expression or field containing a numeric expression in the range -1 to 1 Data type returned number Originated in FileMaker Pro 9.
Atan Purpose Returns the trigonometric arc tangent (Atan), or inverse tangent, of number. Format Atan(number) Parameters number - any numeric expression or field containing a numeric expression Data type returned number Originated in FileMaker Pro 6.0 or earlier Description The arc tangent is the angle, in radians, whose tangent is equal to the specified number. Examples Atan(1) returns .78539816.... Degrees(Atan(1)) returns 45.
Originated in FileMaker Pro 6.0 or earlier Examples Cos(1.047) returns .50017107.... Cos(Radians(60)) returns .5. Related topics Contents Functions reference (alphabetical list) Degrees Purpose Converts angleInRadians to degrees. Format Degrees(angleInRadians) Parameters angleInRadians - any numeric expression or field containing a numeric expression, in radians Data type returned number Originated in FileMaker Pro 6.
Pi Purpose Calculates the value of the constant Pi (π), which is approximately 3.14159. Format Pi Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples Pi * 15 returns 47.124. Related topics Contents Functions reference (alphabetical list) Radians Purpose Converts angleInDegrees to radians.
Description The parameters for FileMaker Pro trigonometric functions must be expressed in radians. If the values you want to use as parameters in a trigonometric equation are in degrees, use this function to convert them to radians first. A degree is equal to Pi/180 radians. π • angleInDegrees Radians = ----------------------------------------------------180 Examples Radians(45) returns .78539816.... Sin(Radians(30)) returns .5.
Tan Purpose Returns the tangent (Tan) of angleInRadians. Format Tan(angleInRadians) Parameters angleInRadians - any numeric expression or field containing a numeric expression, in radians Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Use this function to calculate the Tan of angleInRadians. Note With the Tan function, you cannot use values exactly equal to 90 degrees (Pi/2 radians), or multiples of 90 degrees.
