FileMaker ® Pro 12 Functions Reference
© 2007–2012 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 . . . . . . . . . . . . . . . .
ScriptNames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 TableIDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 TableNames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 ValueListIDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Get(FoundCount) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Get(HighContrastColor) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 Get(HighContrastState) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 Get(HostApplicationVersion) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Get(UseSystemFormatsState) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Get(UUID) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Get(WindowContentHeight) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Get(WindowContentWidth) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Mod . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 Random . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 Round . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Right . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235 RightValues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 RightWords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237 RomanHankaku . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Acos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262 Asin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Functions reference (alphabetical list) G Get(AccountExtendedPrivileges) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Get(AccountName) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Get(AccountPrivilegeSetName) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Get(ActiveFieldContents) . . . . . . . . . . . . . . . . . . . . .
Functions reference (alphabetical list) Get(PersistentID) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 Get(PreferencesPath) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 Get(PrinterName) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 Get(QuickFindText) . . . . . . . . . . . . . . . .
Functions reference (alphabetical list) GetAsSVG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 GetAsText . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 GetAsTime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 GetAsTimestamp . . . . . . . . . . . . .
Functions reference (alphabetical list) MiddleWords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230 Min . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Minute . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 Mod . . . . . . . . . . . . . .
Functions reference (alphabetical list) Tan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268 TextColor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247 TextColorRemove . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248 TextFont . . . . . . . . . . . . . .
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).
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. Related topics Contents Functions reference (alphabetical list) List Purpose Returns a concatenated list of non-blank values (separated by carriage returns) for a field or fields. Format List(field{;field...
• $f1 contains orange. 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).
Notes • 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. • Aggregate functions such as Min or Max use the data type of the first parameter to perform all comparisons. For example, if the first parameter’s data type is text, all other parameters are converted to text and then compared.
Examples A Contracts table has a portal showing bids submitted for each contract. Min(Bids::Price) returns the lowest bid submitted for a contract. 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 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.
• a repeating field (repeatingField). • 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.
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. • several non-repeating fields in a record (field1;field2;field3...).
Sum Purpose Returns the total of all valid, non-blank values in field. Format Sum(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).
If the calculation result isn’t a repeating field: • Sum(Field2) returns 26. • Sum(Field1;Field2;Field3) returns 12. If the calculation result is a repeating field: • Sum(Field2) returns a repeating field with 26 in the first repetition. • Sum(Field1;Field2;Field3) returns a repeating field with 12, 8, 7, 8. Note When a referenced field is a repeating field, the Sum function returns the sum of the first repetition field, then the sum of the second repetition field, and so on.
• several fields in the first matching record specified by (table::field1;table::field2;...). You can include fields from different tables (table 1::field A;table 2::field B...). x 12 + x 22 + + x n2 x 1 + x 2 + + x n 2 Variance = -------------------------------------------- – --------------------------------------------------n–1 n n – 1 Examples A portal displays the related values 5, 6, 7, and 8 in Scores. Variance(table::Scores) returns 1.66666666....
Data type returned number Originated in FileMaker Pro 7.0 Description The variance of a population distribution is a measure of how spread out the distribution is. 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. • several non-repeating fields in a record (field1;field2;field3...).
Related topics Contents Functions reference (alphabetical list) FILEMAKER FUNCTIONS REFERENCE 30
Container functions Container functions calculate, manipulate, and report on data in container fields. Click a function name for details. This function Returns GetHeight The height of the image in a container field that holds images. GetThumbnail An image stored in the container field, resized according to specified values for width and height. GetWidth The width of the image in a container field that holds images.
GetThumbnail Purpose Returns a thumbnail image of the content in a container field, according to specified values for width and height. Format GetThumbnail(field;width;height) Parameters field - any text, number, date, time, timestamp, or container field; or any text expression or numeric expression. width - the width for the thumbnail. height - the height for the thumbnail. Data type returned container Originated in FileMaker Pro 12.
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 12.0 Description Returns the width in pixels of images in a container field that holds images. Otherwise, GetWidth returns 0. Examples GetWidth(Product) returns 1024.
Examples VerifyContainer(Photo) returns: • 0 (False) if files saved externally were modified or deleted. • 1 (True) if no changes or deletions occurred. • ? if the Photo field is not a container field.
Date functions Date functions calculate dates and manipulate date information. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see Conversion of dates with two-digit years. Note System formats affect the way dates are displayed. See Opening files with foreign system formats. Tip You can use zero (0) and negative numbers as Date function arguments.
Parameters month - the month of the year (a one-digit or two-digit number; see note). day - the day of the month (a one-digit or two-digit number; see note). 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.
Format Day(date) 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. For more information about how FileMaker Pro handles two-digit dates, see Conversion of dates with two-digit years. Examples Day(“5/15/2014”) returns 15. This example assumes that the system date format is MM/DD/ YYYY.
Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples DayName(Date(10;7;2014)) returns Tuesday. DayName(ProjectDue) returns Tuesday when ProjectDue is 10/7/2014. DayName(“10/7/2014”) 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.
Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see Conversion of dates with two-digit years. Contents Functions reference (alphabetical list) 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.
DayOfYear Purpose Returns a number equal to the number of days from the beginning of the year of date. Format DayOfYear(date) Parameters date - any calendar date Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples DayOfYear(Billing Date) returns 32, when Billing Date is 2/1/2014.
Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples Month(“3/19/2014”) returns 3. This example assumes that the operating system date format is set to MM/DD/YYYY. Month(Payment) returns 3, where Payment contains March 19, 2014. (The Payment field must be of type date.) “Bill Due by: ” & Date(Month(DateSold) + 1;Day(DateSold);Year(DateSold)) returns Bill Due by: followed by a value that is one month later than DateSold.
“Payment for: ” & MonthName(Date(Month(Payment) + 1;Day(Payment);Year(Payment))) returns Payment for: followed by the name of the month that is one past the month of the last payment. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see Conversion of dates with two-digit years. Related topics Contents Functions reference (alphabetical list) MonthNameJ Purpose Returns the name of the month of date in Japanese.
Format WeekOfYear(date) Parameters date - any calendar date Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Fractions of weeks occurring at the beginning or end of the year count as full weeks, so the WeekOfYear function returns values 1 through 54. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see Conversion of dates with two-digit years.
Data type returned number Originated in FileMaker Pro 6.0 or earlier Description startingDay indicates which day is considered the first day of the week. The first week of the year is the first week that contains four or more days of that year. For example, if you select 1 (Sunday) as the starting day, then January 1 must be on Sunday, Monday, Tuesday, or Wednesday for that week to be the first week of the fiscal year.
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. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see Conversion of dates with two-digit years. Examples Year(DateSold) returns the year stored in DateSold. Year(“5/5/2014”) returns 2014.
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.8) Name of Emperor in 0 = Long, 1 = Abbreviated, 2 = 2 byte Roman. Seireki is returned when date is before listed emperors. Examples YearName(DateField;0) Returns when DateField contains 7/15/2008.
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. See Creating and managing privilege sets for more information about granting access to database files.
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 the location in points of each field boundary and the field’s rotation in degrees. 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. Important See Design functions for information about literal text parameters.
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.
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.
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 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.
GetNextSerialValue Purpose Returns the next serial number of fieldName in fileName. Format GetNextSerialValue(fileName;fieldName) Parameters fileName - the name of an open database file (local or remote). 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.
Important See Design functions for information about literal text parameters. Data type returned text Originated in FileMaker Pro 6.0 or earlier Description If no parameter is specified for fileName, FileMaker returns results for the current file. Examples LayoutIDs(“Customers”) returns a list of all the layout IDs in the Customers database file.
Related topics Contents Functions reference (alphabetical list) LayoutObjectNames Purpose Returns a list of the names of all named objects on layoutName in fileName, separated by carriage returns. Format LayoutObjectNames(fileName;layoutName) 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.
RelationInfo Purpose Returns a list of four values for each relationship directly related to tableName. Format RelationInfo(fileName;tableName) Parameters fileName - the name of an open database file (local or remote). tableName - the name of a 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.
• You can create records in all tables. • You cannot delete records in all tables. • A sort was specified for the Addresses table for the Employees<-->Addresses relationship.
Related topics Contents Functions reference (alphabetical list) ScriptNames Purpose Returns a list of the names of all scripts in fileName, separated by carriage returns. Format ScriptNames(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.
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 Description Each table ID is unique. Also, the ID is independent of when you create each table: the first table could have the smallest, middle, or largest value. If no parameter is specified for fileName, FileMaker returns results for the current file.
Originated in FileMaker Pro 6.0 or earlier Description If no parameter is specified for fileName, FileMaker returns results for the current file. Examples TableNames(“University Database”) returns table occurrences Teachers Coaches for the University Database database file if a Teachers table and a Coaches table have been defined for the file.
Related topics Contents Functions reference (alphabetical list) ValueListItems Purpose Returns a list of the values in valuelist, separated by carriage returns. Format ValueListItems(fileName;valuelist) Parameters fileName - the name of an open database file (local or remote). valuelist - the name of a value list in the specified database file. 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 Description If no parameter is specified for fileName, FileMaker returns results for the current file. Examples ValueListNames(“Customers”) returns a list of all the value list names in the Customers database file.
windows, and then the hidden windows. If there are no databases or windows open, an empty string is returned. Note Even if you close a file, it may remain open as a hidden file if the window of any other file is displaying data from that file. (For example, another window may be displaying related data from the file you attempted to close.) FileMaker Pro will close the file when you close all the dependent windows.
External functions Use external functions to access FileMaker Pro plug-ins. Plug-ins add features to FileMaker Pro. For more information, see Setting plug-in preferences. 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.
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.
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.
• The FV function doesn’t account for the present value of your investment, and it assumes that payment is made at the end of each period. 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....
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 73
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 application version. Get(CalculationRepetitionNumber) A number representing the repetition of the calculation field that is currently being calculated. Get(ConnectionState) A number representing the security state of the network connection for the current file. Get(CurrentDate) The current date according to the system calendar.
This function Returns Get(LastError) A number representing the error, if any, in the execution of the most recently executed script step. Get(LastMessageChoice) A number corresponding to the button clicked in an alert message displayed by the Show Custom Dialog script step. Get(LastODBCError) A string that shows the error state published by ODBC standards, based on ISO/IEF standards.
This function Returns Get(ScriptName) The name of the script currently running (or paused). Get(ScriptParameter) The script parameter passed into the current script. Get(ScriptResult) The script result from a performed subscript. Get(SortState) A number value representing the current sort state. Get(StatusAreaState) A number representing whether the status toolbar is hidden, visible, visible and locked, or hidden and locked.
This function Returns Get(WindowLeft) A number representing the horizontal distance, in pixels, of the outer edge of the current window relative to the left-most edge of the screen. Get(WindowMode) A number representing whether FileMaker Pro is in Browse mode, Find mode, Preview mode, or printing when the function is evaluated. Get(WindowName) The name of the current window of the file in which the calculation is defined.
End If Go to Record/Request/Page [Exit after last, Next] End Loop Go to Layout [original layout] Get(AccountExtendedPrivileges) Purpose Returns a list of keywords, separated by carriage returns, for the enabled extended privileges. The list that is returned is based on the account used to open the database file. See also Get(CurrentExtendedPrivileges) function. Format Get(AccountExtendedPrivileges) Parameters None Data type returned text Originated in FileMaker Pro 11.
If you are logged in and running a script that is set to run with full access privileges, Get(AccountExtendedPrivileges)returns the extended privileges for your account, but Get(CurrentExtendedPrivileges)returns the extended privileges for the Admin account. Related topics Contents Functions reference (alphabetical list) Get(AccountName) Purpose Returns the name of the authenticated account being used by the current user of the database file.
Related topics Get(UserName) function Contents Functions reference (alphabetical list) Get(AccountPrivilegeSetName) Purpose Returns the name of the privilege set that is being used by the account used to open the database. See also Get(CurrentPrivilegeSetName) function Format Get(AccountPrivilegeSetName) Parameters None Data type returned text Originated in FileMaker Pro 11.
Get(ActiveFieldContents) Purpose Returns the contents of the field that has the focus. Format Get(ActiveFieldContents) Parameters None Data type returned text, number, date, time, timestamp, container 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.
Parameters None 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 http://help.filemaker.com. Examples Returns Country, when the focus is in the Country field.
Examples There are two fields, Teachers::Name and Coaches::Name, on the current layout. Creating a script that returns the result of Get(ActiveFieldTableName) to a third field will return Teachers when the script is performed after clicking in the Teachers::Name field, or will return Coaches after clicking in the Coaches::Name field.
Get(ActiveModifierKeys) Purpose Returns a number representing the keyboard modifier keys (for example, Control+Shift) that are being pressed. Format Get(ActiveModifierKeys) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description The number returned is calculated by summing numbers representing each modifier key being pressed.
Format Get(ActivePortalRowNumber) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier 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.
Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description The first repetition returns 1. If the current field isn’t a repeating field, this function returns 1. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 5 when the focus is in the fifth repetition of a repeating field.
Examples Returns 4 when 4 characters are selected. Related topics Contents Functions reference (alphabetical list) Get(ActiveSelectionStart) Purpose Returns a number representing the starting character of the selected text. Format Get(ActiveSelectionStart) Parameters None Data type returned number Originated in FileMaker Pro 7.0 Description Returns the cursor's current position if no text is selected.
Format Get(AllowAbortState) 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 http://help.filemaker.com. Examples Returns 1 if Allow user abort script step is on.
Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 1 if toolbars are allowed to be visible. Related topics Contents Functions reference (alphabetical list) Get(ApplicationLanguage) Purpose Returns text representing the current application language. Format Get(ApplicationLanguage) Parameters None Data type returned text Originated in FileMaker Pro 7.
Examples Returns English when the current application language is English. Related topics Contents Functions reference (alphabetical list) Get(ApplicationVersion) Purpose Returns text representing the FileMaker application and version. Format Get(ApplicationVersion) Parameters None Data type returned text Originated in FileMaker Pro 6.
Related topics Contents Functions reference (alphabetical list) Get(CalculationRepetitionNumber) Purpose Returns a number representing the repetition of the calculation field that is currently being calculated. Format Get(CalculationRepetitionNumber) Parameters None Data type returned number 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.
Parameters None Data type returned number Originated in FileMaker Pro 12.0 Description Returns a value indicating whether the FileMaker Pro or FileMaker Go connection to the host uses SSL, including whether the Server name matches the Server-side certificate (providing the highest security). Returns: • 0 for no network connection for the current file. • 1 for a non-secured connection (FileMaker Server with SSL disabled, or to a FileMaker Pro host).
Parameters None Data type returned date Originated in FileMaker Pro 6.0 or earlier Description The format of the result of this function varies based on the date format that was in use when the database file was created. In the United States, dates are generally in the format MM/DD/YYYY. You can change the date and time formats in your operating system. If the result is displayed in a field, it is formatted according to the date format of the field in the current layout.
Originated in FileMaker Pro 6.0 or earlier Description Extended privileges are additional access rights assigned to an account’s privilege set. For more information, see About account, privilege sets, and extended privileges. Returns an empty list if a user doesn’t have extended privileges assigned for the current database file.
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. Notes • The client machine and host machine may be in different times zones so Get(CurrentHostTimestamp) and Get(CurrentTimestamp) may return different date/time values. Also, the current date and time are characteristics of the host system, but the format of the date and time is a characteristic of the database file.
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. • If you select the Run script with full access privileges script option, this function returns [Full Access].
Notes • If the calculation result for this script step is set to integer format, it will return the total number of seconds elapsed since the start of the current day. • 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 http://help.filemaker.com.
Related topics Contents Functions reference (alphabetical list) Get(CustomMenuSetName) Purpose Returns the name of the active custom menu set. Format Get(CustomMenuSetName) Parameters None Data type returned text Originated in FileMaker Pro 8.0 Description If the active menu set isn’t a custom menu set, an empty string is returned. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com.
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/. In the Mac OS, the path format is /DriveName/Users/UserName/Desktop/. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns /C:/Documents and Settings/John Smith/Desktop/ for a user named John Smith in Windows.
In Windows Vista, the path format is /Drive:/Users/UserName/Documents/. In the Mac OS, the path format is /DriveName/Users/UserName/Documents/. When running on FileMaker Server, Get(DocumentsPath) returns the location of the Documents folder, which is in the same folder as the server's Backups, Databases, and Scripts folders. The Documents folder is used as a shared location that scripts from different sessions or other processes on the machine can use to import or export files.
Use Get(DocumentsPathListing) with the Import Records script step and Export Records script step to determine if a file exists in the Documents folder before using the Open File script step to open the file. Get(DocumentsPathListing) ensures that multiple scripts can safely read from and write to the same FileMaker Pro database. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com.
Description For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 1 if the Set Error capture script step is on. Related topics Contents Functions reference (alphabetical list) Get(FileMakerPath) Purpose Returns the path to the folder of the currently running copy of FileMaker Pro. Format Get(FileMakerPath) Parameters None Data type returned text Originated in FileMaker Pro 8.
Get(FileName) Purpose Returns the name of the currently active database file, without the filename extension. Format Get(FileName) Parameters None Data type returned text 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 In Windows, the full path is file:/drive:/folder/filename for local files. For remote files, the full path is file://volume/folder/filename. In the Mac OS, the full path is file:/volume/folder/filename for local and remote files. 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.
Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 15000 when the current file size is 15000 bytes. Related topics Contents Functions reference (alphabetical list) Get(FoundCount) Purpose Returns a number that represents the number of records in the current found set. Format Get(FoundCount) Parameters None Data type returned number Originated in FileMaker Pro 6.
Get(HighContrastColor) Purpose Returns the name of the current high contrast default color scheme if Use High Contrast is selected in the Windows OS Accessibility Options dialog box. Format Get(HighContrastColor) Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Returns an empty value (null) if Use High Contrast is unavailable, inactive, or if this function is used on the Mac OS.
Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Returns: • 0 if Use High Contrast is unavailable, inactive, or if the function is used on the Mac OS. • 1 if Use High Contrast is available and active. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com.
Returns ProAdvanced 12.0v1 when the host computer is running FileMaker Pro 12 Advanced version 1. Returns Server 12.0v1 when the host computer is running FileMaker Server 12 version 1. Related topics Contents Functions reference (alphabetical list) Get(HostIPAddress) Purpose Returns the IP address of the host machine for the current database. Format Get(HostIPAddress) Parameters None Data type returned text Originated in FileMaker Pro 8.
Related topics Contents Functions reference (alphabetical list) Get(HostName) Purpose Returns the registered name of the computer that is hosting the database file. Format Get(HostName) Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier 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.
Get(InstalledFMPlugins) Purpose Returns the name, version number (if available), and enabled state of installed plug-ins. Format Get(InstalledFMPlugins) Parameters None Data type returned text Originated in FileMaker Pro 12.0 Description The Get(InstalledFMPlugins) function is useful for determining whether an installed plug-in newer or older than a plug-in required by a file.
Functions reference (alphabetical list) Get(LastError) Purpose Returns a number representing the error, if any, in the execution of the most recently executed script step. Format Get(LastError) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description Use this function to detect and control the outcome of errors. See FileMaker Pro error codes.
Returns 401 when no records are found after the Perform Find script step has been executed. Related topics Contents Functions reference (alphabetical list) Get(LastMessageChoice) Purpose Returns a number corresponding to the button clicked in an alert message that is displayed by the Show Custom Dialog script step. Format Get(LastMessageChoice) Parameters None Data type returned number Originated in FileMaker Pro 6.
Format Get(LastODBCError) Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier Description • For ODBC imports and Execute SQL script steps, returns a detailed, textual ODBC error message. • For working with ODBC data sources in the relationships graph, returns the readable error string that is generated by the ODBC driver. Notes • You can set the Set Error Capture state to “on” to suppress the error messages. You can also use Get(LastError) to get generic errors.
Data type returned number Originated in FileMaker Pro 6.0 or earlier Description You assign the privileges in the Custom Layout Privileges dialog box. Returns: • 0 if the custom layout privileges of an account’s privilege set allow no access to Records via this layout • 1 if the custom layout privileges of an account’s privilege set allow view only access to Records via this layout.
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 http://help.filemaker.com. Examples Returns 3 when the file has three layouts. Related topics Contents Functions reference (alphabetical list) Get(LayoutName) Purpose Returns the name of the layout currently displayed.
Related topics Contents Functions reference (alphabetical list) Get(LayoutNumber) Purpose Returns the number of the layout currently displayed, according to the list in the Manage Layouts dialog box. Format Get(LayoutNumber) 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, each window can have its own layout number value, but results are returned for only the foreground window.
Parameters None Data type returned text Originated in FileMaker Pro 7.0 Description If no windows are open, an empty string is returned. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples There are two layouts, Teachers Layout and Coaches Layout, with corresponding tables named Teachers and Coaches in the table Instructors.
• 0 (zero) if the database file is in Form View • 1 if the database file is in List View • 2 if the database file is in Table View If there are multiple windows open in the current database file, each window can have its own layout view state 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 http://help.filemaker.com.
• For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 0 when access is denied to other users. Related topics Contents Functions reference (alphabetical list) Get(NetworkProtocol) Purpose Returns the name of the network protocol (TCP/IP) that FileMaker Pro is using on this machine.
Format Get(PageNumber) Parameters None Data type returned number Originated in FileMaker Pro 6.0 or earlier Description If nothing is being printed or previewed, 0 is returned. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 4 when page 4 is being printed or previewed.
Description Returns a unique, unchanging identifier for the computer on which FileMaker Pro is running or the device on which FileMaker Go is running, in the form of a 32-digit hexadecimal string. Get(PersistentID) helps you identify devices that access your solution. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com.
Returns /MacintoshHD/Users/John Smith/Library/Preferences/ for a user named John Smith in the Mac OS. Related topics Contents Functions reference (alphabetical list) Get(PrinterName) Purpose Returns a string identifying the default printer name. Format Get(PrinterName) Parameters None Data type returned text Originated in FileMaker Pro 6.
Functions reference (alphabetical list) Get(QuickFindText) Purpose Returns the text that was entered in the Quick Find box. Format Get(QuickFindText) Parameters None 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.
Data type returned number Originated in FileMaker Pro 6.
Parameters None Data type returned number 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.
Originated in FileMaker Pro 6.0 or earlier 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.
Originated in FileMaker Pro 6.0 or earlier 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.
Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. 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.
Related topics Contents Functions reference (alphabetical list) 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.
Parameters None Data type returned number 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 http://help.filemaker.com. 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 http://help.filemaker.com. 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).
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. • 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.
Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 1, when the current status toolbar is visible. Related topics Contents Functions reference (alphabetical list) Get(SystemDrive) Purpose Returns the drive letter (Windows) or volume name (Mac OS) where the currently running operating system is located.
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.0 Description IP addresses are separated by carriage returns. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com.
Get(SystemLanguage) Purpose Returns the language currently set on the current system. Format Get(SystemLanguage) Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier Description Get(SystemLanguage) is evaluated on the system preference that is set for Region on the Formats tab. The text that is returned is in the English language.
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. In the Mac OS, find this address under Network Overview in the System Profile tab under Applications/Utilities/Apple System Profiler.
Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Get(SystemPlatform)returns -2 when the current platform is a Windows platform. Abs(Get(SystemPlatform)) returns 1 when the current platform is Mac OS X. Related topics Contents Functions reference (alphabetical list) Get(SystemVersion) Purpose Returns the version of the current operating system.
Get(TemporaryPath) Purpose Returns the path to the temporary folder that FileMaker Pro uses for the current user, or the path that FileMaker Server uses on the system. Format Get(TemporaryPath) Parameters None Data type returned text Originated in FileMaker Pro 9.0 Description The temporary folder name begins with S, followed by a number representing the session of the database engine during which the operation took place.
where DriveName is the name of your hard disk. S is the name of the folder in which the temporary files are placed (for example, S1); is a number representing the database engine session during which the operation took place. The location may vary due to different variables on the OS, but should follow a similar pattern. Related topics Contents Functions reference (alphabetical list) Get(TextRulerVisible) Purpose Returns a Boolean value representing whether or not the text ruler is visible.
Format Get(TotalRecordCount) 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. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com.
Description Use with the Get(TriggerTargetTabPanel) function. Returns an index value, starting from 1, when running a script triggered by the OnTabSwitch script trigger, and the object name assigned to the tab panel. Returns 0 if the tab panel is invalid or if Get(TriggerCurrentTabPanel) is not used with the OnTabSwitch script trigger. You can use the GetValue function to extract the value you want to use from the index value returned by Get(TriggerCurrentTabPanel).
Examples The following code displays the text Processing input... when a carriage return is entered: If [ Code ( Get(TriggerKeystroke) ) = 13 ] Show Custom Dialog [“Processing input...”] End If Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com.
• Mac OS: Command key combinations do not activate script triggers. • For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples • The following example will only display a custom dialog box when lowercase “a” is entered: If [Get(TriggerKeystroke)=“a” and Get(TriggerModifierKeys)=0] Show Custom Dialog [“You entered \”a\”.
Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples When the tab panel to be switched to is tab number 2, named “Products,” Get(TriggerTargetTabPanel) returns: 2 Products Related topics Contents Functions reference (alphabetical list) Get(UserCount) Purpose Returns the number of clients currently accessing the file.
Examples Returns 5 when there are 4 clients accessing the database file. Related topics Contents Functions reference (alphabetical list) Get(UserName) Purpose Returns the name of the FileMaker Pro user, as specified in the General tab of the Preferences dialog box. Format Get(UserName) Parameters None Data type returned text Originated in FileMaker Pro 6.0 or earlier Description The returned name is user-specified.
Get(UseSystemFormatsState) Purpose Returns a Boolean value representing the state of the Use System Formats command in the Format menu. Format Get(UseSystemFormatsState) Parameters None Data type returned number 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 http://help.filemaker.com.
Originated in FileMaker Pro 12.0 Description Returns a unique 16-byte (128-bit) string. For example, you can use this function to generate a unique ID of a record. For unstored calculations, returns a new string each time Get(UUID) is evaluated. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Stored calculation: In a calculation field, specify the calculation Get(UUID).
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.
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.
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. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com.
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 http://help.filemaker.com. Examples Get(WindowHeight) returns 300 when the current window’s height is 300 pixels.
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.
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. • For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.
Parameters None Data type returned number Originated in FileMaker Pro 12.0 Description Returns: • 0 (zero) if the window is a document window • 1 if the window is a floating document window • 2 if the window is a dialog window Examples Returns 0 if the current window is a document window when the function is evaluated. Returns 1 if the current window is a floating document window when the function is evaluated. Returns 2 if the current window is a dialog window when the function is evaluated.
Description The origin of the reference coordinate system is at the left-most corner below the menu bar. A negative value indicates the portion of the top part of the window that is hidden behind the menu bar. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 52 when the outer edge of the active window is 52 pixels from the menu bar.
Related topics Contents Functions reference (alphabetical list) Get(WindowWidth) Purpose Returns a number representing the width, in pixels, of the window on which the script is acting (not necessarily the foreground window). Format Get(WindowWidth) Parameters None Data type returned number Originated in FileMaker Pro 7.0 Description The width of the window is calculated from the left-most to right-most outer edge of the window.
Parameters None Data type returned text Originated in FileMaker Pro 8.0 Description In Windows, an asterisk appears next to the zoom percentage when Enlarge window contents to improve readability is selected in the General tab of the Preferences dialog box. Note For information on how functions evaluate differently on the host versus the client, search the FileMaker Knowledge Base available at http://help.filemaker.com. Examples Returns 200 when the current window’s zoom percentage is set to 200.
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.
Format Case(test1;result1{;test2;result2;...;defaultResult}) 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.
Parameters test - Any integer calculation. The calculation result of test must be a number that indexes into the list that follows. Because the index is a 0-based index, the test result must be 0 to access the first result. result - one or more results. Parameters in curly braces { } are optional. Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 6.
Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 7.0 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.
The Evaluate function has an optional second parameter, which is a field the calculation is dependent on. When the dependent field contents change, FileMaker Pro re-evaluates the calculation. In the following example, the Total calculation will be re-evaluated when SubTotal changes: Evaluate(StateTaxFormula; SubTotal) + ShippingCost The dependent parameter can also be useful in other cases.
Related topics Contents Functions reference (alphabetical list) ExecuteSQL Purpose Executes an SQL query statement for the specified table occurrence within a FileMaker Pro database. Format ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator {;arguments...}) Parameters sqlQuery - an SQL query statement . The statement can include a Union clause that combines the results of two queries.
Examples Suppose a database contains two tables, Employees and Salaries, which are related through the EmpID field.
Description Returns a Boolean value. Examples GetAsBoolean(“”) returns 0. GetAsBoolean(“Some text here.”) returns 0. GetAsBoolean(Container Field) returns 1 when the field named Container Field contains data, or returns 0 when Container Field is empty. Related topics Contents Functions reference (alphabetical list) GetField Purpose Returns the contents of fieldName.
Suppose you have the fields FirstName and LastName. FirstName contains the text string Jane, and LastName contains the text string Public. • GetField(“FirstName”)&“ ”&GetField(“LastName”) returns the text string Jane Public. GetSummary(GetField(“Field1”), GetField(“Field” & “2”)) performs a summary on the summary field Field1, using a break field of Field2. Related topics Contents Functions reference (alphabetical list) GetFieldName Purpose Returns the fully qualified name of a field reference.
GetLayoutObjectAttribute Purpose Returns the specified attributes of the layout object given by objectName that is currently active in the calculation. Format GetLayoutObjectAttribute(objectName;attributeName{;repetitionNumber; portalRowNumber}) Parameters objectName - the name of a named layout object on the current layout. attributeName - the name of a supported attribute (see below). repetitionNumber - the repetition number (for repeating fields). portalRowNumber - the number of the row in the portal.
height - returns a number representing the height (in points) of the specified object. rotation - returns a number representing the rotation (in degrees) of the specified object. startPoint,endPoint - returns a pair of numeric values (horizontal vertical), separated by spaces, that represent the start point or end point of a line object. Other objects return the top-left point for startPoint and the bottom-right point for endPoint. source - returns the source description of the specified object as follows.
Examples GetLayoutObjectAttribute("CancelButton","objectType") returns button (if the button was created in a new file or a file after it was converted), returns button group (if the button is associated with a button action or script and was converted in a file from a previous version of FileMaker Pro), or returns text (if the button isn’t associated with a button action or script and was converted in a file from a previous version of FileMaker Pro).
GetNthRecord(Contacts::First Name;2) returns the contents of the First Name field for record 2 in the Contacts table. GetNthRecord(Contacts::Has Repetitions[2];2) returns the contents of the second repetition of the Has Repetitions field for record 2 in the Contacts table. Related topics Contents Functions reference (alphabetical list) If Purpose Returns one of two possible results (result1 or result2) depending on the value of test.
If(State =”CA”;Subtotal * CA Tax Rate;0) returns the tax if the purchaser is a resident of California; otherwise returns 0. Related topics Contents Functions reference (alphabetical list) IsEmpty Purpose Returns True(1) if field is empty, if a related field, related table, relationship, or file is missing, or if some other error occurs; otherwise, returns False(0).
Format IsValid(field) Parameters field - any field name Data type returned number Originated in FileMaker Pro 6.
Parameters expression - any calculation expression Data type returned number Originated in FileMaker Pro 7.0 Examples IsValidExpression(calculationField) returns 1 (true) if calculationField contains total + 1. IsValidExpression(calculationField) returns 0 (false) if calculationField contains abs(-1 with no closing parenthesis.
Description Multiple variables are allowed when using a list syntax that is enclosed in square brackets [ ] and is separated by semicolons. For example: Let([variable=value;variable2=value2];calculation) The $ symbol references a local variable and two $$ symbols reference a global variable. An optional repetition number appears in square brackets [ ] immediately after the variable name.
Lookup Purpose Returns the value specified in sourceField using the relationships in the relationships graph. The result of the optional failExpression will be returned if the lookup fails. Format Lookup(sourceField{;failExpression}) Parameters sourceField - the field from which the lookup value is taken. failExpression - any expression. Parameters in curly braces { } are optional. Data type returned text, number, date, time, timestamp, container Originated in FileMaker Pro 7.
The People and Company tables are related using the number field CompanyID. The calculation CompanyName = Lookup(Company::CompanyName;“Not found”) defined in the People table will return Apple for the first record, FileMaker for the second record, and Not found for the third record. Related topics Contents Functions reference (alphabetical list) LookupNext Purpose Returns the next lower or higher value in sourcefield when there isn’t a matching related value.
Item Weight Lamp 8 Chair 22 Desk 60 Bed 120 Rate Lookup Shipping Costs table Rate Code Maximum Weight A 25 B 50 C 100 D 150 The two tables are related by Weight and Max Weight. The calculation field Rate Lookup is defined as Rate Lookup = LookupNext(Shipping Costs::Max Weight; Higher). 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).
Originated in FileMaker Pro 9.0 Description The Self function provides a way for a calculation to reference the object with which it is associated without having to explicitly reference the object. Use Self to create a single calculation formula that can be applied to different objects. The Self function is helpful for conditional formatting calculations and tooltip calculations because it returns the content of the layout object when that object has a value.
Mobile functions Mobile functions are used with FileMaker Go. Click a function name for details. This function Returns Location The current latitude and longitude of a device running FileMaker Go. LocationValues The current latitude, longitude, and altitude of a device running FileMaker Go; the horizontal and vertical accuracy of the values returned; and the number of minutes since the values were returned.
Examples Location returns the following latitude and longitude for a device: +37.400000, -121.980000 Location ( 100; 40 ) takes up to 40 seconds to return the latitude and logitude with an accuracy of 100 meters. +110.230000, -131.340000 Related topics Contents Functions reference (alphabetical list) LocationValues LocationValues Purpose Returns the current latitude, longitude, and altitude on a device running FileMaker Go.
In FileMaker Pro, LocationValues returns an empty string. Note To avoid excessive battery consumption and repeat fetches, specify a larger number for accuracy and a smaller number for timeout. Examples LocationValues returns the following location for a device: 37.406489 -121.983428 0.000000 65 -1 0.
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(-3.9) returns -3. Int(123.9) 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 function with a precision of 16 to 400 digits to the right of the decimal point. Format SetPrecision(expression;precision) Parameters expression - any numeric expression precision - any number or numeric expression Data type returned number Originated in FileMaker Pro 7.0 Description All functions except trigonometric functions support extended precision.
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).
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).
• display subsummary values in Browse mode or in a body part Calculations using the GetSummary function are unstored. Note You can get similar results using a self-join relationship and Aggregate functions. For more information, see Summarizing data in portals. Examples GetSummary(Total Sales;Country) returns a summary of all records pertaining to the value in the Country field.
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 to 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.
GetAsNumber Purpose Returns only the numbers in text, as field type number, for use with formulas involving numbers or numeric functions. 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.
Parameters text - any text expression or text field Data type returned text 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.
Parameters data - any number, date, time or timestamp expression, or a field containing a number, date, time, timestamp, or container Data type returned text Originated in FileMaker Pro 6.0 or earlier Description The data returned can be a field type number, date, time, timestamp, or container.
Data type returned time Originated in FileMaker Pro 6.0 or earlier Description Use the GetAsTime or the Time function to enter a time constant into a formula. The format of the supplied time must be the same as the time format on the system where the file was created. 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.
Examples GetAsTimestamp(“4/5/2014 4:05:06”) returns 4/5/2014 4:05:06 AM. GetAsTimestamp(50000) returns 1/1/0001 1:53:20 PM. Related topics Contents Functions reference (alphabetical list) 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.
GetValue Purpose Returns the requested value given by valueNumber from listOfValues. Format GetValue(listOfValues;valueNumber) 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.
Parameters text - any text expression or text field Data type returned text 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.
KanaZenkaku Purpose Converts Hankaku Katakana to Zenkaku Katakana. Format KanaZenkaku(text) 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.
Examples KanjiNumeral(123) returns “) returns KanjiNumeral(“ Related topics Contents Functions reference (alphabetical list) 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.
Parameters text - any text expression or text field numberOfCharacters - any numeric expression or field containing a number Data type returned text Originated in FileMaker Pro 6.0 or earlier 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.
Description Values are text items separated by carriage returns. A value can be empty, a single character, a word, a sentence, or a paragraph. When you press Return you start creating a new value. The last value will be recognized with or without a carriage return. Each returned value ends with a carriage return, allowing lists to be easily concatenated.
Note Characters such as the ampersand (&) and hyphen (-) can be used to identify the beginning of a new word. Related topics Contents Functions reference (alphabetical list) Length Purpose Returns the number of characters in field, including all spaces, numbers, and special characters. 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.
Format Lower(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.0 or earlier 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.
Examples Middle(“(408)555-9054”;2;3) returns 408. Middle(PhoneNumber;2;3) returns 408 when the PhoneNumber field contains (408) 555-9054. Middle(“abcdefghij”;5;2) returns ef. Middle(Name;Position(Name;“ ”;1;1)+1;3) returns Smi, when the text field Name contains John Smith. Related topics Contents Functions reference (alphabetical list) MiddleValues Purpose Returns a text result containing the specified numberOfValues in text, starting with startingValue.
MiddleValues(list;2;2) returns Bill John when the list field contains • Sophie • Bill • John Related topics Contents Functions reference (alphabetical list) MiddleWords Purpose Returns a text result containing the numberOfWords from text, beginning at startingWord.
NumToJText Purpose Converts Roman numbers in number to Japanese text. Format NumToJText(number;separator;characterType) Parameters number - any numeric expression or field containing a number separator - a number from 0 - 3 representing a separator characterType - a number from 0 - 3 representing a type 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.
Functions reference (alphabetical list) PatternCount Purpose Returns the number of occurrences of searchString in text. Format PatternCount(text;searchString) Parameters text - any text expression or text field searchString - any text expression or text field representing the set of characters you want to find Data type returned number Originated in FileMaker Pro 6.0 or earlier Examples PatternCount(“Mississippi”;“is”) returns 2.
start - any numeric expression, or field containing a number, representing the number of characters from the start of the text string at which to begin the search. occurrence - any numeric expression or field containing a number, representing which instance of the text string you want to find. A negative occurrence value causes the scan to go in the opposite direction from start. A zero value for occurrence is invalid and returns a result of zero. Data type returned number Originated in FileMaker Pro 6.
Originated in FileMaker Pro 6.0 or earlier Examples Proper(“ABCD”) returns Abcd. Proper(Name) returns Yumiko Kitagawa, when the Name field contains YUMIKO KITAGAWA. Related topics Contents Functions reference (alphabetical list) Quote Purpose Returns the text form of text enclosed in quotation marks. Format Quote(text) Parameters text - any text expression or field Data type returned text Originated in FileMaker Pro 7.
Replace Purpose Replaces a string of characters in text with replacementText.
Format Right(text;numberOfCharacters) Parameters text - any text expression or text field numberOfCharacters - any numeric expression or field containing a number Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples Right(“Manufacturing”;4) returns ring. Right(Name;Length(Name) - Position(Name;“ “;1;1)) returns Cannon, when the Name field contains Michelle Cannon.
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. A value can be empty, a single character, a word, a sentence, or a paragraph. When you press Return you start creating a new value. The last value will be recognized with or without a carriage return.
Originated in FileMaker Pro 6.0 or earlier Examples RightWords(“Plaid Canvas Suitcase”;2) returns Canvas Suitcase. RightWords(Name;1) returns Virtanen, when the Name field contains Matti Virtanen. Note Characters such as the ampersand (&) and hyphen (-) can be used to identify the beginning of a new word. Related topics Contents Functions reference (alphabetical list) RomanHankaku Purpose Converts from Zenkaku alphanumeric and symbols to Hankaku alphanumeric and symbols.
Format RomanZenkaku(text) Parameters text - any text expression or text field Data type returned text Originated in FileMaker Pro 6.0 or earlier Examples RomanZenkaku(“Macintosh”) returns M a c i n t o s h. 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.
If the incrementBy value is a decimal number, then only the integer portion of incrementBy value is added to the last number in text. Any character other than a number is considered a separator. You can use both positive and negative incrementBy values. Examples SerialIncrement(“abc12”;1) returns abc13. SerialIncrement(“abc12”;7) returns abc19. SerialIncrement(“abc12”;-1) returns abc11. SerialIncrement(“abc12”;1.2) returns abc13. SerialIncrement(“abc1.2”;1.2) returns abc1.3.
FileMaker supports up to 999 nested substitute conditions. Each search and replace list item is also separated by semicolons. For example: Substitute(text; [search1; replace1]; [search2; replace2]; ... [searchN; replaceN]) Examples Substitute(Description;“WYSIWYG.”;“What you see is what you get.”) replaces every occurrence of the acronym “WYSIWYG.” in the Description field with the phrase What you see is what you get. Substitute(text;[“a”;“A”];[“b”;“B”]) replaces every lowercase a or b with A or B.
TrimAll Purpose Returns a copy of text with specified spaces removed or inserted. Use to work with spaces between text or non-Roman spaces such as full- and half-width spaces; otherwise, use Trim. Format TrimAll(text;trimSpaces;trimType) Parameters text - any text expression or text field trimSpaces - 0 or False, 1 or True trimType - 0 through 3 depending on the trim style that you wish to use Data type returned text Originated in FileMaker Pro 6.
This trimType value Does this 1 Always includes a half-width space between non-Roman and Roman characters (always leave one space between Roman words). 2 Removes spaces between non-Roman characters (reduce multiple space between non-Roman and Roman words to 1 space; do not add spaces if there are none; always leave one space between Roman words). 3 Removes all spaces everywhere. In all cases, spaces between non-Roman characters are removed.
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. Upper(“12n34p”) returns 12N34P. Related topics Contents Functions reference (alphabetical list) ValueCount Purpose Returns a count of the total number of values in text.
Examples ValueCount(“Item 1¶Item 2¶Item 3”) returns 3. ValueCount(ValueListItems(“Employees”;“Employee Names”)) returns the total number of values in the Employee Names value list in the Employees database file. Related topics Contents Functions reference (alphabetical list) WordCount Purpose Returns a count of the total number of words in text. Format WordCount(text) Parameters text - any text expression or text field Data type returned number Originated in FileMaker Pro 6.
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 appearance 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.
Data type returned text 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.
Data type returned text 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.
Originated in FileMaker Pro 7.0 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.
Format TextFontRemove(text{;fontToRemove;fontScript}) 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.
In the following example, to find every occurrence of several words and change their style, use the Substitute function combined with the TextStyleAdd function. 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.
• Superscript • Subscript • 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 hour portion (0-23) of a specified time value. Minute A number representing the minute portion (0-59) of a specified time value. Seconds A number representing the seconds portion (0-59) of a specified time value. Time A time result with the specified number of hours, minutes, and seconds.
Minute Purpose Returns a number representing the minute portion (0-59) of a specified 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.
Examples Seconds(“12:15:23”) returns 23. Note If no seconds value is specified, 0 is returned. Related topics Contents Functions reference (alphabetical list) Time Purpose Returns a time result with the specified number of hours, minutes, and seconds. Format Time(hours;minutes;seconds) Parameters hours - the hour value of a time minutes - the minutes value of a time seconds - the seconds value of a time Data type returned time Originated in FileMaker Pro 6.
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.
