FileMaker 8 ® Functions Reference
© 2005 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 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 FieldIDs 11 11 11 12 17 18 19 20 21 22 23 24 25 27 29 30 30 31 32 32 33 33 34 34 35 35 36 37
FileMaker Functions Reference FieldNames FieldRepetitions FieldStyle FieldType GetNextSerialValue LayoutIDs LayoutNames 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(ActiveModifierKeys) Get(ActiveRepetitionNumber) Get(ActiveSelectionSize) Get
| 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(LayoutAccess) Get(LayoutCount) Get(LayoutName) Get(LayoutNumber) Get(La
FileMaker Functions Reference 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(WindowContentWidth) Get(WindowDesktopHeight) Get(WindowDesktopWid
| If IsEmpty IsValid IsValidExpression Let Lookup LookupNext Quote 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 133 134 136 137 138 139 140 140 141 141 142 142 143 143 144 145 145 146 146 147 147 148 149 149 151 152 152 153 155 156 7
FileMaker Functions Reference Chapter 12 Text functions Exact Filter FilterValues GetAsCSS GetAsDate GetAsNumber GetAsSVG GetAsText GetAsTime GetAsTimestamp GetValue Hiragana KanaHankaku KanaZenkaku KanjiNumeral Katakana Left LeftValues LeftWords Length Lower Middle MiddleValues MiddleWords NumToJText PatternCount Position Proper Replace Right RightValues RightWords RomanHankaku RomanZenkaku SerialIncrement Substitute Trim TrimAll Upper ValueCount 157 159 159 160 161 162 162 163 164 164 165 165 166 166
| 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 184 187 188 188 189 190 191 192 193 193 194 195 197 198 198 199 199 201 202 Atan Cos Degrees Pi Radians Sin Tan 203 204 204 205 205 206 206 207 Appendix A Glossary 209 9
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(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(HighContrastState) Get(HostIPAdd
FileMaker Functions Reference 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) Get(WindowName) Get
Chapter 1 KanjiNumeral Katakana 167 168 L, M, N, O Last LayoutIDs LayoutNames Left LeftValues LeftWords Length Let Lg Ln Log Lookup LookupNext Lower Max Middle MiddleValues MiddleWords Min Minute Mod Month MonthName MonthNameJ NPV NumToJText 153 47 48 168 169 170 170 134 144 145 145 136 137 171 20 171 172 173 21 198 146 33 34 34 58 173 P, Q PatternCount Pi PMT Position Proper PV Quote 174 205 59 175 175 60 138 R Radians Random RelationInfo Replace RGB Right RightValues RightWords RomanHankaku RomanZe
FileMaker Functions Reference S ScriptIDs ScriptNames Seconds SerialIncrement SetPrecision Sign Sin Sqrt StDev StDevP Substitute Sum 49 50 199 180 147 148 206 149 22 23 180 24 T, U TableIDs TableNames Tan TextColor TextColorRemove TextFont TextFontRemove TextFormatRemove TextSize TextSizeRemove TextStyleAdd TextStyleRemove Time Timestamp Trim TrimAll Truncate Upper 50 51 207 188 189 190 191 192 193 193 194 195 199 202 181 182 149 183 V, W, X, Y, Z ValueCount ValueListIDs ValueListItems ValueListNam
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.
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 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.
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. Max Format Max(field{;field...
Max(Field2) returns 8 when the calculation isn’t a repeating field. Max(Field1;Field2;Field3) returns 6, 6, 7, 8 when the calculation is a repeating field. Note When a referenced field is a repeating field, the Max function returns the maximum value in the first repetition field, then the maximum value in the second repetition field, and so on. Min Format Min(field{;field...
• Field3 contains 6. Min(Field2) returns 5 when the calculation isn’t a repeating field. Min(Field1;Field2;Field3) returns 1, 2, 7, 8 when the calculation is a repeating field. Note When a referenced field is a repeating field, the Min function returns the minimum value in the first repetition field, then the minimum value in the second repetition field, and so on. StDev Format StDev(field{;field...
In the following examples: • Field1 contains two repetitions with values of 1 and 2. • Field2 contains four repetitions with values of 5, 6, 7, and 8. • Field3 contains four repetitions with values of 6, 0, 4, and 4. • Field4 contains one repetition with a value of 3. StDev(Field4) results in an error because standard deviation of a single number is not defined. StDev(Field1;Field2;Field3) returns 2.64575131..., 3.05505046..., 2.12132034..., 2.82842712... for a repeating field.
StDevP = x 12 + x 22 + … + x n2 ⎛ x 1 + x 2 + … + x n⎞ 2 ------------------------------------------ – ----------------------------------------⎝ ⎠ n n Examples A portal displays the related values 5, 6, 7, and 8 in the field Scores. StDevP(table::Scores) returns 1.11803398.... In the following examples: • Field1 contains two repetitions with values of 1 and 2. • Field2 contains four repetitions with values of 5, 6, 7, and 8. • Field3 contains four repetitions with values of 6, 0, 4, and 4.
• corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats. • several fields in the first matching record specified by (table::field1;table::field2;...). You can include fields from different tables (table 1::field A;table 2::field B...). Examples An Invoice table has a portal showing line items.
Data type returned number Description Returns the variance of a sample represented by a series of non-blank values. The variance of a 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...).
The variance for Class 1 is much lower than the variance for Class 2, because the scores for Class 2 are more spread out. VarianceP Format VarianceP(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 variance of a population represented by a series of non-blank values.
• Field4 contains one repetition with a value of 3. VarianceP(Field4) results in an error since the variance of a single value is not defined. VarianceP(Field1;Field2;Field3) returns 4.66666666..., 6.22222222..., 2.25, 4 if the calculation is a repeating field. Student example: Two classes of students take an exam. Class 1 has scores of 70, 71, 70, 74, 75, 73, 72 and Class 2 has scores of 55, 80, 75, 40, 65, 50, 95. The population variance for each class is: Class 1: 3.26530612... Class 2: 310.20408163...
Chapter 3 | Date functions 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 30 The calendar date for the specified month, day, and year.
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. Data type returned date Description Returns the calendar date for month, day, and year.
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. This example assumes that the system date format is MM/DD/YYYY.
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.
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.
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.
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.
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. For example, if you select 1 (Sunday) as the starting day, then January 1 must be on Sunday, Monday, Tuesday, or Wednesday for that week to be the first week of the fiscal year.
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. YearName Format YearName(date;format) Parameters date - any calendar date format - a number (0, 1, or 2) that describes the display format Data type returned text Description Returns the Japanese year name of date, provided in the specified format.
Chapter 4 | Chapter 4 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. 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.
This function Returns LayoutNames, page 48 A list of the names of all layouts in the specified database file, separated by carriage returns. RelationInfo, page 48 A list of four values for each relationship directly related to the specified table. ScriptIDs, page 49 A list of all script IDs in the specified database file, separated by carriage returns. ScriptNames, page 50 A list of the names of all scripts in the specified database file, separated by carriage returns.
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. 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.
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. The location is measured from the top left corner of the layout (regardless of printer margins) and is specified in this order: position of left field boundary, position of top field boundary, position of right field boundary, position of bottom field boundary, degree of rotation (measured in a counterclockwise direction; 0 degrees for unrotated).
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. FieldComment("Customers"; "Accounts::Current Balance") returns “Customer's current balance” if it was entered as a comment for the Current Balance field in the Accounts table.
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. See “Design functions” on page 39 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.
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.
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.
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. See “Design functions” on page 39 for information about literal text parameters. Data type returned text Description Returns the next serial number of fieldName in fileName.
LayoutNames Format LayoutNames(fileName) Parameter fileName - the name of an open database file (local or remote). See “Design functions” on page 39 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.
4. fourth line - options that were set in the right side of the Edit Relationship dialog box when the relationship was defined. This line will be blank if the following options have not been set; otherwise these options are separated by spaces.
Data type returned text Description Returns a list of all script IDs in fileName, separated by carriage returns. 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). See “Design functions” on page 39 for information about literal text parameters.
Description Returns a list of all table IDs in fileName, separated by carriage returns. Each table ID is unique. Also, the ID is independent of when you create each table: the first table could have the smallest, middle, or largest value. 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).
Parameter fileName - the name of an open database file (local or remote). See “Design functions” on page 39 for information about literal text parameters. 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).
See “Design functions” on page 39 for information about literal text parameters. Data type returned text 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 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.
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.0 and uses the syntax External(“function name”, parameter), where function name is in quotes and is the name of an external function.
Chapter 6 | 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 58 The future value of an initial investment, based on a constant interest rate and payment amount for the number of periods in months.
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. For example, you can calculate how much you’ll earn on an investment in which you pay $50 a month for 60 months at a 6 percent annual interest rate.
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.
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 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.
This function Returns Get(CalculationRepetitionNumber), page 73 A number representing the repetition of the calculation field that is currently being calculated. Get(CurrentDate), page 73 The current date according to the system calendar. Get(CurrentHostTimestamp), page 74 The host's current date and time (to the nearest second) according to the system clock. Get(CurrentTime), page 75 The current time (to the nearest second) according to the system clock.
This function Returns Get(LayoutViewState), page 95 Information about how the database file is being viewed. Get(MultiUserState), page 95 A number representing the current multi-user state of the database file. Get(NetworkProtocol), page 96 The name of the network protocol that FileMaker Pro is using on this machine. Get(PageNumber), page 96 A number representing the current page being printed or previewed. Get(PortalRowNumber), page 97 The number of the currently selected portal row.
This function Returns Get(SystemVersion), page 113 The version of the operating system of the machine on which the function is executed. Get(TextRulerVisible), page 113 A Boolean value representing whether or not the text ruler is visible. Get(TotalRecordCount), page 114 The total number of records in the current table. Get(UserCount), page 114 The number of users who are currently accessing the file.
Enter Browse Mode [] Go to Layout ["LayoutName"] Go to Record/Request/Page [First] Loop If [DatabaseName::Date < Get(CurrentDate) - 30] Show Custom Dialog ["30 or more days late"] If [Get(LastMessageChoice) = 1] Halt Script Else If [Get(LastMessageChoice) = 2] Go to Layout ["Late Notice"] Print [] Else Send Mail [To: DatabaseName::Client; Subject: "Late Notice"; Message: "Your account is past due.
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.
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. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
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. 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.
Example Returns the number 9 when Shift+Alt is pressed on a computer running Windows. You could use this function in a script that includes a custom dialog box script step (with an OK and Cancel button) to perform some special action if the user presses the Alt (or Option) key while clicking OK.
See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 4 when 4 characters are selected. Get(ActiveSelectionStart) Format Get(ActiveSelectionStart) Parameter None Data type returned number Description Returns a number representing the starting character of the selected text. Returns the cursor's current position if no text is selected.
See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 1 if Allow user abort script step is on. Get(AllowToolbarState) Format Get(AllowToolbarState) Parameter None Data type returned number 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.
• English • French • Italian • German • Swedish • Spanish • Dutch • Japanese See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns English when the current application language is English. 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.
Get(CalculationRepetitionNumber) Format Get(CalculationRepetitionNumber) Parameter None Data type returned number Description Returns a number representing the repetition of the calculation field that is currently being calculated. The first repetition is 1. If the current field isn’t a repeating field, the function returns 1. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. 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 Returns 2/2/2004 when the system date is set to February 2, 2004.
Example Returns 1/1/2004 11:30:01 AM when the system clock shows January 1, 2004 11:30:01 AM on the host machine. Get(CurrentTime) Format Get(CurrentTime) Parameter None Data type returned time 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).
Description Returns the current date and 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). In client/server and peer-to-peer environments, Get(CurrentTimestamp) evaluates the status of the client machine running the script (not the host machine).
Data type returned text Description Returns the path to the desktop folder for the current user. In Windows, the path format is /Drive:/Documents and Settings/UserName/Desktop/. In the Mac OS, the path format is / DriveName/Users/UserName/Desktop/. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Examples Returns /C:/Documents and Settings/John Smith/Desktop/ for a user named John Smith in Windows.
Get(ErrorCaptureState) Format Get(ErrorCaptureState) Parameter None Data type returned number Description Returns 1 if the Set Error capture script step is on; otherwise returns 0. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 1 if the Set Error capture script step is on.
• See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Your account uses a privilege set that includes the extended privilege of Access via Instant Web Publishing (keyword "fmiwp"). Position(Get(ExtendedPrivileges); "fmiwp"; 1; 1) returns a value greater than 0. Get(FileMakerPath) Format Get(FileMakerPath) Parameter None Data type returned text Description Returns the path to the folder of the currently running copy of FileMaker Pro.
Description Returns the name of the currently active database file, without the filename extension. 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 Contacts when Contacts is the active file.
Returns file:/path/databaseName for local and remote files in the Mac OS. Returns fmnet:/networkaddress/databaseName for FileMaker Pro networked files. Get(FileSize) Format Get(FileSize) Parameter None Data type returned number Description Returns the size (in bytes) of the currently active database file.
If there are multiple windows open in the current database file, each window can have its own found count value, but results are returned for only the foreground window. 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.
Data type returned number Description Windows: Returns a number representing the state of the Use High Contrast option in the Accessibility Options control panel. Returns: • 0 if Use High Contrast is unavailable, inactive, or if the function is used on the Mac OS. • 1 if Use High Contrast is available and active. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Parameter None Data type returned text Description Returns the registered name of the computer that is hosting the database file. On the computer that is hosting the database file: • Windows: Choose Start menu > Control Panels > System, and then click the Network Identification tab. Full computer name displays the current registered name. Click Properties to rename the computer (you must be logged on as an administrator to rename the computer).
Notes • Mac OS: In FileMaker Pro, if an error occurs while performing an AppleScript from ScriptMaker™, the AppleScript error code will be returned. • In FileMaker Pro, if an error occurs while performing a SQL query, a SQLSTATE error will be returned by ODBC. • See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Error Number Description 100 File is missing 101 Record is missing 102 Field is missing 103 Relationship is missing 104 Script is missing 105 Layout is missing 106 Table is missing 107 Index is missing 108 Value list is missing 109 Privilege set is missing 110 Related tables are missing 111 Field repetition is invalid 112 Window is missing 113 Function is missing 114 File reference is missing 115 Specified menu set is not present 130 Files are damaged or missing and must b
Error Number Description 301 Record is in use by another user 302 Table is in use by another user 303 Database schema is in use by another user 304 Layout is in use by another user 306 Record modification ID does not match 400 Find criteria are empty 401 No records match the request 402 Selected field is not a match field for a lookup 403 Exceeding maximum record limit for trial version of FileMaker Pro 404 Sort order is invalid 405 Number of records specified exceeds number of record
Error Number Description 707 Graphic translator cannot be found 708 Can't import the file or need color monitor support to import file 709 QuickTime movie import failed 710 Unable to update QuickTime file reference because the database file is read-only 711 Import translator cannot be found 714 Password privileges do not allow the operation 715 Specified Excel worksheet or named range is missing 716 A SQL query using DELETE, INSERT, or UPDATE is not allowed for ODBC import 717 There is no
Error Number Description 809 Disk/volume is full 810 Disk/volume is locked 811 Temporary file cannot be opened as FileMaker Pro file 813 Record Synchronization error on network 814 File(s) cannot be opened because maximum number is open 815 Couldn't open lookup file 816 Unable to convert file 817 Unable to open file because it does not belong to this solution 819 Cannot save a local copy of a remote file 820 File is in the process of being closed 821 Host forced a disconnect 822 FMI
Error Number Description 1210 Plug-in function has already been registered 1211 List usage is not allowed in this function 1212 An operator (for example, +, -, *) is expected here 1213 This variable has already been defined in the Let function 1214 AVERAGE, COUNT, EXTEND, GETREPETITION, MAX, MIN, NPV, STDEV, SUM and GETSUMMARY: expression found where a field alone is needed 1215 This parameter is an invalid Get function parameter 1216 Only Summary fields allowed as first argument in GETSUMMAR
Description Returns a number corresponding to the button clicked in an alert message that is displayed by the Show Custom Dialog script step. Returns: • 1 for the first button (by default, labeled OK) • 2 for the second button (by default, labeled Cancel) • 3 for the third button Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Get(LayoutAccess) Format Get(LayoutAccess) Parameter None Data type returned number 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.
Parameter None Data type returned number Description Returns the total number of layouts in the database file. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. 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.
Parameter None Data type returned number Description Returns the number of the layout currently displayed, according to the list in the Set Layout Order dialog box. If there are multiple windows open in the current database file, each window can have its own layout number value, but results are returned for only the foreground window. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Get(LayoutViewState) Format Get(LayoutViewState) Parameter None Data type returned number Description Returns a number indicating the currently active database file view.
• 2 when network sharing is on, you’re accessing the database file from a client computer, and either all users or a specific group of users (based on their privilege set) have network access to the database file. 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.
Data type returned number Description Returns a number representing the current page being printed or previewed. If nothing is being printed or previewed, 0 is returned. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 4 when page 4 is being printed or previewed. Get(PortalRowNumber) Format Get(PortalRowNumber) Parameter None Data type returned number Description Returns the number of the currently selected portal row.
Data type returned text Description Returns the path to the preferences and default options folder for the current user. In Windows, the path format is /Drive:/Documents and Settings/UserName/Local Settings/ Application Data/. In the Mac OS, the path format is /DriveName/Users/UserName/ Library/Preferences/. Note See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
Examples For Windows: Returns HP LaserJet 4, WINSPOOL, LPT1. For Mac OS: Returns 24.109.265.43. 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].
Parameter None Data type returned number 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.
Parameter None Data type returned number 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.
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 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.
Get(RecordOpenCount) Format Get(RecordOpenCount) Parameter None 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.
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. Example Returns 1 if the current record is a new record that hasn’t been saved.
Description Returns a Boolean value representing the state of the Omit checkbox in Find mode. Returns 1 if the Omit checkbox is selected, otherwise returns 0. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 1 when the Omit checkbox is selected in the current find request.
Data type returned number 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.
Parameter None Data type returned text Description Returns the name of the script currently running (or paused). 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.
The following example shows how to pass named parameters, allowing access to both variable 'a' and 'b'. The simplified first parameter makes the second parameter more complex (the example returns 6, 12): ScriptParameter = "a = 5; b = 10" 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
Do Reports Perform Script [Find Customers] If [Get(ScriptResult) = 0] Show Custom Dialog [“You have created 10 records already.”] End If 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.
Parameter None Data type returned number 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.
Returns /DriveName/ in the Mac OS when the operating system is on a volume named DriveName. 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. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments. Example Returns 14.156.13.
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. In the Mac OS, find this address under Network Overview in the System Profile tab under Applications/Utilities/Apple System Profiler.
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. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
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.
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. 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.
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. 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.
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.
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.
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. See FileMaker Pro help for information about running scripts in client/server and peer-to-peer environments.
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.
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.
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.
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. In Windows, an asterisk appears next to the zoom percentage when Enlarge window contents to improve readability is selected in the General tab of the Preferences dialog box.
Chapter 8 | Logical functions 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.
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. The Case function evaluates each test expression in order, and when a True expression is found, returns the value specified in result for that expression.
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.
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.
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. A syntax error indicates an invalid calculation.
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. GetField Format GetField(fieldName) Parameter fieldName - any text expression or text field that refers to a field’s name See chapter 4, “Design functions” for information about literal text parameters.
GetNthRecord Format GetNthRecord(fieldName;recordNumber) Parameters fieldName - any related field or repeating field, or an expression that returns a field or a repeating field. recordNumber - the record number from which you want data. Data type returned text, number, date, time, timestamp, container Description Returns the contents of fieldName from the provided recordNumber.
Data type returned text, number, date, time, timestamp, container 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.
IsEmpty(Payments::DatePaid) returns 1 if, for example, the Payments table has been moved or renamed. IsEmpty(“text”) returns 0.
Data type returned number 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...
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.
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.
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.
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. 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.
Chapter 9 | Number functions Chapter 9 Number functions Number functions are used to manipulate numeric data. Click a function name for details. This function Returns Abs, page 140 The absolute value (a positive number) of a number. Ceiling, page 140 A number rounded up to the next integer. Combination, page 141 The number of ways to uniquely choose a specified number of items from a set of a specified size. Div, page 141 An integer of the specified number divided by the divisor.
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.
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. They form Pascal’s triangle.
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.
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 Factorial(n;i) 1 )( n 2 )… ( 1 ) 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.
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.
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.
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). Mod number ( Div(number;divisor) • divisor ) Examples Mod(13;4) returns 1.
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.
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. Examples SetPrecision(5/9;30) returns 0.
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.
Truncate(29343.98;-3) returns 29000. Truncate(123.456;4) returns 123.456. Truncate(29343.98;5) returns 29343.98.
Chapter 10 | Repeating functions Chapter 10 Repeating functions Repeating functions perform calculations on repeating fields. Click a function name for details. This function Returns Extend, page 152 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 152 The contents of the specified repetition of a repeating field.
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.
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. You can also find the contents of a particular repetition in a repeating field using square brackets [ ] as array operators.
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.
Summary functions Summary functions Summary functions produce a summary of all records in the found set, or subsummary values for records in different groups. Formulas can contain more than one summary function. Summary functions calculate more slowly than other functions because they generate values for a range of records. An alternate way to generate similar calculated results is to use Aggregate functions to summarize data in related records (whether or not they appear in a portal).
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.
Text functions 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.
This function Returns Katakana, page 168 Zenkaku Katakana converted from Hiragana. Left, page 168 The specified number of characters in the text, counting from the left. LeftValues, page 169 The specified number of values in the text, counting from the left. LeftWords, page 170 The specified number of words in the text, counting from the left. Length, page 170 The number of characters in the specified text, including all spaces, numbers, and special characters.
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. For text to match exactly, the uppercase and lowercase usage must be the same. If the fields match, the result is 1 (True); otherwise the result is 0 (False).
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. Examples Filter(“(408)555-1212”;“0123456789”) returns 4085551212. Filter(“AaBb”;“AB”) returns AB.
When the textToFilter or the filterValues parameter is a literal string, you must insert a paragraph character (¶) between each item in the string. To insert a carriage return character, click the ¶ button in the Specify Calculation dialog box. Examples FilterValues(“Plaid¶Canvas¶Suitcase”;“Plaid¶Canvas”) returns Plaid Canvas FilterValues(ValueListItems(“Database”;“Sizes”);“Medium¶Small”) returns Small Medium when a database file named Database has a value list Sizes that contains Small¶Medium¶Large.
GetAsDate Format GetAsDate(text) Parameter text - any text expression or text field containing text in the same format as the date on the system where the file was created. Data type returned date Description Returns dates in text as data type date, for use in formulas involving dates or date functions. The format of text date must be the same as the date format on the system where the file was created. Use the GetAsDate or Date function to enter a date constant into a formula.
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. GetAsSVG Format GetAsSVG(text) Parameter text - any text expression or text field Data type returned text Description Returns text, converted to the SVG (Scalable Vector Graphics) format, an Internet text format similar to HTML or CSS.
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.
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. GetAsTime(“02:47:35”) returns 1/1/0001 2:47:35 when you select timestamp as the calculation result. Abs(GetAsTime(“12:15 pm”) - CheckOut) returns 3:00:00 when the CheckOut time field contains 3:15 PM.
Description Returns the requested value given by valueNumber from listOfValues. Useful in looping scripts or recursive custom calculations. Values are text items separated by carriage returns. You can place several values together to create a carriage return-delimited list of values. A value can be empty, a single character, a word, a sentence, or a paragraph. When you press Enter or Return, you start creating a new value. The last value will be recognized with or without a carriage return.
Data type returned text Description Converts Zenkaku Katakana to Hankaku Katakana. Example KanaHankaku(“ “) returns 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.
Examples KanjiNumeral(123) returns “) returns KanjiNumeral(“ Katakana Format Katakana(text) Parameter text - any text expression or text field Data type returned text Description Converts from Hiragana to Zenkaku Katakana.
Examples Left(“Manufacturing”;4) returns Manu. Left(Name;Position(Name;“ “;1;1)) returns Sophie, when the Name field contains Sophie Tang. Left(PostalCode;3) & Upper(Left(LastName;4)) returns 481JOHN when the PostalCode field contains 48187 and LastName contains Johnson.
LeftWords Format LeftWords(text;numberOfWords) Parameters text - any text expression or text field numberOfWords - any numeric expression or field containing a number Data type returned text Description Returns a text result containing the numberOfWords in text, counting from the left. The ampersand (&) and hyphen (-) characters identify the beginning of a new word. Examples LeftWords(“Plaid Canvas Suitcase”;2) returns Plaid Canvas. LeftWords(Name;1) returns Sophie, when the Name field contains Sophie Tang.
Lower Format Lower(text) Parameter text - any text expression or text field Data type returned text Description Returns all letters in text as lowercase. 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.
Middle(“abcdefghij”;5;2) returns ef. Middle(Name;Position(Name;“ “;1;1)+1;3) returns Smi, when the text field Name contains John Smith. MiddleValues Format MiddleValues(text;startingValue;numberOfValues) Parameters text - any text expression or text field startingValue - any numeric expression or field containing a number numberOfValues - any numeric expression or field containing a number See chapter 4, “Design functions” for information about literal text parameters.
MiddleWords Format MiddleWords(text;startingWord;numberOfWords) Parameters text - any text expression or text field startingWord - any numeric expression or field containing a number numberOfWords - any numeric expression or field containing a number Data type returned text Description Returns a text result containing the numberOfWords from text, beginning at startingWord. The ampersand (&) and hyphen (-) characters identify the beginning of a new word.
Separator: 0 - no separator 1 - every 3 digits (thousands) 2 - ten thousands( 3 - tens( ) and millions( ), hundreds( ) unit ), thousands( ), ten thousands( ) and millions( ) unit Type: 0 - half width (Hankaku) number 1 - full width (Zenkaku) number 2 - Kanji character number 3 - Traditional-old-style Kanji character number Examples NumToJText(123456789;2;0) returns NumToJText(123456789;3;2) returns PatternCount Format PatternCount(text;searchString) Parameters text - any text expression or text fie
Position Format Position(text;searchString;start;occurrence) Parameters text - any text expression or text field searchString - any text expression or text field representing the set of characters you want to find. start - any numeric expression, or field containing a number, representing the number of characters from the start of the text string at which to begin the search. occurrence - any numeric expression or field containing a number, representing which instance of the text string you want to find.
Data type returned text Description Returns the first letter of each word in text as uppercase and all other letters as lowercase. Examples Proper(“ABCD”) returns Abcd. Proper(Name) returns Yumiko Kitagawa, when the Name field contains YUMIKO KITAGAWA. Replace Format Replace(text;start;numberOfCharacters;replacementText) Parameters text - any text expression or text field start - any numeric expression or field containing a number representing the starting position in text.
Replace(“William”;3;4;”NEW TEXT”) returns WiNEW TEXTm. Replace(PhoneNumber;1;3;”415”) returns 415-555-9054, when the PhoneNumber field contains 408-555-9054. Right Format Right(text;numberOfCharacters) Parameters text - any text expression or text field numberOfCharacters - any numeric expression or field containing a number Data type returned text Description Returns the specified numberOfCharacters in text, counting from the right. Examples Right(“Manufacturing”;4) returns ring.
Description Returns a text result containing the specified numberOfValues in text starting from the right. Values are text items separated by carriage returns. You can place several items together to create a carriage return-delimited list of values. A value can be empty, a single character, a word, a sentence, or a paragraph. When you press Return you start creating a new value. The last value will be recognized with or without a carriage return.
Examples RightWords(“Plaid Canvas Suitcase”;2) returns Canvas Suitcase. RightWords(Name;1) returns Virtanen, when the Name field contains Matti Virtanen. RomanHankaku Format RomanHankaku(text) Parameter text - any text expression or text field Data type returned text Description Converts from Zenkaku alphanumeric and symbols to Hankaku alphanumeric and symbols.
SerialIncrement Format SerialIncrement(text;incrementBy) Parameters text - any text that also contains a number incrementBy - any numeric expression to increment the text by Data type returned text Description Returns the combined text and numbers specified by text, with the numbers in text incremented by the specified amount. The text in text isn’t removed, as normally happens when performing standard math against a value that contains text.
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.
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.
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). 1 Always includes a half-width space between non-Roman and Roman characters (always leave one space between Roman words).
Examples Upper(“Ca”) returns CA. Upper(“12n34p”) returns 12N34P. ValueCount Format ValueCount(text) Parameter text - any text expression or text field See chapter 4, “Design functions” for information about literal text parameters. Data type returned number Description Returns a count of the total number of values in text. Values are text items separated by carriage returns. You can place several items together to create a carriage-return-delimited list of values.
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. The ampersand (&) and hyphen (-) characters identify the beginning of a new word.
Text formatting functions Text formatting functions can be used to change the color, font, size, and style of the specified text. For example, you could use the TextFont function to change the font of the specified text from Arial to Courier. 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.
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 represent a color.
Data type returned text Description Changes the color of text to the color specified by the RGB function. 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. TextColor(“Plaid”;RGB(0;0;0)) returns the word Plaid in black.
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.
• SimplifiedChinese • OEM • Symbol • Other 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 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.
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. TextSize Format TextSize(text;fontSize) Parameters text - any text expression or text field fontSize - any font size expressed as an integer Data type returned text Description Changes the font size of the specified text to fontSize.
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. The font size is described in points (72 points to the inch).
• Extend • Strikethrough • SmallCaps • Superscript • Subscript • Uppercase • Lowercase • Titlecase • WordUnderline • DoubleUnderline • AllStyles (all available styles) 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. The following calculation removes all styles from the text, then italicizes the entire phrase.
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.
Time functions Time functions calculate times and manipulate time information. Click a function name for details. This function Returns Hour, page 198 A number representing the number of hours in a time value. Minute, page 198 A number representing the number of minutes in a time value. Seconds, page 199 A number representing the number of seconds in a time value. Time, page 199 A time result with the specified number of hours, minutes, and seconds.
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. Hour(CheckIn) returns 3 when the value of CheckIn is 3:24.
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.
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.
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 202 A timestamp containing a calendar date and time of day.
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.
Trigonometric functions Trigonometric functions are used to calculate degrees, angles, and other geometric data. 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 204 The trigonometric arc tangent (inverse tangent) of radians. Cos, page 204 The cosine of the specified angle.
Atan Format Atan(number) Parameter - any numeric expression or field containing a numeric expression Data type returned number Description Returns the trigonometric arc tangent (inverse tangent) of . 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. Cos Format Cos(angleInRadians) Parameter angleInRadians - any numeric expression or field containing a numeric expression, in radians.
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. Degrees 180 • angleInRadians--------------------------------------------------π Examples Degrees(Atan(1)) returns 45. Degrees(1.0472) returns 60.00014030....
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. A degree is equal to Pi/180 radians.
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. With the Tan function, you cannot use values exactly equal to 90 degrees (Pi/2 radians), or multiples of 90 degrees. Tan Sin(angleInRadians)------------------------------------------------Cos(angleInRadians) Examples Tan(.13) returns .13073731.... Tan(Radians(34)) returns .6745085.
Appendix A 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.
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 | 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.
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 | Convert Opening a data file from another application, which creates a new FileMaker Pro file containing the data. 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).
Driver The ODBC or JDBC driver translates SQL queries into commands that a DBMS can understand. It processes ODBC/JDBC calls, submits SQL requests to the data source, and returns the data back to the driver manager, which then routes it to the requesting application (for example, FileMaker Pro). Driver manager The control panel that manages communication between requesting applications and data sources.
Appendix A | Export To save data from one file so that it can be used in another file or in another application. 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.
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.) File path The location of a file in an operating system as identified by the drive, folders, filename, and file extension.
Appendix A | 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.
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 | 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. This improves the performance of tasks such as finding data, but it increases the size of the database file on disk.
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. Layout An arrangement of fields, objects, pictures, and layout parts that represents the way information is organized and presented when you browse, preview, or print records.
Appendix A | 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.
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. (A match field is sometimes called a key field or trigger field.
Appendix A | 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.
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. Operands Components of a formula.
Appendix A | Port A pre-assigned number that indicates a "logical connection place" where a client (such as a web browser) can connect to a particular server application on a networked computer. Port numbers range from 0 to 65536. Port 80 is the default port for HTTP services such as FileMaker Pro web publishing, but you can use another port number if 80 is already in use by another server application.
Record One set of fields in a database table. Each record contains data about a single activity, individual, subject, or transaction. Recursive script A script that calls itself. 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 | Report with grouped data A subsummary report that you create using the Columnar List/Report layout type. Reports with grouped data can include totals and subtotals. Row When a database file is viewed as a table, a row corresponds to a record. S Schema In database terminology, a schema is the organization or structure of the elements, objects, and attributes of a database.
Serial number A unique number entered by FileMaker Pro for each record. You can tell FileMaker Pro to automatically enter a serial number for each record by setting the Auto-Enter options in the Options for Field dialog box. You can also serialize records by choosing Records menu > Replace Contents in Browse mode. Shared database A database file for which sharing has been enabled, which permits users to access the database file over a network.
Appendix A | Source table The table upon which one or more tables in the relationships graph are based. The source table is the table defined in the Tables tab of the Define Database dialog box. SQL A structured programming query language that controls and interacts with a DBMS. Stacking order The order in which objects overlap on a layout.
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 | 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. Text baseline In Layout mode, the dotted guideline that appears at the base of the text in a field or text block. When you move a field or text block, the text baseline extends out horizontally from the object to help you align it with other objects.
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. URL (Uniform Resource Locator) A web address, which consists of a protocol, a host name, and optionally a port, a directory, and a filename. For example, http://www.filemaker.com/ , ftp://12.34.56.78:80/ myfiles/, or fmp7://mywebsite.com/sample.fp7.
Appendix A | Web page An HTML document displayed on the Internet or on an intranet. Web server A computer that is connected to the Internet or an intranet, and has a web server application installed on it. Web server applications deliver web pages and associated files to web browsers. Web site One or more web pages connected by links and displayed on the Internet or on an intranet. Web user Someone using a web browser to access a FileMaker Pro database published on the World Wide Web or an intranet.
