FileMaker 8.
© 2006 FileMaker, Inc. All Rights Reserved. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries, and ScriptMaker and the file folder logo are trademarks of FileMaker, Inc. All persons and companies listed in the examples are purely fictitious and any resemblance to existing persons and companies is purely coincidental. FileMaker documentation is copyrighted.
Contents Chapter 1 Introduction About functions Using this functions reference Functions reference (alphabetical list) Chapter 2 Aggregate functions Average Count List Max Min StDev StDevP Sum Variance VarianceP Chapter 3 Date functions Date Day DayName DayNameJ DayOfWeek DayOfYear Month MonthName MonthNameJ WeekOfYear WeekOfYearFiscal Year YearName Chapter 4 Design functions DatabaseNames FieldBounds FieldComment 11 11 11 12 17 18 19 20 21 22 23 25 26 27 28 31 32 32 33 34 34 35 35 36 36 37 37 38 39
FileMaker Functions Reference FieldIDs FieldNames FieldRepetitions FieldStyle FieldType GetNextSerialValue LayoutIDs LayoutNames LayoutObjectNames RelationInfo ScriptIDs ScriptNames TableIDs TableNames ValueListIDs ValueListItems ValueListNames WindowNames Chapter 5 External functions External Chapter 6 Financial functions FV NPV PMT PV Chapter 7 Get functions Get(AccountName) Get(ActiveFieldContents) Get(ActiveFieldName) Get(ActiveFieldTableName) Get(ActiveLayoutObjectName) Get(ActiveModifierKeys) G
| Get(AllowToolbarState) Get(ApplicationLanguage) Get(ApplicationVersion) Get(CalculationRepetitionNumber) Get(CurrentDate) Get(CurrentHostTimestamp) Get(CurrentTime) Get(CurrentTimestamp) Get(CustomMenuSetName) Get(DesktopPath) Get(DocumentsPath) Get(ErrorCaptureState) Get(ExtendedPrivileges) Get(FileMakerPath) Get(FileName) Get(FilePath) Get(FileSize) Get(FoundCount) Get(HighContrastColor) Get(HighContrastState) Get(HostIPAddress) Get(HostName) Get(LastError) Get(LastMessageChoice) Get(LastODBCError) Get
FileMaker Functions Reference Get(RecordOpenCount) Get(RecordOpenState) Get(RequestCount) Get(RequestOmitState) Get(ScreenDepth) Get(ScreenHeight) Get(ScreenWidth) Get(ScriptName) Get(ScriptParameter) Get(ScriptResult) Get(SortState) Get(StatusAreaState) Get(SystemDrive) Get(SystemIPAddress) Get(SystemLanguage) Get(SystemNICAddress) Get(SystemPlatform) Get(SystemVersion) Get(TextRulerVisible) Get(TotalRecordCount) Get(UserCount) Get(UserName) Get(UseSystemFormatsState) Get(WindowContentHeight) Get(Windo
| GetAsBoolean GetField GetLayoutObjectAttribute GetNthRecord If IsEmpty IsValid IsValidExpression Let Lookup LookupNext Chapter 9 Number functions Abs Ceiling Combination Div Exp Factorial Floor Int Lg Ln Log Mod Random Round SetPrecision Sign Sqrt Truncate Chapter 10 Repeating functions Extend GetRepetition Last Chapter 11 Summary functions GetSummary Contents 131 132 133 134 135 136 137 137 138 140 141 143 144 144 145 145 146 146 147 147 148 149 149 150 150 151 151 152 153 153 155 156 156 157 15
FileMaker Functions Reference Chapter 12 Text functions Exact Filter FilterValues GetAsCSS GetAsDate GetAsNumber GetAsSVG GetAsText GetAsTime GetAsTimestamp GetAsURLEncoded GetValue Hiragana KanaHankaku KanaZenkaku KanjiNumeral Katakana Left LeftValues LeftWords Length Lower Middle MiddleValues MiddleWords NumToJText PatternCount Position Proper Quote Replace Right RightValues RightWords RomanHankaku RomanZenkaku SerialIncrement Substitute Trim TrimAll 161 163 164 164 165 166 166 167 168 168 169 169 17
| Upper ValueCount WordCount Chapter 13 Text formatting functions RGB TextColor TextColorRemove TextFont TextFontRemove TextFormatRemove TextSize TextSizeRemove TextStyleAdd TextStyleRemove Chapter 14 Time functions Hour Minute Seconds Time Chapter 15 Timestamp functions Timestamp Chapter 16 Trigonometric functions Contents 188 189 189 191 192 192 193 194 195 196 197 197 198 199 201 202 202 203 203 205 206 Atan Cos Degrees Pi Radians Sin Tan 207 208 208 209 209 210 210 211 Appendix A Glossary
FileMaker Functions Reference
Chapter 1 Introduction About 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: 1 1 1 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. FileMaker® Pro provides many functions for you to use in your FileMaker database solutions.
FileMaker Functions Reference Functions reference (alphabetical list) This section lists the functions in alphabetical order.
Chapter 1 Get(ActiveRepetitionNumber) Get(ActiveSelectionSize) Get(ActiveSelectionStart) Get(AllowAbortState) Get(AllowToolbarState) Get(ApplicationLanguage) Get(ApplicationVersion) Get(CalculationRepetitionNumber) Get(CurrentDate) Get(CurrentHostTimestamp) Get(CurrentTime) Get(CurrentTimestamp) Get(CustomMenuSetName) Get(DesktopPath) Get(DocumentsPath) Get(ErrorCaptureState) Get(ExtendedPrivileges) Get(FileMakerPath) Get(FileName) Get(FilePath) Get(FileSize) Get(FoundCount) Get(HighContrastColor) Get(High
FileMaker Functions Reference Get(ScreenWidth) Get(ScriptName) Get(ScriptParameter) Get(ScriptResult) Get(SortState) Get(StatusAreaState) Get(SystemDrive) Get(SystemIPAddress) Get(SystemLanguage) Get(SystemNICAddress) Get(SystemPlatform) Get(SystemVersion) Get(TextRulerVisible) Get(TotalRecordCount) Get(UserCount) Get(UserName) Get(UseSystemFormatsState) Get(WindowContentHeight) Get(WindowContentWidth) Get(WindowDesktopHeight) Get(WindowDesktopWidth) Get(WindowHeight) Get(WindowLeft) Get(WindowMode) Ge
Chapter 1 IsValidExpression KanaHankaku KanaZenkaku KanjiNumeral Katakana 137 171 172 172 172 L, M, N, O Last LayoutIDs LayoutNames LayoutObjectNames Left LeftValues LeftWords Length Let Lg List Ln Log Lookup LookupNext Lower Max Middle MiddleValues MiddleWords Min Minute Mod Month MonthName MonthNameJ NPV NumToJText 157 49 50 50 173 173 174 175 138 148 20 149 149 140 141 175 21 176 176 177 22 202 150 35 36 36 60 178 P, Q PatternCount Pi PMT Position Proper PV Quote 179 209 61 179 180 62 181 R Radian
FileMaker Functions Reference RomanHankaku RomanZenkaku Round 184 184 151 S ScriptIDs ScriptNames Seconds SerialIncrement SetPrecision Sign Sin Sqrt StDev StDevP Substitute Sum 52 53 203 185 151 152 210 153 23 25 186 26 T, U TableIDs TableNames Tan TextColor TextColorRemove TextFont TextFontRemove TextFormatRemove TextSize TextSizeRemove TextStyleAdd TextStyleRemove Time Timestamp Trim TrimAll Truncate Upper 53 54 211 192 193 194 195 196 197 197 198 199 203 206 186 187 153 188 V, W, X, Y, Z Value
Chapter 2 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. The parameter values can include a numeric constant (for example, 10) or any valid expression.
FileMaker Functions Reference Average Format Average(field{;field...}) Parameter 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 Description Returns a value that is the average of all valid, non-blank values in field, where field can be any of the following: • a repeating field (repeatingField).
Chapter 2 | Aggregate functions 19 Note When a referenced field is a repeating field, the Average function returns the average of the values in the first repetition field, then the average of the values in the second repetition field, and so on. Therefore, (1+5+6)/3=4;(2+6)/2=4;7/1=7;8/1=8. Count Format Count(field{;field...}) Parameter 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.
FileMaker Functions Reference Count(Field1;Field2;Field3) returns 3, 2, 1,1 when the calculation is a repeating field. Note When a referenced field is a repeating field, the Count function returns the total number of valid, non-blank values in the first repetition field, then the number of valid, nonblank values in the second repetition field, and so on. List Format List(field{;field...
Chapter 2 | Aggregate functions 21 Example 1 List (Field1; Field2) returns: white black Example 2 List(Field3) returns: red green blue Example 3 List (Field1; Field2; Field3) returns: in calculation repetition 1: white black red in calculation repetition 2: green in calculation repetition 3: blue Example 4 List(Related::Field4) returns: 100 200 300 Max Format Max(field{;field...
FileMaker Functions Reference Data type returned text, number, date, time, timestamp Description Returns the highest valid value in: • 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...).
Chapter 2 | Aggregate functions 23 Parameters in curly braces { } are optional. Data type returned text, number, date, time, timestamp Description Returns the smallest valid non-blank value in: • 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...).
FileMaker Functions Reference Parameter 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 Description Returns the standard deviation of the sample represented by a series of non-blank values in: • a repeating field (repeatingField).
Chapter 2 | Aggregate functions 25 StDevP Format StDevP(field{;field... }) Parameter 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 Description Returns the standard deviation of a population represented by a series of non-blank values in: • a repeating field (repeatingField).
FileMaker Functions Reference StDevP(Field2) returns 1.11803398... for a non-repeating field. StDevP(Field1;Field2;Field3) returns 2.16024689..., 2.49443825..., 1.5, 2 for repeating fields. Note When a referenced field is a repeating field, the StDevP function returns the standard deviation of a population in the first repetition fields, then the standard deviation of a population in the second repetition fields, and so on. Sum Format Sum(field{;field...
Chapter 2 | Aggregate functions 27 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. 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.
• FileMaker Functions Reference 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....
Chapter 2 | Aggregate functions 29 Description Returns the variance of a population represented by a series of non-blank values. The variance of a population distribution is a measure of how spread out the distribution is. Use this function on any of the following fields: • 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...).
FileMaker Functions Reference
Chapter 3 | Date functions 31 Chapter 3 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 FileMaker Pro help. Note System formats affect the way dates are displayed. See FileMaker Pro help. Click a function name for details. This function Returns Date, page 32 The calendar date for the specified month, day, and year.
FileMaker Functions Reference Date Format Date(month;day;year) Parameters month - the month of the year (a number from 1 to 12). day - the day of the month (a number from 1 to 31). year - the year (four digits between 0001 and 4000. For example, 2005 but not 05). 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.
Chapter 3 | Date functions 33 Data type returned number Description Returns a number in the range 1 through 31, representing the day of the month on which date occurs. For example, you can identify the day of the month that 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 FileMaker Pro help. Examples Day(“5/15/2004”) returns 15.
FileMaker Functions Reference DayNameJ Format DayNameJ(date) Parameter date - any calendar date Data type returned text Description Returns a text string in Japanese that is the full name of the weekday for date. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see FileMaker Pro help.
Chapter 3 | Date functions 35 DayOfYear Format DayOfYear(date) Parameter date - any calendar date Data type returned number Description Returns a number equal to the number of days from the beginning of the year of date. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see FileMaker Pro help. Examples DayOfYear(Billing Date) returns 33, when Billing Date is 2/2/2005.
FileMaker Functions Reference Examples Month(“3/19/2004”) 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 16, 2004. (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.
Chapter 3 | Date functions Data type returned text Description Returns the name of the month of date in Japanese. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see FileMaker Pro help. Example MonthNameJ(“6/6/2003”) returns WeekOfYear Format WeekOfYear(date) Parameter date - any calendar date Data type returned number Description Returns the number of weeks after January 1 of the year of date.
FileMaker Functions Reference Data type returned number Description Returns a number between 1 and 53 representing the week containing date, figured according to startingDay. 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.
Chapter 3 | Date functions 39 Examples Year(DateSold) returns the year stored in DateSold. Year(“5/5/2004”) returns 2004. Year(Date(Month(Get(CurrentDate)) + 48;Day(Get(CurrentDate));Year(Get(CurrentDate)))) returns the year that is 48 months from today’s date.
FileMaker Functions Reference
Chapter 4 | Design functions 41 Chapter 4 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 FileMaker Pro help for more information about granting access to database files.
FileMaker Functions Reference This function Returns LayoutNames, page 50 A list of the names of all layouts in the specified database file, separated by carriage returns. LayoutObjectNames, A list of the names of all named layout objects, separated by carriage returns. page 50 RelationInfo, page 51 A list of four values for each relationship directly related to the specified table. ScriptIDs, page 52 A list of all script IDs in the specified database file, separated by carriage returns.
Chapter 4 | Design functions 43 DatabaseNames Format DatabaseNames Parameter None Data type returned text Description Returns a list of the names of all database files open on the computer, separated by carriage returns. The names do not include file extensions. Note If your database is hosted on another computer, DatabaseNames returns a list of the names of local client and remote database files open only on the client computer.
FileMaker Functions Reference Description Returns in a non-repeating text field the location in pixels of each side of fieldName and its rotation in degrees on layoutName in the fileName file.
Chapter 4 | Design functions 45 Description Returns the specified field’s comment. The field name must be in the form tablename::fieldname to specify a field that exists in a table different from the current table. Examples FieldComment("Customers"; "Phone Number") returns “Customer's home telephone number” if it was entered as a comment for the Phone Number field.
FileMaker Functions Reference 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” on page 41 for information about literal text parameters. Data type returned text Description Returns a list of the names of all fields on layoutName, in fileName file, separated by carriage returns. Related fields are displayed in tablename::fieldname format.
Chapter 4 | Design functions 47 Description 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. If fieldName isn’t a repeating field, it returns 1 vertical.
FileMaker Functions Reference Example On the Data Entry layout in the Customers database file, FieldStyle(“Customers”;“Data Entry”;“Current Customer”) returns RadioButton Yes/No List when the Current Customer field is formatted as a radio button and is associated with the value list named Yes/No List. FieldType Format FieldType(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.
Chapter 4 | Design functions 49 GetNextSerialValue 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” on page 41 for information about literal text parameters. Data type returned text Description Returns the next serial number of fieldName in fileName.
FileMaker Functions Reference LayoutNames Format LayoutNames(fileName) Parameter fileName - the name of an open database file (local or remote). Important See “Design functions” on page 41 for information about literal text parameters. Data type returned text Description Returns a list of the names of all layouts in fileName, separated by carriage returns. Example LayoutNames(“Customers”) returns a list of all the layouts in the Customers database file.
Chapter 4 | Design functions 51 Example LayoutObjectNames ("Customers";"Data Entry") returns a list of named objects in the Customers database file that appear on the Data Entry layout. RelationInfo 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 FileMaker Pro Help for information about literal text parameters.
FileMaker Functions Reference Example A database file called Human Resources has three tables: Company, Employees, and Addresses. Company::Company ID is connected to Employees::Company ID, Employees::Employee ID is connected to Addresses::Employee ID and Employees::DateOfHire is connected to Addresses::DateMovedIn. The relationships have the following criteria: • You can create records in all tables. • You cannot delete records in all tables.
Chapter 4 | Design functions 53 Example ScriptIDs(“Customers”) returns a list of all the script IDs in the Customers database file. ScriptNames Format ScriptNames(fileName) Parameter fileName - the name of an open database file (local or remote). Important See “Design functions” on page 41 for information about literal text parameters. Data type returned text Description Returns a list of the names of all scripts in fileName, separated by carriage returns.
FileMaker Functions Reference Example TableIDs(“University Database”) returns 1065089 1065090 for the University Database database file if two tables have been defined for the file. TableNames Format TableNames(fileName) Parameter fileName - the name of an open database file (local or remote). Important See “Design functions” on page 41 for information about literal text parameters.
Chapter 4 | Design functions 55 Data type returned text Description Returns a list of all value list IDs in fileName, separated by carriage returns. Example ValueListIDs(“Customers”) returns a list of all the value list IDs in the Customers database file. ValueListItems 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.
FileMaker Functions Reference Description Returns a list of the names of all value lists in fileName, separated by carriage returns. Example ValueListNames(“Customers”) returns a list of all the value list names in the Customers database file. WindowNames Format WindowNames{(fileName)} Parameter {fileName} - the name of an open database file (local or remote). Parameters in curly braces { } are optional.
Chapter 5 | External functions 57 Chapter 5 External functions Use external functions to access FileMaker Pro plug-ins. Plug-ins add features to FileMaker Pro. For more information, see FileMaker Pro help. External functions are only available if FileMaker Pro plug-ins are installed and enabled on your computer.
FileMaker Functions Reference External Format External(nameOfFunction;parameter) Parameters nameOfFunction - the name of the external function parameter - the parameter(s) required by the external function. A parameter is required, even if it’s only 0. Data type returned Depends on the external function Description The External function accesses plug-ins created for versions of FileMaker Pro prior to 7.
Chapter 6 | Financial functions 59 Chapter 6 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, page 60 The future value of an initial investment, based on a constant interest rate and payment amount for the number of periods in months.
FileMaker Functions Reference FV Format FV(payment;interestRate;periods) Parameters payment - payment to be made per period interestRate - interest rate per period periods - number of periods Data type returned number Description Returns the future value of an initial investment, based on a constant interestRate and payment amount for the number of periods in months.
Chapter 6 | Financial functions 61 interestRate - interest rate. Data type returned number Description Returns the net present value of a series of unequal payments made at regular intervals, assuming a fixed interestRate per interval. For example, suppose someone borrows money from you and pays you back in unequal amounts over a period of several years. You can calculate the result using the NPV function.
FileMaker Functions Reference Examples In the following example, the PMT function calculates payments for purchasing a sports car costing $21,000, at an annual rate of 6.9% over 48 monthly payments. PMT(21000;.069/12;48) returns the payment amount $501.90. PMT(Cost;.13;Years) returns a payment amount, based on the purchase value stored in Cost, at a 13 percent rate, over the duration stored in Years. “Your payment will be “ & PMT(150000;.13/12;Months) & “.
Chapter 7 | Get functions 63 Chapter 7 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.
FileMaker Functions Reference This function Returns Get(ApplicationVersion), page 75 Text representing the FileMaker Pro application version. Get(CalculationRepetitionNumber), page 75 A number representing the repetition of the calculation field that is currently being calculated. Get(CurrentDate), page 76 The current date according to the system calendar. Get(CurrentHostTimestamp), page 76 The host's current date and time (to the nearest second) according to the system clock.
Chapter 7 | Get functions 65 This function Returns Get(LayoutTableName), page 97 The name of the table that the layout is displaying records from. Get(LayoutViewState), page 97 Information about how the database file is being viewed. Get(MultiUserState), page 98 A number representing the current multi-user state of the database file. Get(NetworkProtocol), page 99 The name of the network protocol that FileMaker Pro is using on this machine.
FileMaker Functions Reference This function Returns Get(SystemPlatform), page 115 A number indicating the current platform. Get(SystemVersion), page 116 The version of the operating system of the machine on which the function is executed. Get(TextRulerVisible), page 116 A Boolean value representing whether or not the text ruler is visible. Get(TotalRecordCount), page 117 The total number of records in the current table.
Chapter 7 | Get functions 67 Get functions example This script uses the function Get(CurrentDate) to check each record in the found set to see if an account is past due. If an account is past due, the script shows a message and prompts the user to click a button labeled Ignore, Send letter, or Send mail (set up through the Show Custom Dialog script step). The script captures the user's response using Get(LastMessageChoice).
FileMaker Functions Reference Get(AccountName) Format Get(AccountName) Parameter None Data type returned text Description For FileMaker authentication, Get(AccountName) returns the name of the authenticated account being used by the current user of the database file. If a user is using the default Admin account, Get(AccountName) returns Admin. If a user is using the FileMaker Pro guest account then [Guest] will be returned.
Chapter 7 | Get functions 69 Data type returned text, number, date, time, timestamp, container Description Returns the contents of the field containing the cursor. When the cursor is in a repeating field, returns the contents of the active repetition. The result type of the active field depends upon the data type of the active field and the result type assigned to the Get(ActiveFieldContents) calculation function.
FileMaker Functions Reference Parameter None Data type returned text Description Returns the name of the table that contains the active field (the field that currently contains the cursor). If there is no active field, an empty string is returned. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example There are two fields, Teachers::Name and Coaches::Name, on the current layout.
Chapter 7 | Get functions 71 Get(ActiveModifierKeys) Format Get(ActiveModifierKeys) Parameter None Data type returned number Description Returns a number representing the keyboard modifier keys (for example, Control+Shift) that are being pressed. The number is calculated by summing numbers representing each modifier key being pressed.
FileMaker Functions Reference Description Returns a number representing the active repetition of a repeating field (the repetition that currently contains the cursor). The first repetition is 1. If the current field isn’t a repeating field, the function returns 1. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 5 when the cursor is in the fifth repetition of a repeating field.
Chapter 7 | Get functions 73 Description Returns a number representing the starting character of the selected text. Returns the cursor's current position if no text is selected. If there are multiple windows open in the current database file, a result is returned for only the foreground window. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 5 when the selection starts at character 5.
FileMaker Functions Reference Description Returns a Boolean value representing whether toolbars are allowed to be visible. Returns 1 if toolbars are allowed, otherwise returns 0. The Allow Toolbars script step sets the toolbar state. For more information, see FileMaker Pro help. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 1 if toolbars are allowed to be visible.
Chapter 7 | Get functions 75 Get(ApplicationVersion) Format Get(ApplicationVersion) Parameter None Data type returned text Description Returns text representing the FileMaker application and version. • Pro (version) for FileMaker Pro. • ProAdvanced (version) for FileMaker Pro Advanced. • Runtime (version) for FileMaker Runtime. • Web (version) for FileMaker Web Client. • Server (version) for FileMaker Web Server.
FileMaker Functions Reference Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 5 when FileMaker Pro is calculating the fifth repetition of a repeating field. Get(CurrentDate) Format Get(CurrentDate) Parameter None Data type returned date Description Returns the current date according to the system calendar. The format of the result varies based on the date format that was in use when the database file was created.
Chapter 7 | Get functions 77 Data type returned timestamp Description Returns the host’s current date and time according to the system clock, to the nearest second. The format of the value returned is determined by the database file’s settings. You can use your client system’s settings by editing the Regional Settings Control Panel (Windows 2000), the Date and Time Control Panel (Windows XP), or the Date & Time System Preference (Mac OS).
FileMaker Functions Reference Description Returns the current time according to the system clock, to the nearest second. The format of the value returned is determined by the Regional Settings Control Panel (Windows 2000), the Date and Time Control Panel (Windows XP), or the Date & Time System Preference (Mac OS). Note In client/server and peer-to-peer environments, Get(CurrentTimestamp) evaluates the status of the client machine running the script (not the host machine).
Chapter 7 | Get functions 79 Parameter None Data type returned text Description Returns the name of the active custom menu set. If the active menu set isn’t a custom menu set, an empty string is returned. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Examples Returns Custom Menu Set #1 when this custom menu set is active. Returns an empty string when the [Standard FileMaker Menus] menu set is active.
FileMaker Functions Reference Get(DocumentsPath) Format Get(DocumentsPath) Parameter None Data type returned text Description Returns the path to the documents folder for the current user. In Windows, the path format is /Drive:/Documents and Settings/UserName/My Documents/. In the Mac OS, the path format is /DriveName/Users/UserName/Documents/. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Chapter 7 | Get functions 81 Get(ExtendedPrivileges) Format Get(ExtendedPrivileges) Parameter None Data type returned text Description Returns a list of keywords, separated by carriage returns, identifying the extended privileges available to the account being used by the current user of the database file (extended privileges are additional access rights assigned to an account’s privilege set). For more information, see FileMaker Pro help.
FileMaker Functions Reference Description Returns the path to the folder of the currently running copy of FileMaker Pro. In Windows, the path format is /Drive:/Program Files/FileMaker/FileMaker Pro 8.5/. In the Mac OS, the path format is /DriveName/Applications/FileMaker Pro 8.5/. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Examples Returns /C:/Program Files/FileMaker/FileMaker Pro 8.5/ in Windows.
Chapter 7 | Get functions 83 Parameter None Data type returned text Description Returns the full path indicating the location of the currently active database file. 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.
FileMaker Functions Reference 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. • See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 15000 when the current file size is 15000 bytes.
Chapter 7 | Get functions 85 Parameter None Data type returned text Description Windows: returns the name of the current high contrast default color scheme. Returns an empty value (null) if Use High Contrast is unavailable, inactive, or if the function is used on the Mac OS. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
FileMaker Functions Reference Parameter None Data type returned text Description Returns the IP address of the host machine for the current database. If the current database isn’t being hosted, an empty string is returned. 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.
Chapter 7 | Get functions 87 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. • See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns Fred Jones when Fred Jones is the registered name of the host computer in use.
FileMaker Functions Reference Runtime errors Error Number Description -1 Unknown error 0 No error 1 User canceled action 2 Memory error 3 Command is unavailable (for example, wrong operating system, wrong mode, etc.
Chapter 7 | Get functions 89 Error Number Description 115 Specified menu set is not present 116 Specified layout object is not present 130 Files are damaged or missing and must be reinstalled 131 Language pack files are missing (such as template files) 200 Record access is denied 201 Field cannot be modified 202 Field access is denied 203 No records in file to print, or password doesn't allow print access 204 No access to field(s) in sort order 205 User does not have access privileg
FileMaker Functions Reference Error Number Description 409 Import order is invalid 410 Export order is invalid 412 Wrong version of FileMaker Pro used to recover file 413 Specified field has inappropriate field type 414 Layout cannot display the result 415 One or more required related records are not available 500 Date value does not meet validation entry options 501 Time value does not meet validation entry options 502 Number value does not meet validation entry options 503 Value
Chapter 7 | Get functions Error Number Description 723 You do not have permission to add records to or modify records in the target table 724 You do not have permission to add records to the target table 725 You do not have permission to modify records in the target table 726 There are more records in the import file than in the target table. Not all records were imported 727 There are more records in the target table than in the import file.
FileMaker Functions Reference Error Number Description 900 General spelling engine error 901 Main spelling dictionary not installed 902 Could not launch the Help system 903 Command cannot be used in a shared file 905 No active field selected; command can only be used if there is an active field 920 Can’t initialize the spelling engine 921 User dictionary cannot be loaded for editing 922 User dictionary cannot be found 923 User dictionary is read-only 951 An unexpected error occurre
Chapter 7 | Get functions Error Number Description 1223 The function is not implemented 1224 The function is not defined 1300 The specified name can’t be used 1400 ODBC driver initialization failed; make sure the ODBC drivers are properly installed 1401 Failed to allocate environment (ODBC) 1402 Failed to free environment (ODBC) 1403 Failed to disconnect (ODBC) 1404 Failed to allocate connection (ODBC) 1405 Failed to free connection (ODBC) 1406 Failed check for SQL API (ODBC) 1407
FileMaker Functions Reference Get(LastODBCError) Format Get(LastODBCError) Parameter None Data type returned text Description Returns a string that shows the ODBC error state (SQLSTATE), as published by ODBC standards, based on ISO/IEF standards. You can obtain the error state after an ODBC-related script step has been executed to check for known errors and determine if you want to continue with the script. The ODBC error state is cleared before the next ODBC-related script is executed.
Chapter 7 | Get functions 95 Description Returns a number based on record access privileges available through the current layout. 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.
FileMaker Functions Reference Example Returns 3 when the file has three layouts. Get(LayoutName) Format Get(LayoutName) Parameter None Data type returned text Description Returns the name of the layout currently displayed. If there are multiple windows open in the current database file, each window can have its own layout name value, but results are returned for only the foreground window. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Chapter 7 | Get functions 97 Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 3 when the current layout is third in the list of layouts in Set Layout Order. Get(LayoutTableName) Format Get(LayoutTableName) Parameter None Data type returned text Description Returns the name of the table from which the current layout is displaying records. If no windows are open, an empty string is returned.
FileMaker Functions Reference • 0 (zero) if the database file is in View as Form view • 1 if the database file is in View as List view • 2 if the database file is in View as 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 See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Chapter 7 | Get functions 99 Get(NetworkProtocol) Format Get(NetworkProtocol) Parameter None Data type returned text Description Returns the name of the network protocol (TCP/IP) that FileMaker Pro is using on this machine. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns TCP/IP.
FileMaker Functions Reference Get(PortalRowNumber) Format Get(PortalRowNumber) Parameter None Data type returned number Description Returns the number of the currently selected portal row. When no portal row is selected, 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.
Chapter 7 | Get functions 101 Examples Returns /C:/Documents and Settings/John Smith/Local Settings/Application Data/ for a user named John Smith in Windows. Returns /MacintoshHD/Users/John Smith/Library/Preferences/ for a user named John Smith in the Mac OS.
FileMaker Functions Reference Get(PrivilegeSetName) Format Get(PrivilegeSetName) Parameter None Data type returned text Description Returns the name of the privilege set assigned to the account being used by the current user of the database file. If a user is using the default Admin account and you haven’t modified access privileges for the database file, Get(PrivilegeSetName) returns [Full Access].
Chapter 7 | Get functions 103 Description Returns a number based on the current record’s access privileges, assigned through the Custom Record Privileges dialog box. Returns: • 0 if the custom record privileges of an account’s privilege set have neither View nor Edit privileges set to yes for the current record.
FileMaker Functions Reference Description Returns the unique ID number of the current record. This number 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.
Chapter 7 | Get functions 105 Example Returns 0 if the record has not been modified since it was created. If changes are made to four fields and all four fields are committed together, the result increments by one. If changes are made to four fields and each change is committed separately, the result increments by four. Get(RecordNumber) Format Get(RecordNumber) Parameter None Data type returned number Description Returns the number of the current record in the current found set.
FileMaker Functions Reference Data type returned number Description Returns the total number of open records in the current found set that haven’t been saved. 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. • See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Chapter 7 | Get functions 107 Example Returns 1 if the current record is a new record that hasn’t been saved. Get(RequestCount) Format Get(RequestCount) Parameter None Data type returned number Description Returns the total number of find requests defined for the current table. If there are multiple windows open in the current database file, then results are returned for only the top-most window of the file that the calculation is defined in.
FileMaker Functions Reference Example Returns 1 when the Omit checkbox is selected in the current find request. Get(ScreenDepth) Format Get(ScreenDepth) Parameter None Data type returned number Description Returns the number of bits needed to represent the color or shade of gray of a pixel on the main screen. A value of 8 represents 256 (equal to 28) colors or shades of gray. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Chapter 7 | Get functions 109 Description Returns the number of pixels displayed vertically on the screen in which the window of the current file is open. When the window spans more than one screen, this function uses the screen that contains the largest percentage of the window. If there are multiple windows open in the current database file, each window can have its own screen height value, but results are returned for only the foreground window.
FileMaker Functions Reference Data type returned text Description Returns the name of the script currently running (or paused). Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Examples Returns Print Report when the Print Report script is running. Returns Update Customer when the Update Customer script is running.
Chapter 7 | Get functions 111 Evaluate("Let ( [" & Get(ScriptParameter) & "]; a + 1 & \", \" & b + 2 )" ) The following example shows how to pass named parameters, while keeping the ability to check the syntax of the second parameter of the Let function (the example returns 6, 12): ScriptParameter = "a = 5; b = 10" Let( [a = Evaluate("Let( [" & Get(ScriptParameter) & "]; a )"), b = Evaluate("Let( [" & Get(ScriptParameter) & "]; b )")]; a + 1 & ", " & b + 2 ) Get(ScriptResult) Format Get(ScriptResult)
FileMaker Functions Reference Get(SortState) Format Get(SortState) Parameter None Data type returned number Description Returns 0 if the records in the active table are not sorted. Returns 1 if the records in the active table are sorted. Returns 2 if the records in the active table are partially sorted (semi-sorted). Each window has its own sort state.
Chapter 7 | Get functions 113 Description Returns a number indicating the current status area state. Returns: • 0 (zero) if the status area is hidden • 1 if the status area is visible • 2 if the status area is visible and locked • 3 if the status area is hidden and locked If there are multiple windows open on the currently active database file, then results are returned for only the active window.
FileMaker Functions Reference Get(SystemIPAddress) Format Get(SystemIPAddress) Parameter None Data type returned text Description Returns a list of the IP addresses of all the machines connected to a NIC (Network Interface Controller) card. IP addresses are separated by carriage returns. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 14.156.13.121, for example, when only one machine is connected.
Chapter 7 | Get functions 115 Get(SystemNICAddress) Format Get(SystemNICAddress) Parameter None Data type returned text Description Returns the hardware address of all the Network Interface Controller cards connected to the machine. 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.
FileMaker Functions Reference Examples Abs(Get(SystemPlatform)) returns 2 when the current platform is a Windows platform. Get(SystemPlatform) returns -1 when the current platform is Mac OS X. Get(SystemVersion) Format Get(SystemVersion) Parameter None Data type returned text Description When this function is used in a script, returns the version of the operating system of the machine of the person running the script.
Chapter 7 | Get functions 117 Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Examples Returns 1 when the text ruler is visible. Get(TotalRecordCount) Format Get(TotalRecordCount) Parameter None Data type returned number Description Returns the total number of records in the current table.
FileMaker Functions Reference Description Returns the number of clients currently accessing the file. Returns: • 1 if FileMaker network sharing is turned off • 1 + the number of clients if FileMaker network sharing is turned on This function does not count clients accessing the database file via the web, ODBC or JDBC.
Chapter 7 | Get functions 119 Get(UseSystemFormatsState) Format Get(UseSystemFormatsState) Parameter None Data type returned number Description Returns a Boolean value representing the state of the Use System Formats command in the Format menu. Returns 1 if Use System Formats is on, otherwise returns 0. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Examples Returns 1 when Use System Formats is on.
FileMaker Functions Reference The example below combines Get(WindowContentHeight) with Get(WindowHeight) to determine the size of the title bar and horizontal scroll bar: Get(WindowHeight) - Get(WindowContentHeight) returns 37 in the Mac OS when the window height is 437. Get(WindowContentWidth) Format Get(WindowContentWidth) Parameter None Data type returned number Description Returns a number representing the width, in pixels, of the FileMaker Pro content area.
Chapter 7 | Get functions 121 Description Returns a number representing the height, in pixels, of the desktop space. 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. In the Mac OS, the desktop space is the area on the main monitor excluding the menu bars and Dock. The main monitor is where the menu bar is located.
FileMaker Functions Reference Get(WindowHeight) Format Get(WindowHeight) Parameter None Data type returned number Description Returns a number representing the height, in pixels, of the window that the script is acting on (not necessarily the foreground window). 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.
Chapter 7 | Get functions 123 Examples Returns 52 when the outer edge of the active window is 52 pixels from the left edge of the screen. Returns 0 when the active window is 0 pixels from the left edge of the screen.
FileMaker Functions Reference Data type returned text Description Returns the name of the window that the script is acting on (not necessarily the foreground window). Returns an empty string if there is no window. Notes • You can set the window name with the Set Window Title script step. • See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Chapter 7 | Get functions 125 Get(WindowVisible) Format Get(WindowVisible) Parameter None Data type returned number Description Returns a number representing whether or not the current window is visible. The current window is the window that the script is acting on (not necessarily the foreground window). Returns a 1 if the window is visible. Returns a 0 if the window is hidden using the Hide Window command. The window can be located outside of the visible screen space and still return 1.
FileMaker Functions Reference Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 300 when the current window is 300 pixels wide. Get(WindowZoomLevel) Format Get(WindowZoomLevel) Parameter None Data type returned text Description Returns the zoom percentage of the current window.
Chapter 8 | Logical functions 127 Chapter 8 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.
FileMaker Functions Reference Case 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 Description Returns one of several possible results based on a series of tests.
Chapter 8 | Logical functions 129 Data type returned text, number, date, time, timestamp, container Description Returns one result value, according to the integer value of test. FileMaker Pro evaluates test to obtain an index number, which is used to choose the corresponding ordinal result. Because the Choose function is a 0 based list, the first item on the list is indexed 0 and the second item on the list is indexed 1. For example, if test evaluates to 2, then result2 is chosen.
FileMaker Functions Reference Evaluate(GetField("textfield")) returns 4 when textfield contains 2 + 2. Evaluate(TextField;[Amount]) returns .80 when TextField contains .08 * Amount and the Amount field contains 10.00. Evaluate(“Let(TaxRate=.05;“& Tax Rate Calculation &“)”) returns .50 when the field Tax Rate Calculation contains SubTotal * TaxRate where SubTotal is a numeric field that contains 10.00.
Chapter 8 | Logical functions 131 The dependent parameter can also be useful in other cases. For example, Evaluate(“Get(CurrentTimeStamp)”, [FieldB, FieldC]) will store a timestamp in the calculation field whenever FieldB or FieldC changes. EvaluationError Format EvaluationError(expression) Parameter expression - any calculation expression Data type returned number Description Returns an error code, if any, from expression. There are two types of errors: syntax errors and runtime errors.
FileMaker Functions Reference Description Returns 0 if data has a value of 0 or is empty, all other values return 1. 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.
Chapter 8 | Logical functions 133 GetLayoutObjectAttribute Format GetLayoutObjectAttribute(objectName;attributeName{;repetition Number}) 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) Parameters in curly braces {} are optional.
FileMaker Functions Reference height - returns a number representing the height (in pixels) 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 will return the top left point for startPoint and the bottom right point for endPoint.
Chapter 8 | Logical functions 135 Parameters fieldName - any related field or repeating field, or an expression that returns a field or a repeating field. recordNumber - the record number from which you want data. Data type returned text, number, date, time, timestamp, container Description Returns the contents of fieldName from the provided recordNumber.
FileMaker Functions Reference Description Returns one of two possible results depending on the value of test. If test is True (any non-zero numeric result), FileMaker Pro returns result1. If test is False (0), result2 is returned. Test must be an expression that returns either a numeric or Boolean (True, False) result. Notes • If you have more than two possible results, consider using the Case function.
Chapter 8 | Logical functions 137 IsValid Format IsValid(field) Parameter field - any field name Data type returned number Description Returns False (0) when: • A record contains an invalid value because of a data type mismatch (text in a date field, for example) • FileMaker Pro cannot locate (temporarily or permanently) the related table in which the referenced field is defined • A field has been deleted from a related table, and therefore the references to that field in the parent table are inval
FileMaker Functions Reference Description Returns True (1) if expression syntax is correct. Returns False (0) if expression has a syntax error. Examples IsValidExpression(calculationField) returns 1 (true) when calculationField contains total + 1. IsValidExpression(calculationField) returns 0 (false) when calculationField contains abs(-1 with no closing parenthesis. Let Format Let({[}var1=expression1{;var2=expression2...
Chapter 8 | Logical functions 139 The Let function sets the variables from left to right. You can use previously defined variables (for example, variables that you defined with the Set Variable script step) to define new variable values, and you can nest one Let function within another. If you use a previously defined variable within a nested Let function, the variable has scope only within the nested function (as if you had defined a completely unique variable). See the City example shown below.
FileMaker Functions Reference Lookup 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 Description Returns the contents of sourceField, using the relationships in the relationships graph. The result of the optional failExpression will be returned if the lookup fails.
Chapter 8 | Logical functions 141 LookupNext Format LookupNext(sourceField;lower/higherFlag) Parameters sourceField - the field from which the lookup value is taken. lower/higherFlag - the keywords lower or higher denote whether the value from the next lower/higher matching record must be taken if the lookup fails. Data type returned text, number, date, time, timestamp, container Description Returns the value specified in sourceField using the relationships in the relationships graph.
FileMaker Functions Reference The People and Company tables are related using the number field CompanyID. The calculation CompanyName = LookupNext(Company::CompanyName;Higher) defined in the People table will return Apple, FileMaker, Motorola and Cisco for records 1 to 4.
Chapter 9 | Number functions 143 Chapter 9 Number functions Number functions are used to manipulate numeric data. Click a function name for details. This function Returns Abs, page 144 The absolute value (a positive number) of a number. Ceiling, page 144 A number rounded up to the next integer. Combination, page 145 The number of ways to uniquely choose a specified number of items from a set of a specified size. Div, page 145 An integer of the specified number divided by the divisor.
FileMaker Functions Reference Abs Format Abs(number) Parameter number - any numeric expression or field containing a numeric expression Data type returned number, time Description Returns the absolute value (a positive number) of number. 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.
Chapter 9 | Number functions 145 Combination 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 Description Returns the number of ways to uniquely choose numberOfChoices items from a set of size setSize. The values returned by this function are referred to as combination coefficients.
FileMaker Functions Reference Description Returns the next lowest integer value after dividing number by divisor. The Div function is equivalent to Floor(number/divisor). Examples Div(2.5;2) returns 1. Div(-2.5;2) returns -2. Exp Format Exp(number) Parameter number - any numeric expression or field containing a numeric expression Data type returned number Description Returns the value of the constant e (the base of the natural logarithm, equal to 2.7182818) raised to the power of number.
Chapter 9 | Number functions Data type returned number Description Returns the factorial of number stopping at 1, or stopping at the optional numberOfFactors. Useful in statistics and combinatorics. Where n = number and i = numberOfFactors: Factorial(n) = n ( n – 1 ) ( n – 2 )… ( 1 ) Factorial(n;i) = n ( n – 1 ) ( n – 2 )… ( n – i + 1 ) Examples Factorial(3) returns 6, which = 3 * 2 * 1. Factorial(10;3) returns 720, which = 10 * 9 * 8.
FileMaker Functions Reference Parameter number - any numeric expression or field containing a numeric expression Data type returned number Description Returns the whole number (integer) part of number without rounding. It drops any digits to the right of the decimal point, depending on the number you specify. Examples Int(1.45) returns 1. Int(123.987) returns 123. Int(Players/3) returns 4, if Players contains 13.
Chapter 9 | Number functions 149 Ln Format Ln(number) Parameter number - any numeric expression or field containing a numeric expression Data type returned number Description Returns the base-e (natural) logarithm of number. The Exp function is the inverse of the Ln function. Negative values return an error. For 0, the Ln function returns nothing because these values are out of the acceptable range. Examples Ln(2.7182818) returns .99999998.... Ln(Exp(5)) returns 5.
FileMaker Functions Reference Mod Format Mod(number;divisor) Parameters number - any numeric expression or field containing a numeric expression divisor - numeric expression or field containing a numeric expression Data type returned number Description Returns the remainder after number is divided by divisor. Use the Mod function to test whether a number is even or odd by specifying a divisor of 2 (if the result is zero the number is even, otherwise it’s odd).
Chapter 9 | Number functions 151 Description Returns a random number between zero and one. FileMaker Pro generates a new random number when you: • insert the Random function into a formula • cause a formula containing the Random function to be reevaluated (by changing data in any of the fields the formula uses) • display or access a calculation field defined to have an unstored result.
FileMaker Functions Reference Parameters expression - any numeric expression precision - any number or numeric expression Data type returned number Description Computes any math functions contained within expression that support extended precision to precision decimal places (up to 400). All functions except the trigonometric functions support extended precision. This function doesn’t perform a truncation: constant numbers are left at the precision in which they were entered.
Chapter 9 | Number functions 153 Sqrt Format Sqrt(number) Parameter number - any positive number, numeric expression, or field containing a numeric expression. Data type returned number Description Calculates the square root of number. Sqrt = number Examples Sqrt(4) returns 2. Sqrt(SquareFeet) returns 6 if the SquareFeet number field contains 36.
FileMaker Functions Reference Truncate(29343.98;-3) returns 29000. Truncate(123.456;4) returns 123.456. Truncate(29343.98;5) returns 29343.98.
Chapter 10 | Repeating functions 155 Chapter 10 Repeating functions Repeating functions perform calculations on repeating fields. Click a function name for details. This function Returns Extend, page 156 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, page 156 The contents of the specified repetition of a repeating field.
FileMaker Functions Reference Extend Format Extend(non-repeatingField) Parameter non-repeatingField - any non-repeating field (a field defined to contain only one value), or an expression that returns a reference to one. Data type returned text, number, date, time, timestamp, container Description Allows a value in non-repeatingField to be used with every repetition in a repeating field. Use the Extend function with calculations involving both repeating and nonrepeating fields.
Chapter 10 | Repeating functions 157 Description Returns the contents of the repeating field specified by number. Examples ParcelBids is a field defined to repeat with ten values and contains the values 2500, 1200, and 1500. GetRepetition(ParcelBids;2) returns 1200. GetRepetition(if(IsEmpty(ParcelBids) ≠ true, ParcelBids, HouseBids);2) returns 1200. GetRepetition(ParcelBids;5) returns nothing.
FileMaker Functions Reference Last(if(IsEmpty(Company);PersonalPhone;WorkPhone)) returns the last nonempty phone number from the repeating field PersonalPhone when the Company field is empty. If the Company field is not empty, the function returns the last non-empty phone number from the repeating field WorkPhone.
Chapter 11 | Summary functions 159 Chapter 11 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.
FileMaker Functions Reference GetSummary Format GetSummary(summaryField;breakField) Parameters summaryField - field of type summary, or an expression that returns a reference to one. breakField - field, or an expression that returns a reference to one. To calculate a grand summary value, use the same summary field for both the summary field and the break field parameters. The GetSummary function must be set up in the same table as the break field.
Chapter 12 | Text functions 161 Chapter 12 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 quotes) • expressions having a text result Click a function name for details.
FileMaker Functions Reference This function Returns KanjiNumeral, page 172 Kanji numerals converted from Arabic numerals. Katakana, page 172 Zenkaku Katakana converted from Hiragana. Left, page 173 The specified number of characters in the text, counting from the left. LeftValues, page 173 The specified number of values in the text, counting from the left. LeftWords, page 174 The specified number of words in the text, counting from the left.
Chapter 12 This function Returns ValueCount, page 189 A count of the total number of values in the specified text. WordCount, page 189 A count of the total number of words in the specified text. | Text functions 163 Exact Format Exact(originalText;comparisonText) Parameters originalText - any text expression, text field, or container field comparisonText - any text expression, text field, or container field Data type returned number Description Compares the contents of any two fields.
FileMaker Functions Reference Filter Format Filter(textToFilter;filterText) Parameters textToFilter - any text expression or text field filterText - the characters to preserve in the specified text Data type returned text Description Returns from textToFilter only those characters specified in filterText, in the order that they were originally entered in textToFilter. If filterText doesn’t have any characters, an empty string is returned. The Filter function is case-sensitive.
Chapter 12 | Text functions 165 Description Returns a text result containing only the characters that were provided in filterValues, in the order that they were originally entered in textToFilter. If filterValues doesn’t have any characters, an empty string is returned. 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 Enter or Return, you start creating a new value.
FileMaker Functions Reference Example GetAsCSS(text) returns the example result shown below when the field text contains the word “Frank” and the word Frank has the following text attributes: Font = Helvetica, Font Size = 12 points, Font Color = red, Font Style = bold.
Chapter 12 | Text functions 167 Data type returned number Description Returns only the numbers in text, as data type number, for use with formulas involving numbers or numeric functions. The GetAsNumber function drops all non-numeric characters from text. Examples GetAsNumber(“FY98”) returns 98. GetAsNumber(“$1,254.50”) returns 1254.5. GetAsNumber(“2 + 2”) returns 22. GetAsNumber(SerialNumber) returns 35684, when the value of SerialNumber is TKV35FRG6HH84.
FileMaker Functions Reference Frank GetAsText Format GetAsText(data) Parameter data - any number, date, time or timestamp expression, or a field containing a number, date, time, timestamp, or container. Data type returned text Description Returns data as data type text, for use with formulas involving text or text functions. data can be data type number, date, time, timestamp, or container.
Chapter 12 | Text functions 169 Description Returns times or timestamps in text as data type time, for use with formulas involving the Time or Timestamp functions. The format of the supplied time must be the same as the time format on the system where the file was created. Use the GetAsTime or the Time function to enter a time constant into a formula. 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.
FileMaker Functions Reference Data type returned text Description Returns text as URL (Uniform Resource Locator) encoding, for use as a URL. Removes all styles from text. All characters are first converted to UTF-8 format. Characters that are neither letters nor digits, or digits that are in the upper ASCII range, are converted to %HH format (a percent sign followed by the character’s hexadecimal value). See the following website for more information on URL encoding: http://www.w3.
Chapter 12 Example GetValue(“London¶Paris¶Hong Kong”;2) returns Paris Hiragana Format Hiragana(text) Parameter text - any text expression or text field Data type returned text Description Converts Katakana (hankaku and zenkaku) in text to Hiragana. Example Hiragana(“ ”) returns KanaHankaku Format KanaHankaku(text) Parameter text - any text expression or text field Data type returned text Description Converts Zenkaku Katakana to Hankaku Katakana.
FileMaker Functions Reference KanaZenkaku Format KanaZenkaku(text) Parameter text - any text expression or text field Data type returned text Description Converts Hankaku Katakana to Zenkaku Katakana. Example KanaZenkaku(“ “) returns KanjiNumeral Format KanjiNumeral(text) Parameter text - any text expression or text field Data type returned text Description Converts Arabic numerals to Kanji numeral.
Chapter 12 | Text functions 173 Parameter text - any text expression or text field Data type returned text Description Converts from Hiragana to Zenkaku Katakana. Example Katakana(“ “) returns Left Format Left(text;numberOfCharacters) Parameters text - any text expression or text field numberOfCharacters - any numeric expression or field containing a number Data type returned text Description Returns the numberOfCharacters in text, counting from the left. Examples Left(“Manufacturing”;4) returns Manu.
FileMaker Functions Reference numberOfValues - any numeric expression or field containing a number Important See chapter 4, “Design functions” for information about literal text parameters. Data type returned text Description Returns a text result containing the specified numberOfValues from the list of values in text, counting from the left. Values are text items separated by carriage returns. A value can be empty, a single character, a word, a sentence, or a paragraph.
Chapter 12 | Text functions 175 Examples LeftWords(“Plaid Canvas Suitcase”;2) returns Plaid Canvas. LeftWords(Name;1) returns Sophie, when the Name field contains Sophie Tang. Length Format Length(field) Parameter field - any text, number, date, time, timestamp, or container field, or any text expression or numeric expression Data type returned number Description Returns the number of characters in field, including all spaces, numbers, and special characters.
FileMaker Functions Reference 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.
Chapter 12 | Text functions 177 Important See chapter 4, “Design functions” for information about literal text parameters. Data type returned text Description Returns a text result containing the specified numberOfValues in text, starting with startingValue. 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.
FileMaker Functions Reference Description Returns a text result containing the numberOfWords from text, beginning at startingWord. Note The ampersand (&) and hyphen (-) characters identify the beginning of a new word. Examples MiddleWords(“Plaid Canvas Suitcase”;2;2) returns Canvas Suitcase. MiddleWords(Name;1;2) returns Brigitte Erika, when the Name field contains Brigitte Erika Durand.
Chapter 12 | Text functions 179 Examples NumToJText(123456789;2;0) returns NumToJText(123456789;3;2) returns PatternCount 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 Description Returns the number of occurrences of searchString in text. Examples PatternCount(“Mississippi”;“is”) returns 2.
FileMaker Functions Reference 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 Description Returns the starting position of the specified occurrence of searchString in text.
Chapter 12 | Text functions 181 Quote Format Quote(text) Parameter text - any text expression or field Data type returned text Description Returns the text form of text enclosed in quotation marks. Special characters within text are escaped appropriately. This function protects text from being evaluated by the Evaluate function. Examples Quote(“hello”) returns “hello” Quote(“abc\¶”) returns “abc\¶” Quote(“say \”hello\” fred”) returns “say \”hello\” fred” Evaluate(Quote(“1 + 2”)) returns 1 + 2 Evaluate(“
FileMaker Functions Reference Description Replaces a string of characters in text with replacementText. Character replacement in text begins at the start character position and continues for numberOfCharacters characters. Compare to the Substitute function. Examples Replace(“1234567”;5;1;“X”) returns 1234X67. Replace(“1234567”;5;1;“XX”) returns 1234XX67. Replace(“1234567”;5;2;“X”) returns 1234X7. Replace(“William”;3;4;”NEW TEXT”) returns WiNEW TEXTm.
Chapter 12 | Text functions 183 Parameters text - any text expression or text field numberOfValues - any numeric expression or field containing a number Important See chapter 4, “Design functions” for information about literal text parameters. Data type returned text Description Returns a text result containing the specified numberOfValues in text starting from the right. Values are text items separated by carriage returns.
FileMaker Functions Reference numberOfWords - any numeric expression or field containing a number Data type returned text Description Returns a text result containing the numberOfWords in text, counting from the right. Note The ampersand (&) and hyphen (-) characters identify the beginning of a new word. Examples RightWords(“Plaid Canvas Suitcase”;2) returns Canvas Suitcase. RightWords(Name;1) returns Virtanen, when the Name field contains Matti Virtanen.
Chapter 12 | Text functions 185 Description Converts from Hankaku alphanumeric and symbols to Zenkaku alphanumeric and symbols.
FileMaker Functions Reference Substitute Format Substitute(text;searchString;replaceString) Parameters text - any text expression or text field searchString - any text expression or text field replaceString - any text expression or text field Data type returned text Description Returns a text string with every occurrence of searchString in text replaced by replaceString in text. The Substitute function is case-sensitive. Compare to the Replace function.
Chapter 12 | Text functions 187 Description Returns text stripped of all leading and trailing spaces. Tip Use the Trim function to remove unneeded spaces when you convert files from other programs or systems that require a fixed number of characters per field, or to remove spaces accidentally typed during data entry. Examples Trim(“ Tom ”) returns Tom. Trim(Middle(“00230013 William 1234”;9;9)) returns William.
FileMaker Functions Reference Characters within the non-Roman range are those belonging to the CJK symbols/ punctuations area, Hiragana, Katakana, Bopomofo, Hangul compatibility Jamo, Kanbun, CJK unified ideographs, and so on. Spaces are removed or inserted depending on the value of trimType, as given in the following tables: This trimType value Does this 0 Removes all spaces between non-Roman and Roman characters (always leave one space between Roman words).
Chapter 12 | Text functions 189 Description Returns all letters in text as uppercase. 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. ValueCount Format ValueCount(text) Parameter text - any text expression or text field Important See chapter 4, “Design functions” for information about literal text parameters.
FileMaker Functions Reference Parameter text - any text expression or text field Data type returned number Description Returns a count of the total number of words in text. Examples WordCount(“The sun is rising.”) returns 4. WordCount(Letter) returns the total number of words in the Letter field. Note The ampersand (&) and hyphen (-) characters identify the beginning of a new word.
Chapter 13 | Text formatting functions 191 Chapter 13 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. Text formatting functions operate on these parameters: • fields of type text • text constants (in quotes) • expressions having a text result Click a function name for details.
FileMaker Functions Reference RGB Format RGB(red;green;blue) Parameters red - any numeric expression or numeric field containing a value ranging from 0 to 255 green - any numeric expression or numeric field containing a value ranging from 0 to 255 blue - any numeric expression or numeric field containing a value ranging from 0 to 255 Data type returned number Description Returns an integer number from 0 to 16777215 obtained by combining the red, green, and blue values (each ranging from 0 to 255) to r
Chapter 13 | Text formatting functions 193 Data type returned text Description Changes the color of text to the color specified by the RGB function. Note Text formatting options will be lost if the data type that is returned is something other than text. Examples TextColor(“Plaid”;RGB(255;0;0)) returns the word Plaid in red. TextColor(“Plaid”;RGB(0;255;0)) returns the word Plaid in green. TextColor(“Plaid”;RGB(0;0;255)) returns the word Plaid in blue.
FileMaker Functions Reference Examples TextColorRemove(“Red Text and Green Text”) returns Red Text and Green Text displayed in the field’s default font color. TextColorRemove(“Red Text and Green Text”;RGB(255;0;0)) returns Red Text and Green Text with only the pure red font color removed from the words Red Text. TextFont Format TextFont(text;fontName{;fontScript}) Parameters text - any text expression or text field. fontName - any font name expressed in text.
Chapter 13 • SimplifiedChinese • OEM • Symbol • Other | Text formatting functions 195 Examples TextFont(“Plaid”;“Courier”) returns the word Plaid in the Courier font. TextFont(“Plaid”;“Arial”) returns the word Plaid in the Arial font. TextFont(“Plaid”;“Arial”;Cyrillic) returns the word Plaid in the Arial font in the font script of Cyrillic.
FileMaker Functions Reference 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. If this fails, then the TextFontRemove function uses the default font for the system script specified in the Fonts tab of the Preferences dialog box. This font might not be the same as the font script provided.
Chapter 13 | Text formatting functions 197 Description Removes all text formatting from text in a single action. All fonts, styles, font sizes, and font colors are removed from the specified text. Example TextFormatRemove(“Plaid”) returns the word Plaid without any text formatting applied.
FileMaker Functions Reference Description Removes all font sizes in text, or removes the font size specified by sizeToRemove. 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. When the font size is specified by sizeToRemove, only the specified font size is removed from every portion of the text displayed in that size and these same portions of the text are then displayed in the field's default font size.
Chapter 13 • Extend • Strikethrough • SmallCaps • Superscript • Subscript • Uppercase • Lowercase • Titlecase • WordUnderline • DoubleUnderline • AllStyles (all available styles) | Text formatting functions 199 Examples TextStyleAdd(“Plaid”;Italic) returns the word Plaid in italics. TextStyleAdd(FirstName;Bold+Underline) returns Sophie in bold, underlined text when the FirstName field contains Sophie.
FileMaker Functions Reference Data type returned text Description Removes the specified styles from text in a single action. You can remove multiple styles by using the + operator between style names. Negative values are not valid. The Plain styles cannot be used for this function. Plain is ignored if intermingled with other styles. Styles are not case-sensitive and do not contain spaces. An additional style called AllStyles has been provided to make it easier to remove all styles.
Chapter 14 | Time functions Chapter 14 Time functions Time functions calculate times and manipulate time information. Click a function name for details. This function Returns Hour, page 202 A number representing the number of hours in a time value. Minute, page 202 A number representing the number of minutes in a time value. Seconds, page 203 A number representing the number of seconds in a time value. Time, page 203 A time result with the specified number of hours, minutes, and seconds.
FileMaker Functions Reference Hour Format Hour(time) Parameter time - any time value or field of type time Data type returned number Description Returns a number representing the number of hours in time. Examples Hour(“12:15:23”) returns 12. Hour(Duration) + (Minute(Duration)/60) returns 2.5, when the Duration time field contains 2:30:15. If(Hour(HoursWorked) > 8;“Overtime Pay”;“ “) returns Overtime Pay when the number of hours in HoursWorked is greater than 8.
Chapter 14 | Time functions 203 Seconds Format Seconds(time) Parameter time - any time value or field of type time Data type returned number Description Returns a number representing the number of seconds in time. Examples Seconds(“12:15:23”) returns 23. Hour(Duration) + (Minute(Duration)/60) + (Seconds(Duration)/3600 returns 2.504166, if the Duration time field contains 2:30:15.
FileMaker Functions Reference Examples Time(4;14;32) returns 4:14:32. Time(4.5;10;30) returns 4:40:30. Time(4;15;70) returns 4:16:10.
Chapter 15 | Timestamp functions 205 Chapter 15 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, page 206 A timestamp containing a calendar date and time of day.
FileMaker Functions Reference Timestamp Format Timestamp(date;time) Parameters date - any calendar date or date field time - any time value or time field Data type returned timestamp Description Returns a timestamp containing date as a calendar date and time as a time of day. The format of the result depends on the date and time formats that were in use when the database file was created.
Chapter 16 | Trigonometric functions 207 Chapter 16 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 Atan, page 208 The trigonometric arc tangent (inverse tangent) of radians.
FileMaker Functions Reference Atan Format Atan(number) Parameter number - any numeric expression or field containing a numeric expression Data type returned number Description Returns the trigonometric arc tangent (inverse tangent) of number. 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.
Chapter 16 | Trigonometric functions 209 Degrees Format Degrees(angleInRadians) Parameter angleInRadians - any numeric expression or field containing a numeric expression, in radians. Data type returned number Description Converts angleInRadians to degrees. Use this function to translate results from trigonometric functions from radians to degrees. • angleInRadiansDegrees = 180 --------------------------------------------------π Examples Degrees(Atan(1)) returns 45. Degrees(1.0472) returns 60.00014030.
FileMaker Functions Reference Radians Format Radians(angleInDegrees) Parameter angleInDegrees - any numeric expression or field containing a numeric expression, in degrees. Data type returned number Description Converts angleInDegress to radians. 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.
Chapter 16 | Trigonometric functions 211 Tan Format Tan(angleInRadians) Parameter angleInRadians - any numeric expression or field containing a numeric expression, in radians. Data type returned number Description Returns the tangent of angleInRadians. Note With the Tan function, you cannot use values exactly equal to 90 degrees (Pi/2 radians), or multiples of 90 degrees. Sin(angleInRadians)Tan = ------------------------------------------------Cos(angleInRadians) Examples Tan(.13) returns .13073731....
FileMaker Functions Reference
Appendix A | Glossary 213 Appendix A Glossary A Access key (Windows) A key that activates a menu, menu item, or control when used with the ALT key. In Windows, this key corresponds to the underlined letter on a menu, command, or dialog box option. Access privileges Permission to view and work with certain records, fields, layouts, value lists, and scripts and to perform selected activities in a file. Account A username and (usually) password that accesses a file with a defined level of privileges.
FileMaker Functions Reference Authentication The process of checking the validity of an account and password (if one is defined) before assigning privileges and allowing access to a system or a database file. An account authenticated via FileMaker Pro or FileMaker Server is referred to as a FileMaker Account. (FileMaker Server can also authenticate an account via External Server -- an external authentication system such as Apple Open Directory, or a Windows Domain.
Appendix A | Glossary 215 Button Any layout object (a 3-D rectangle with a text label if created by the Button tool) that performs a specified script in Browse or Find modes. C Cache The amount of memory assigned to FileMaker Pro. A larger cache size increases performance. A smaller cache size saves data to the hard disk more frequently, offering greater protection in case of a system crash. Calculation field A field that returns the result of a calculation of values.
FileMaker Functions Reference Client/server architecture The relationship between two networked computers that share resources. The client requests services from the server, and the server provides services to the client. Clipboard A temporary storage area in computer memory where FileMaker Pro places the most recent selection you've cut or copied. Clone A copy of a FileMaker Pro file that contains all the field definitions, tables, layouts, scripts, and page setup options, but none of the data.
Appendix A | Glossary 217 Also refers to opening a file created with a previous version of FileMaker Pro. D Data Entry Only privilege set One of the three pre-defined privilege sets that appear in every file. The Data Entry Only privilege set allows read/write access to the records in a file, but not design access (for example, the ability to create layouts and value lists). Data source The data you want to access (such as a DBMS) and information to locate the data (such as the path or IP address).
FileMaker Functions Reference Driver manager The control panel that manages communication between requesting applications and data sources. When an application makes a request via ODBC/JDBC, the driver manager routes the request through the correct driver to the correct data source and returns the data to the requesting application. All ODBC/JDBC drivers and data sources to be used on that computer are registered with the driver manager.
Appendix A | Glossary 219 Expression A value or any computation that produces a value. Expressions can contain functions, field values, and constants and can be combined to produce other expressions.
FileMaker Functions Reference Field type The part of a field definition that determines what kind of data you can enter in the field and the kinds of operations FileMaker Pro can perform with the data. FileMaker Pro can create text, number, date, time, timestamp, container, calculation, and summary fields. (Global fields contain the same value for all records in the database and can be of any type except summary.
Appendix A | Glossary 221 Formula A set of instructions that FileMaker Pro follows to calculate a value used in a field or as the criteria for matching database records. Found set The set of records in a table that are made active by a find request. When you find all records, the found set is the entire table. Full Access privilege set One of the three pre-defined privilege sets that appear in every file.
FileMaker Functions Reference Grand summary part Use grand summary parts to view and display summary information (totals, averages, and so on) in summary fields for all records in the found set. You can add one grand summary part at the top (leading) and one grand summary part at the bottom (trailing) of a layout. Grouped object A collection of objects that behaves as one object in Layout mode. Guest A user who opens a protected file without specifying an account name and password.
Appendix A | Glossary 223 I Import To bring (copy) data from a table, another file, or another application into the current table. You can also import scripts from one FileMaker Pro file into another. Indexing An option that can be enabled when defining (or changing) the definition of a field. When indexing is enabled, FileMaker Pro builds a list of all the values that occur in the field in the table.
FileMaker Functions Reference Key A column (or columns) that makes a particular row unique (corresponds to a match field). L Labels layout A predefined layout with fields arranged for printing on mailing label stock, and media and index sheets. LAN (local area network) A connection between computers within a location using cable or a wireless system.
Appendix A | Glossary 225 Layout types FileMaker Pro includes six predefined layout types: Standard Form, Columnar List/ Report, Table View, Labels, Envelope, and Blank layout. You can use the predefined layout types as they are, or change them to suit your needs. To use a predefined layout type, in Layout mode, choose Layouts menu > New Layout/ Report. The assistant guides you through creating the type of layout or report you want.
FileMaker Functions Reference M Many-to-many relationship A correspondence between data in database tables in which more than one record in the first table is related to more than one record in another table, and more than one record in that table is related to more than one record in the first table. Match field For relational databases and lookups, a field in a source table and a field in a related table that contain values you want to use to find matching records.
Appendix A | Glossary 227 Multi-key field A match field that contains more than one value, each on a separate line. A multi-key field can be used in one table involved in a relationship, to match several possible values in the match field of the other table. Multimedia Files that combine media, like text, graphics, sound, animation, and video. N Network protocol A network protocol (for example, TCP/IP) is a set of rules that govern how computers exchange messages on a network.
FileMaker Functions Reference 1-away relationship A correspondence between database tables in which two tables are directly related to each other, with no other tables between them. One-to-many relationship A correspondence between data in database tables in which one record in the first table is related to more than one record in another table. One-to-one relationship A correspondence between data in database tables in which one record in the first table is related to one record in another table.
Appendix A | Glossary 229 Portal For relational databases, a layout object in one table where you place one or more related fields to display in rows the data from one or more related records. Preview mode The FileMaker mode in which you see how layouts will look when they're printed. (Use Browse mode to enter and edit your information; use Layout mode to design how your information is displayed.
FileMaker Functions Reference Related field For relational databases, a field in one table that is related to a field in another table (or to a different field within the same table). If a relationship is defined between two tables (even through another table), data in fields in one table can be accessed from the other table.
Appendix A | Glossary 231 S Schema In database terminology, a schema is the organization or structure of the elements, objects, and attributes of a database. A schema report will typically list all the database files, fields, scripts, layouts, relationships, tables, and so on in the database, and will document their properties. Script One or more instructions (script steps) that you define to automate repetitive or difficult tasks. You define scripts using ScriptMaker.
FileMaker Functions Reference Shared database A database file for which sharing has been enabled, which permits users to access the database file over a network.
Appendix A | Glossary 233 Stacking order The order in which objects overlap on a layout. You can change this order by cutting and pasting objects or by choosing Arrange menu > Bring to Front, or Bring Forward, or Send to Back, or Send Backward in Layout mode. Standard Form layout The default layout, with all fields arranged on separate lines in the order they were defined. The body part is only as tall as it needs to be to include all the fields in the database.
FileMaker Functions Reference System formats Settings you control with control panels to determine how dates, times, currency, and numbers display and sort on your computer. (See the documentation that came with your computer for information on using these control panels.) If the system formats are different on your computer from the ones on the computer where the database file was created, the first time you open the file, FileMaker Pro will ask you which system formats to use.
Appendix A | Glossary 235 Template Or Starter Solution. A pre-designed and formatted FileMaker Pro file, or web page, that you can copy and change for your own use. Also, a pre-defined website that you can select in the Web Viewer Setup dialog box to help you create a web viewer quickly. Text baseline In Layout mode, the dotted guideline that appears at the base of the text in a field or text block.
FileMaker Functions Reference Unit of measure In Browse and Layout modes, you can set the unit of measure to pixels, inches, or centimeters. Unstored calculation A calculation field with a result that is only calculated when the value is needed, for example, to browse or print. In most cases, FileMaker Pro makes a field stored when you define it, but you can change the storage type to unstored.
Appendix A | Glossary 237 W, X, Y, Z Web address The calculated expression that you enter in the Web Viewer Setup dialog box. A web address is not equivalent to a URL that a web user could enter in a web browser. Web browser An application that you can use to view web pages/sites on the World Wide Web or an intranet. Browsers download the web pages onto the viewer's computer. Web page An HTML document displayed on the Internet or on an intranet.
FileMaker Functions Reference XML (Extensible Markup Language) Instead of being a rigid file format, XML is a language for defining agreed-upon formats that groups can use for exchanging data. Many organizations and businesses are using XML to transfer product information, transactions, inventory, and other business data. FileMaker Pro can export XML data that can then be used, for example, by spreadsheet applications, data charting applications, and enterprise SQL databases.
