FileMaker 9 ® Functions Reference
© 2004-2007 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 other trademarks are the property of their respective owners. FileMaker documentation is copyrighted.
Contents Chapter 1 Introduction About functions Using this functions reference Functions reference (alphabetical list) Chapter 2 Aggregate functions Average Count List Max Min StDev StDevP Sum Variance VarianceP Chapter 3 Date functions Date Day DayName DayNameJ DayOfWeek DayOfYear Month MonthName MonthNameJ WeekOfYear WeekOfYearFiscal Year YearName 1 1 1 2 9 10 11 12 15 16 17 19 20 22 23 25 26 27 27 28 28 29 30 30 31 31 32 33 33
FileMaker Functions Reference Chapter 4 Design functions DatabaseNames FieldBounds FieldComment FieldIDs FieldNames FieldRepetitions FieldStyle FieldType GetNextSerialValue LayoutIDs LayoutNames LayoutObjectNames RelationInfo ScriptIDs ScriptNames TableIDs TableNames ValueListIDs ValueListItems ValueListNames WindowNames Chapter 5 External functions External Chapter 6 Financial functions FV NPV PMT PV Chapter 7 Get functions Get(AccountName) Get(ActiveFieldContents) Get(ActiveFieldName) Get(ActiveFie
| Get(ActiveModifierKeys) Get(ActiveRepetitionNumber) Get(ActiveSelectionSize) Get(ActiveSelectionStart) Get(AllowAbortState) Get(AllowToolbarState) Get(ApplicationLanguage) Get(ApplicationVersion) Get(CalculationRepetitionNumber) Get(CurrentDate) Get(CurrentHostTimestamp) Get(CurrentTime) Get(CurrentTimestamp) Get(CustomMenuSetName) Get(DesktopPath) Get(DocumentsPath) Get(ErrorCaptureState) Get(ExtendedPrivileges) Get(FileMakerPath) Get(FileName) Get(FilePath) Get(FileSize) Get(FoundCount) Get(HighContras
FileMaker Functions Reference Get(PrinterName) Get(PrivilegeSetName) Get(RecordAccess) Get(RecordID) Get(RecordModificationCount) Get(RecordNumber) Get(RecordOpenCount) Get(RecordOpenState) Get(RequestCount) Get(RequestOmitState) Get(ScreenDepth) Get(ScreenHeight) Get(ScreenWidth) Get(ScriptName) Get(ScriptParameter) Get(ScriptResult) Get(SortState) Get(StatusAreaState) Get(SystemDrive) Get(SystemIPAddress) Get(SystemLanguage) Get(SystemNICAddress) Get(SystemPlatform) Get(SystemVersion) Get(TemporaryPat
| Chapter 8 Logical functions Case Choose Evaluate EvaluationError GetAsBoolean GetField GetLayoutObjectAttribute GetNthRecord If IsEmpty IsValid IsValidExpression Let Lookup LookupNext Self Chapter 9 Number functions Abs Ceiling Combination Div Exp Factorial Floor Int Lg Ln Log Mod Random Round SetPrecision Sign Sqrt Truncate Contents 119 120 120 121 123 124 124 125 127 129 129 130 131 131 133 134 136 137 138 138 139 139 140 140 141 142 142 143 143 144 144 145 146 146 147 147 7
FileMaker Functions Reference Chapter 10 Repeating functions Extend GetRepetition Last Chapter 11 Summary functions GetSummary Chapter 12 Text functions Exact Filter FilterValues GetAsCSS GetAsDate GetAsNumber GetAsSVG GetAsText GetAsTime GetAsTimestamp GetAsURLEncoded GetValue Hiragana KanaHankaku KanaZenkaku KanjiNumeral Katakana Left LeftValues LeftWords Length Lower Middle MiddleValues MiddleWords NumToJText PatternCount Position Proper Quote 149 149 150 150 153 153 155 157 158 158 159 160 161
| Replace Right RightValues RightWords RomanHankaku RomanZenkaku SerialIncrement Substitute Trim TrimAll Upper ValueCount WordCount Chapter 13 Text formatting functions RGB TextColor TextColorRemove TextFont TextFontRemove TextFormatRemove TextSize TextSizeRemove TextStyleAdd TextStyleRemove Chapter 14 Time functions Hour Minute Seconds Time Chapter 15 Timestamp functions Timestamp Contents 176 176 177 178 178 179 179 180 181 182 183 184 184 187 187 188 189 190 191 193 193 194 195 196 199 199 200 20
FileMaker Functions Reference Chapter 16 Trigonometric functions Acos Asin Atan Cos Degrees Pi Radians Sin Tan 205 205 206 206 207 207 208 208 209 210 Appendix A Glossary 211
Chapter 1 | Introduction 1 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.
2 FileMaker Functions Reference Functions reference (alphabetical list) This section lists the functions in alphabetical order.
Chapter 1 FieldType Filter FilterValues Floor FV 41 158 158 141 53 G Get(AccountName) Get(ActiveFieldContents) Get(ActiveFieldName) Get(ActiveFieldTableName) Get(ActiveLayoutObjectName) Get(ActiveModifierKeys) Get(ActiveRepetitionNumber) Get(ActiveSelectionSize) Get(ActiveSelectionStart) Get(AllowAbortState) Get(AllowToolbarState) Get(ApplicationLanguage) Get(ApplicationVersion) Get(CalculationRepetitionNumber) Get(CurrentDate) Get(CurrentHostTimestamp) Get(CurrentTime) Get(CurrentTimestamp) Get(CustomMe
4 FileMaker Functions Reference Get(LayoutNumber) Get(LayoutTableName) Get(LayoutViewState) Get(MultiUserState) Get(NetworkProtocol) Get(PageNumber) Get(PortalRowNumber) Get(PreferencesPath) Get(PrinterName) Get(PrivilegeSetName) Get(RecordAccess) Get(RecordID) Get(RecordModificationCount) Get(RecordNumber) Get(RecordOpenCount) Get(RecordOpenState) Get(RequestCount) Get(RequestOmitState) Get(ScreenDepth) Get(ScreenHeight) Get(ScreenWidth) Get(ScriptName) Get(ScriptParameter) Get(ScriptResult) Get(SortStat
Chapter 1 Get(WindowVisible) Get(WindowWidth) Get(WindowZoomLevel) GetAsBoolean GetAsCSS GetAsDate GetAsNumber GetAsSVG GetAsText GetAsTime GetAsTimestamp GetAsURLEncoded GetField GetLayoutObjectAttribute GetNextSerialValue GetNthRecord GetRepetition GetSummary GetValue 116 116 117 124 159 160 161 161 162 163 163 164 124 125 42 127 150 153 164 H, I, J, K Hiragana Hour If Int IsEmpty IsValid IsValidExpression KanaHankaku KanaZenkaku KanjiNumeral Katakana 165 199 129 142 129 130 131 165 166 166 167 L, M,
6 FileMaker Functions Reference Ln Log Lookup LookupNext Lower Max Middle MiddleValues MiddleWords Min Minute Mod Month MonthName MonthNameJ NPV NumToJText 143 143 133 134 169 15 170 171 172 16 200 144 30 30 31 54 172 P, Q PatternCount Pi PMT Position Proper PV Quote 173 208 55 174 175 56 175 R Radians Random RelationInfo Replace RGB Right RightValues RightWords RomanHankaku RomanZenkaku Round 208 144 44 176 187 176 177 178 178 179 145 S ScriptIDs ScriptNames Seconds 46 47 200
Chapter 1 Self SerialIncrement SetPrecision Sign Sin Sqrt StDev StDevP Substitute Sum 136 179 146 146 209 147 17 19 180 20 T, U TableIDs TableNames Tan TextColor TextColorRemove TextFont TextFontRemove TextFormatRemove TextSize TextSizeRemove TextStyleAdd TextStyleRemove Time Timestamp Trim TrimAll Truncate Upper 47 48 210 188 189 190 191 193 193 194 195 196 201 203 181 182 147 183 V, W, X, Y, Z ValueCount ValueListIDs ValueListItems ValueListNames Variance VarianceP WeekOfYear WeekOfYearFiscal WindowN
8 FileMaker Functions Reference
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.
10 FileMaker Functions Reference Average Format Average(field{;field...}) Parameter field - any related field, repeating field, or set of non-repeating fields; or an expression that returns a field, repeating field, or set of non-repeating fields. Parameters in curly braces { } are optional. Data type returned number Description Returns a value that is the average of all valid, non-blank values in field, where field can be any of the following: • a repeating field (repeatingField).
Chapter 2 | Aggregate functions 11 Examples A Student table has a portal showing scores for all exams a student has taken. The exam scores are in a table called Exams. Average(Exams::Score) returns the student’s average score for all exams she has taken. In the following examples: • Field1 contains two repetitions with values of 1 and 2. • Field2 contains four repetitions with values of 5, 6, 7, and 8. • Field3 contains 6. Average(Field2) returns 6.5 when the calculation isn’t a repeating field.
12 FileMaker Functions Reference Description Returns the number of valid, non-blank values in field where field can be any of the following: • a repeating field (repeatingField). • a field in matching related records specified by (table::field), whether or not these records appear in a portal. • several non-repeating fields in a record (field1;field2;field3...).
Chapter 2 | Aggregate functions 13 Data type returned text Description Returns a concatenated list of non-blank values (separated by carriage returns) for either: • a single field (table::field), calculates a single result over all repetitions (if any) for this field and over all matching related records, whether or not these records appear in a portal. • several fields and/or literal values (table::field1,constant,table::field2...
14 FileMaker Functions Reference Examples In the following examples: • Field1 contains white. • Field2 contains black. • Field3 contains three repetitions with values of red, green, blue. • Related::Field4 refers to three records that contain 100, 200, 300.
Chapter 2 | Aggregate functions 15 Max Format Max(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 text, number, date, time, timestamp Description Returns the highest valid value in: • a repeating field (repeatingField).
16 FileMaker Functions Reference Min Format Min(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 text, number, date, time, timestamp Description Returns the smallest valid non-blank value in: • a repeating field (repeatingField).
Chapter 2 | Aggregate functions 17 StDev Format StDev(field{;field...}) Parameter field - any related field, repeating field, or set of non-repeating fields; or an expression that returns a field, repeating field, or set of non-repeating fields. Parameters in curly braces { } are optional. Data type returned number Description Returns the standard deviation of the sample represented by a series of non-blank values in: • a repeating field (repeatingField).
18 FileMaker Functions Reference Examples A portal displays the related values 5, 6, 7, and 8 in a field called Scores. StDev(table::Scores) returns 1.29099444.... In the following examples: • Field1 contains two repetitions with values of 1 and 2. • Field2 contains four repetitions with values of 5, 6, 7, and 8. • Field3 contains four repetitions with values of 6, 0, 4, and 4. • Field4 contains one repetition with a value of 3.
Chapter 2 | Aggregate functions 19 StDevP Format StDevP(field{;field... }) Parameter field - any related field, repeating field, or set of non-repeating fields; or an expression that returns a field, repeating field, or set of non-repeating fields. Parameters in curly braces { } are optional. Data type returned number Description Returns the standard deviation of a population represented by a series of non-blank values in: • a repeating field (repeatingField).
20 FileMaker Functions Reference 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. • Field4 contains one repetition with a value of 3.
Chapter 2 | Aggregate functions 21 Description Returns the total of all valid, non-blank values in: • a repeating field (repeatingField). • a field in matching related records specified by (table::field), whether or not these records appear in a portal. • several non-repeating fields in a record (field1;field2;field3...).
22 FileMaker Functions Reference Variance Format Variance(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 sample represented by a series of non-blank values. The variance of a distribution is a measure of how spread out the distribution is.
Chapter 2 | Aggregate functions 23 Examples A portal displays the related values 5, 6, 7, and 8 in Scores. Variance(table::Scores) returns 1.66666666.... 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.
24 FileMaker Functions Reference Description Returns the variance of a population represented by a series of non-blank values. The variance of a population distribution is a measure of how spread out the distribution is. Use this function on any of the following fields: • a repeating field (repeatingField). • a field in matching related records specified by (table::field), whether or not these records appear in a portal. • several non-repeating fields in a record (field1;field2;field3...).
Chapter 3 | Date functions 25 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. Tip You can use zero (0) and negative numbers as Date function arguments.
26 FileMaker Functions Reference Date Format Date(month;day;year) Parameters month - the month of the year (a two-digit number; see note). day - the day of the month (a two-digit number; see note). year - the year (four digits between 0001 and 4000. For example, 2008 but not 08). Important The order of the parameters in the Date function is always Month, Day, Year, no matter what operating system or FileMaker Pro date formats you are using.
Chapter 3 | Date functions 27 Day Format Day(date) Parameter date - any calendar date 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/2008”) returns 15.
28 FileMaker Functions Reference Examples DayName(Date(10;7;2008)) returns Tuesday. DayName(ProjectDue) returns Tuesday when ProjectDue is 10/7/2008. DayName(“10/7/2008”) returns Tuesday. “Return your selection by “& DayName(DueDate) displays the text Return your selection by followed by the name of the day stored in DueDate.
Chapter 3 | Date functions 29 Description Returns a number representing the day of the week that date falls on. The number 1 represents Sunday, 2 represents Monday, 3 represents Tuesday, and so on. For example, you can find out on what day of the week a holiday occurs. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see FileMaker Pro Help. Examples DayOfWeek(“10/8/2008”) returns 4.
30 FileMaker Functions Reference Month Format Month(date) Parameter date - any calendar date Data type returned number Description Returns a number in the range 1 through 12, representing the number of the month of the year in which date occurs. 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 Month(“3/19/2008”) returns 3.
Chapter 3 | Date functions 31 Examples MonthName(“6/6/2008”) returns June. “Payment due by the end of: “ & MonthName(Date(Month(InvoiceDate) + 1;Day(InvoiceDate);Year(InvoiceDate))) returns Payment due by the end of May, where InvoiceDate is 4/4/2008. “Payment for: “ & MonthName(Date(Month(Payment) + 1;Day(Payment);Year(Payment))) returns Payment for: followed by the name of the month that is one past the month of the last payment.
32 FileMaker Functions Reference Description Returns the number of weeks after January 1 of the year of date. Fractions of weeks occurring at the beginning or end of the year count as full weeks, so the WeekOfYear function returns values 1 through 54. Important To avoid errors when using dates, always use four-digit years. For more information about how FileMaker Pro handles two-digit dates, see FileMaker Pro Help. Examples WeekOfYear(“1/1/2008”) returns 1.
Chapter 3 | Date functions 33 Examples WeekOfYearFiscal(Date(1;2;2008);1) returns 53. WeekOfYearFiscal(Date(1;7;2008);1) returns 1. WeekOfYearFiscal(Date(1;1;2009);5) returns 1. Year Format Year(date) Parameter date - any calendar date Data type returned number Description Returns a number representing the year in which date occurs. For example, you can extract the year from a field containing the date an item sold. Important To avoid errors when using dates, always use four-digit years.
34 FileMaker Functions Reference Description Returns the Japanese year name of date, provided in the specified format. If the value for format is blank or other than 0, 1, or 2, then 0 is used. Format 08 (Meiji 8), 8 (Taisho 8), xxxx [before 1868.9.8]) 1- 8 (Mei 8), 8 (Tai 8), 8 (Sho 8), 8 (Showa 8), 8 (Hei 8), 8 (Heisei 8), (Seireki xxxx (Sei xxxx [before 1868.9.8]) 2 - M8, T8, S8, H8, A.D.xxxx (before 1868.9.8) Name of Emperor in 0 = Long, 1 = Abbreviated, 2 = 2 byte Roman.
Chapter 4 | Design functions 35 Chapter 4 Design functions Design functions return information about the structure of open database files. For example, you could determine the names of all the layouts or fields in an open database file. Note FileMaker Pro limits the information returned by a design function, according to the privilege set in effect when the function evaluates a database file. See FileMaker Pro Help for more information about granting access to database files.
36 FileMaker Functions Reference This function Returns LayoutNames, page 43 A list of the names of all layouts in the specified database file, separated by carriage returns. LayoutObjectNames A list of the names of all named layout objects, separated by carriage returns. , page 44 RelationInfo, page 44 A list of four values for each relationship directly related to the specified table. ScriptIDs, page 46 A list of all script IDs in the specified database file, separated by carriage returns.
Chapter 4 | Design functions 37 Examples To determine whether “Customers” is one of the files currently open, use the DatabaseNames function with the FilterValues function in the formula: FilterValues( DatabaseNames;"Customers") If the formula returns any text value, then “Customers” is open.
38 FileMaker Functions Reference Example FieldBounds(“Customers”;“Layout #1”;“Field”) returns 36 48 295 65 0 in the example below. Notice that all parameters are enclosed in quotation marks. 48 65 36 295 FieldComment Format FieldComment(fileName;fieldName) Parameters fileName - the name of an open database file (local or remote). fieldName - the name of a field in the specified database file. Important See Design functions, page 35 for information about literal text parameters.
Chapter 4 | Design functions FieldIDs Format FieldIDs(fileName;layoutName) Parameters fileName - the name of an open database file (local or remote). layoutName - the name of a layout or table in the specified database file. Important See Design functions, page 35 for information about literal text parameters. Data type returned text Description Returns a list of all field IDs in fileName and layoutName, separated by carriage returns. Related fields are returned as TableID::RelatedFieldID.
40 FileMaker Functions Reference 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. If layoutName isn’t specified, then the field names of the first table created (the “default table”) will be returned. Note If FieldNames returns a question mark (?) or the name of only one field, go to the Specify Calculation dialog box and make sure the Calculation result is text.
Chapter 4 | Design functions 41 FieldStyle Format FieldStyle(fileName;layoutName;fieldName) Parameters fileName - the name of an open database file (local or remote). layoutName - the name of a layout in the specified database file. fieldName - the name of a field on the specified layout. Important See Design functions, page 35 for information about literal text parameters. Data type returned text Description Returns the field formatting applied to fieldName on layoutName in the fileName file.
42 FileMaker Functions Reference Important See Design functions, page 35 for information about literal text parameters. Data type returned text Description Returns information about fieldName. Field names must be in the form tablename::fieldname to specify a field that exists in a table different from the current table. The result has four values separated by spaces: • The first value is either Standard, StoredCalc, Summary, UnstoredCalc, or Global.
Chapter 4 | Design functions 43 Description Returns the next serial number of fieldName in fileName. Field names must be fully qualified in the form tablename::fieldname to specify a field that exists in a table different from the current table. Example GetNextSerialValue(“Customers”;”CustID”) returns the next serial number for the CustID field. LayoutIDs Format LayoutIDs(fileName) Parameter fileName - the name of an open database file (local or remote).
44 FileMaker Functions Reference 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. LayoutObjectNames Format LayoutObjectNames(fileName;layoutName) Parameter fileName - the name of an open database file (local or remote). layoutName - the name of a layout in the specified database file.
Chapter 4 | Design functions 45 Important See Design functions, page 35 for information about literal text parameters. Data type returned text Description Returns a list of four values for each relationship directly related to tableName. Values in a list are separated by carriage returns, and lists are separated by two carriage returns. For each additional relationship connected to tableName, an additional list of four values is output.
46 FileMaker Functions Reference Example A database file called Human Resources has three tables: Company, Employees, and Addresses. Company::Company ID is connected to Employees::Company ID, Employees::Employee ID is connected to Addresses::Employee ID and Employees::DateOfHire is connected to Addresses::DateMovedIn. The relationships have the following criteria: • You can create records in all tables. • You cannot delete records in all tables.
Chapter 4 | Design functions 47 ScriptNames Format ScriptNames(fileName) Parameter fileName - the name of an open database file (local or remote). Important See Design functions, page 35 for information about literal text parameters. Data type returned text Description Returns a list of the names of all scripts in fileName, separated by carriage returns. Example ScriptNames(“Customers”) returns a list of all the scripts in the Customers database file.
48 FileMaker Functions Reference TableNames Format TableNames(fileName) Parameter fileName - the name of an open database file (local or remote). Important See Design functions, page 35 for information about literal text parameters. Data type returned text Description Returns a list of all table occurrences in the relationships graph for fileName, separated by carriage returns.
Chapter 4 | Design functions 49 ValueListItems Format ValueListItems(fileName;valuelist) Parameters fileName - the name of an open database file (local or remote). valuelist - the name of a value list in the specified database file. Important See Design functions, page 35 for information about literal text parameters. Data type returned text Description Returns a list of the values in valuelist, separated by carriage returns.
50 FileMaker Functions Reference WindowNames Format WindowNames{(fileName)} Parameter {fileName} - the name of an open database file (local or remote). Parameters in curly braces { } are optional. Data type returned text Description Returns a list of the names of windows that are currently open. Use the optional fileName parameter to only return windows that are based on the specified file. The window could be visible, hidden, or minimized.
Chapter 5 | External functions 51 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.
52 FileMaker Functions Reference 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. Plug-ins created for FileMaker Pro version 7.0 and later do not use the External(“function name”, parameter) syntax.
Chapter 6 | Financial functions 53 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 53 The future value of an initial investment, based on a constant interest rate and payment amount for the number of periods in months.
54 FileMaker Functions Reference Notes • When interestRate is 0, this function returns the result of payment * periods. • The FV function doesn’t account for the present value of your investment, and it assumes that payment is made at the end of each period. ( 1 + ιντερεστΡατε ) περιοδσ – 1 Φς = παψμεντ ∗ ----------------------------------------------------------------------------ιντερεστΡατε Examples FV(50;.11/12;5 * 12) returns 3975.90398429.... FV(2000;.12;30) + 5000 * (.
Chapter 6 | Financial functions 55 Examples NPV(Loan;.05) returns 156.91277445..., when the repeating field, Loan, contains 2000 (the initial payment), 600, 300, 500, 700, and 400. The result (156.91277445...) is the actual profit in today’s dollars that will be realized from this transaction. NPV(Amounts;.10) returns 16758.35604870..., when the repeating field, Amounts, contains -5000 (the initial investment), 10,000, 0, 10,000, and 10,000.
56 FileMaker Functions Reference PV Format PV(payment;interestRate;periods) Parameters payment - payment amount to be made per period. Type a negative number for money you pay and a positive number for money you receive. interestRate - interest rate per period. periods - number of periods (intervals between payments). Data type returned number Description Returns the present value of a series of equal payments made at regular intervals (periods), assuming a fixed interestRate per interval.
Chapter 7 | Get functions 57 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.
58 FileMaker Functions Reference This function Returns Get(ApplicationVersion), page 69 Text representing the FileMaker Pro application version. Get(CalculationRepetitionNumber), page 69 A number representing the repetition of the calculation field that is currently being calculated. Get(CurrentDate), page 70 The current date according to the system calendar. Get(CurrentHostTimestamp), page 71 The host's current date and time (to the nearest second) according to the system clock.
Chapter 7 | Get functions 59 This function Returns Get(LayoutNumber), page 86 The number of the layout currently displayed, according to the list in the Set Layout Order dialog box. Get(LayoutTableName), page 86 The name of the table that the layout is displaying records from. Get(LayoutViewState), page 87 Information about how the database file is being viewed. Get(MultiUserState), page 87 A number representing the current multi-user state of the database file.
60 FileMaker Functions Reference This function Returns Get(SystemNICAddress), page 105 The hardware addresses of all the Network Interface Controller cards connected to the machine. Get(SystemPlatform), page 105 A number indicating the current platform. Get(SystemVersion), page 106 The version of the operating system of the machine on which the function is executed. Get(TemporaryPath), page 106 The path to the current user’s temporary folder used by FileMaker Pro.
Chapter 7 | Get functions 61 Get functions example This script uses the function Get(CurrentDate) to check each record in the found set to see if an account is past due. If an account is past due, the script shows a message and prompts the user to click a button labeled Ignore, Send letter, or Send mail (set up through the Show Custom Dialog script step). The script captures the user's response using Get(LastMessageChoice).
62 FileMaker Functions Reference Get(AccountName) Format Get(AccountName) Parameter None Data type returned text Description For FileMaker authentication, Get(AccountName) returns the name of the authenticated account being used by the current user of the database file. If a user is using the default Admin account, Get(AccountName) returns Admin. If a user is using the FileMaker Pro guest account then [Guest] will be returned.
Chapter 7 | Get functions 63 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.
64 FileMaker Functions Reference Get(ActiveFieldTableName) Format Get(ActiveFieldTableName) Parameter None Data type returned text Description Returns the name of the table that contains the active field (the field that currently contains the cursor). If there is no active field, an empty string is returned. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Example There are two fields, Teachers::Name and Coaches::Name, on the current layout.
Chapter 7 | Get functions 65 Get(ActiveModifierKeys) Format Get(ActiveModifierKeys) Parameter None Data type returned number Description Returns a number representing the keyboard modifier keys (for example, Control+Shift) that are being pressed. The number is calculated by summing numbers representing each modifier key being pressed.
66 FileMaker Functions Reference Description Returns a number representing the active repetition of a repeating field (the repetition that currently contains the cursor). The first repetition is 1. If the current field isn’t a repeating field, the function returns 1. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Example Returns 5 when the cursor is in the fifth repetition of a repeating field.
Chapter 7 | Get functions 67 Description Returns a number representing the starting character of the selected text. Returns the cursor's current position if no text is selected. If there are multiple windows open in the current database file, a result is returned for only the foreground window. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Example Returns 5 when the selection starts at character 5.
68 FileMaker Functions Reference Description Returns a Boolean value representing whether toolbars are allowed to be visible. Returns 1 if toolbars are allowed, otherwise returns 0. The Allow Toolbars script step sets the toolbar state. For more information, see Allow Toolbars script step. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Example Returns 1 if toolbars are allowed to be visible.
Chapter 7 | Get functions 69 Get(ApplicationVersion) Format Get(ApplicationVersion) Parameter None Data type returned text Description Returns text representing the FileMaker application and version. • Pro (version) for FileMaker Pro. • ProAdvanced (version) for FileMaker Pro Advanced. • Runtime (version) for FileMaker Runtime. • Web (version) for FileMaker Web Client. • Server (version) for FileMaker Web Server.
70 FileMaker Functions Reference Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Example Returns 5 when FileMaker Pro is calculating the fifth repetition of a repeating field. Get(CurrentDate) Format Get(CurrentDate) Parameter None Data type returned date Description Returns the current date according to the system calendar. The format of the result varies based on the date format that was in use when the database file was created.
Chapter 7 | Get functions 71 Get(CurrentHostTimestamp) Format Get(CurrentHostTimestamp) Parameter None Data type returned timestamp Description Returns the host’s current date and time according to the system clock, to the nearest second. The format of the value returned is determined by the database file’s settings. You can use your client system’s settings in the operating system.
72 FileMaker Functions Reference 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 operating system settings. Note In client/server and peer-to-peer environments, Get(CurrentTimestamp) evaluates the status of the client machine running the script (not the host machine).
Chapter 7 | Get functions 73 Get(CustomMenuSetName) Format Get(CustomMenuSetName) Parameter None Data type returned text Description Returns the name of the active custom menu set. If the active menu set isn’t a custom menu set, an empty string is returned. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Examples Returns Custom Menu Set #1 when this custom menu set is active.
74 FileMaker Functions Reference Examples Returns /C:/Documents and Settings/John Smith/Desktop/ for a user named John Smith in Windows. Returns /MacintoshHD/Users/John Smith/Desktop/ for a user named John Smith in the Mac OS. Get(DocumentsPath) Format Get(DocumentsPath) Parameter None Data type returned text Description Returns the path to the documents folder for the current user. In Windows XP, the path format is /Drive:/Documents and Settings/UserName/My Documents/.
Chapter 7 | Get functions 75 Data type returned number Description Returns 1 if the Set Error capture script step is on; otherwise returns 0. Note 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.
76 FileMaker Functions Reference 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. In Windows, the path format is /Drive:/Program Files/FileMaker/FileMaker Pro 9.0/. In the Mac OS, the path format is /DriveName/Applications/FileMaker Pro 9.0/. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments.
Chapter 7 | Get functions 77 Example Returns Contacts when Contacts is the active file. Get(FilePath) Format Get(FilePath) Parameter None Data type returned text Description Returns the full path indicating the location of the currently active database file. In Windows, the full path is file:/drive:/folder/filename for local files. For remote files, the full path is file://volume/folder/filename. In the Mac OS, the full path is file:/volume/folder/ filename for local and remote files.
78 FileMaker Functions Reference Data type returned number Description Returns the size (in bytes) of the currently active 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. • See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments.
Chapter 7 | Get functions 79 Get(HighContrastColor) Format Get(HighContrastColor) Parameter None Data type returned text Description Windows: Returns the name of the current high contrast default color scheme. Returns an empty value (null) if Use High Contrast is unavailable, inactive, or if the function is used on the Mac OS. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments.
80 FileMaker Functions Reference Get(HostApplicationVersion) Format Get(HostApplicationVersion) Parameter None Data type returned text Description Returns the version of FileMaker Pro or FileMaker Server running on the computer that is hosting the current database. If the current database is not shared or hosted, the Get(HostApplicationVersion) function returns an empty string. Examples Returns Pro 9.0v1 when the host computer is running FileMaker Pro 9 version 1. Returns ProAdvanced 9.
Chapter 7 | Get functions 81 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 14.156.13.121 when the current database is being hosted.
82 FileMaker Functions Reference Get(LastError) Format Get(LastError) Parameter None Data type returned number Description Returns a number representing the error, if any, in the execution of the most recently executed script step. Use this function to detect and control the outcome of errors. See FileMaker Pro Help. Notes • Mac OS: In FileMaker Pro, if an error occurs while performing an AppleScript from ScriptMaker™, the AppleScript error code will be returned.
Chapter 7 | Get functions 83 Data type returned number 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.
84 FileMaker Functions Reference Example For ODBC imports and Execute SQL script steps, returns [DataDirect][Macintosh ODBC Driver Manager] Data source name not found and no default driver specified (-1) when a data source name wasn’t found and the driver wasn’t specified. 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.
Chapter 7 | Get functions 85 Get(LayoutCount) Format Get(LayoutCount) 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.
86 FileMaker Functions Reference Get(LayoutNumber) Format Get(LayoutNumber) 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.
Chapter 7 | Get functions 87 Get(LayoutViewState) Format Get(LayoutViewState) Parameter None Data type returned number Description Returns a number indicating the currently active database file view.
88 FileMaker Functions Reference Description Returns a number representing the level of sharing for the database file using FileMaker Network. Returns: • 0 when network sharing is off. • 1 when network sharing is on, you’re accessing the database file from the host computer, and either all users or a specific group of users (based on their privilege set) have network access to the database file.
Chapter 7 | Get functions 89 Get(PageNumber) Format Get(PageNumber) Parameter None 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.
90 FileMaker Functions Reference Get(PreferencesPath) Format Get(PreferencesPath) Parameter None 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/.
Chapter 7 | Get functions 91 Description In Windows, returns a string with each of these entries separated by a comma: • the printer name • the driver name • the name of the printer port In Mac OS, returns a string with these entries separated by the word on: • the Queue name of the printer (if provided) • the IP address of the printer If any of this information isn’t available, is inserted in the result (except for Queue name in the Mac OS).
92 FileMaker Functions Reference 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. Examples For current user Administrator, Get(PrivilegeSetName) might return [Full Access].
Chapter 7 | Get functions 93 Notes • Get(RecordAccess) only returns information about the privileges defined for accessing records. It ignores access privileges assigned through individual layouts. To fully check access to a record, consider the return values of the Get(LayoutAccess) function, page 84 and Get(RecordAccess).
94 FileMaker Functions Reference Get(RecordModificationCount) Format Get(RecordModificationCount) Parameter None Data type returned number Description Returns the total number of times changes to the current record have been committed. To commit changes, you can, for example: • click out of all fields (exit the record) • go to a different record • enter Find mode If multiple windows are open, clicking in another window does not commit the record.
Chapter 7 | Get functions 95 Data type returned number Description Returns the number of the current record in the current found set. This value is determined by the relative place of the record in the found set, and changes depending on the find criteria and the sort order. Notes • To return a value that uniquely and permanently identifies a record in this table, use Get(RecordID).
96 FileMaker Functions Reference Get(RecordOpenState) Format Get(RecordOpenState) Parameter None Data type returned number Description Returns a number representing the state of the current record. Returns: • 0 for a closed or committed record. • 1 for a new record that hasn’t been committed. • 2 for a modified record that hasn’t been committed.
Chapter 7 | Get functions 97 Description Returns the total number of find requests defined for the current table. If there are multiple windows open in the current database file, then results are returned for only the top-most window of the file that the calculation is defined in. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Example Returns 5 when there are five find requests defined for the current table.
98 FileMaker Functions Reference Description Returns the number of bits needed to represent the color or shade of gray of a pixel on the main screen. A value of 8 represents 256 (equal to 28) colors or shades of gray. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Examples Returns 32 on a display showing millions (232) of colors. Returns 16 on a display showing thousands (216) of colors. Returns 4 on a VGA display.
Chapter 7 | Get functions 99 Get(ScreenWidth) Format Get(ScreenWidth) Parameter None Data type returned number Description Returns the number of pixels displayed horizontally 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.
100 FileMaker Functions Reference Get(ScriptParameter) Format Get(ScriptParameter) Parameter None Data type returned text Description When this function is part of a calculation evaluated within a script, returns the script parameter passed into the script. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Examples Returns Print when “Print” was the value of the parameter passed into the current script.
Chapter 7 | Get functions 101 Get(ScriptResult) Format Get(ScriptResult) Parameter None Data type returned text, number, date, time, timestamp, container Description When this function is part of a calculation evaluated within a script, returns the script result from a performed subscript. If a subscript doesn’t return a result, then the content of the script result will be empty.
102 FileMaker Functions Reference Get(SortState) Format Get(SortState) Parameter None Data type returned number Description Returns 0 if the records in the active table are not sorted. Returns 1 if the records in the active table are sorted. Returns 2 if the records in the active table are partially sorted (semi-sorted). Each window has its own sort state.
Chapter 7 | Get functions 103 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.
104 FileMaker Functions Reference Get(SystemIPAddress) Format Get(SystemIPAddress) Parameter None Data type returned text Description Returns a list of all IP addresses of all active NIC (Network Interface Controller) card connected to the computer. IP addresses are separated by carriage returns. See FileMaker Pro Help for information about running scripts in client/server and peer-topeer environments.
Chapter 7 | Get functions 105 Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Example Returns Japanese when Japanese is the language currently set on the operating system. Get(SystemNICAddress) Format Get(SystemNICAddress) Parameter None Data type returned text Description Returns a list of hardware addresses of all Network Interface Controller cards associated with the machine. Values in the list are separated by carriage returns.
106 FileMaker Functions Reference Description Returns a number indicating the current platform: • -1 if the current platform is PowerPC-based Macs • 1 if the current platform is Intel-based Macs • -2 if the platform is Windows XP or Windows Vista Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Examples Get(SystemPlatform) returns -2 when the current platform is a Windows platform.
Chapter 7 | Get functions 107 Parameters None Description Returns the path to the temporary folder that FileMaker Pro uses on the current user’s computer. Note Because your operating system controls the location of temporary files, the path returned might be different from the examples shown.
108 FileMaker Functions Reference Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Examples Returns 1 when the text ruler is visible. Get(TotalRecordCount) Format Get(TotalRecordCount) Parameter None Data type returned number Description Returns the total number of records in the current table.
Chapter 7 | Get functions 109 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 ODBC or JDBC.
110 FileMaker Functions Reference Get(UseSystemFormatsState) Format Get(UseSystemFormatsState) Parameter None Data type returned number Description Returns a Boolean value representing the state of the Use System Formats command in the Format menu. Returns 1 if Use System Formats is on, otherwise returns 0. Note See FileMaker Pro Help for information about running scripts in client/server and peer-to-peer environments. Examples Returns 1 when Use System Formats is on.
Chapter 7 | Get functions 111 Examples Returns 400 in the Mac OS when the current window height is 437. 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.
112 FileMaker Functions Reference Data type returned number 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. The main monitor is where the menu bar is located.
Chapter 7 | Get functions 113 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.
114 FileMaker Functions Reference 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.
Chapter 7 | Get functions 115 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.
116 FileMaker Functions Reference 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.
Chapter 7 | Get functions 117 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.
118 FileMaker Functions Reference
Chapter 8 | Logical functions 119 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.
120 FileMaker Functions Reference Case Format Case(test1;result1{;test2;result2;...;defaultResult}) Parameters test - any text or numeric expression. result - result corresponding to the expression. Parameters in curly braces { } are optional. Data type returned text, number, date, time, timestamp, container Description Returns one of several possible results based on a series of tests.
Chapter 8 | Logical functions 121 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.
122 FileMaker Functions Reference Examples Evaluate(TextField) returns 4 when TextField contains 2 + 2. Evaluate("textfield") returns 2 + 2 when textfield contains 2 + 2. Evaluate(GetField("textfield")) returns 4 when textfield contains 2 + 2. Evaluate(TextField;[Amount]) returns .80 when TextField contains .08 * Amount and the Amount field contains 10.00. Evaluate(“Let(TaxRate=.05;“& Tax Rate Calculation &“)”) returns .
Chapter 8 | Logical functions 123 The Evaluate function has an optional second parameter, which is a field the calculation is dependent on. When the dependent field contents change, FileMaker Pro re-evaluates the calculation. In the following example, the Total calculation will be re-evaluated when SubTotal changes: Evaluate(StateTaxFormula, SubTotal) + ShippingCost The dependent parameter can also be useful in other cases.
124 FileMaker Functions Reference GetAsBoolean Format GetAsBoolean(data) Parameter data - any text, number, date, time, timestamp or container expression, or a field containing text, a number, date, time, timestamp or container. Data type returned number 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.
Chapter 8 | Logical functions 125 Examples Assume you have the following fields: Arrow and Target. Arrow contains the text string Target, and Target contains the text string Bullseye. • GetField(“Arrow”) returns Target. Notice the use of quotation marks around Arrow to indicate the literal string is the fieldName parameter. • GetField(Arrow) returns Bullseye. Notice the absence of quotation marks to indicate the value stored in the Arrow field is the fieldName parameter.
126 FileMaker Functions Reference Description Returns the specified attributes of the layout object given by objectName that is currently active in the calculation. Attributes: objectType - returns the object's type as a text literal, in English. Valid return values are: field, text, graphic, line, rectangle, rounded rectangle, oval, group, button group, portal, tab panel, web viewer, and unknown. hasFocus - returns 1 (True) if objectName is currently active, otherwise returns 0 (False).
Chapter 8 | Logical functions 127 content - returns the content of the specified object as follows. For: • web viewers - returns the current content (such as HTML code) • fields - returns the field data formatted using the specified object's properties • text objects - returns the text (including text from merge fields) • graphics - returns image data such as Container data type For all other objects, returns an empty string. enclosingObject - returns objectName of the enclosing layout object.
128 FileMaker Functions Reference Description Returns the contents of fieldName from the provided recordNumber. Note The result of GetNthRecord() will not be updated when the record referred to by GetNthRecord() is a record other than the one in which the calculation is currently being evaluated. GetNthRecord of the current table returns the Nth record of the found set according to how the current table is sorted.
Chapter 8 | Logical functions 129 If Format If(test;result1;result2) Parameters test - any numeric value or logical expression result1 - expression or field name result2 - expression or field name Data type returned text, number, date, time, timestamp, container 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.
130 FileMaker Functions Reference Data type returned number Description Returns True(1) if field is empty, if a related field, related table, relationship, or file is missing, or if some other error occurs; otherwise returns False(0). Examples IsEmpty(OrderNum) returns 1 if the OrderNum field is empty. If(IsEmpty(LastName);“Invalid record”;““) displays Invalid Record if the LastName field is blank, but displays nothing if there is an entry in LastName.
Chapter 8 | Logical functions 131 Examples IsValid(Datefield) returns 0 if there is non-date data in Datefield, for example if text was imported into it. IsValid(Amount) returns 0 if there is only text in the number field Amount. IsValid(table::field) returns 0 if the related table was renamed and the relationship isn’t updated with the new filename.
132 FileMaker Functions Reference Data type returned text, number, date, time, timestamp, container Description Sets varX to the result of expressionX for the duration of calculation, until the script exits (local variables), or until the file is closed (global variables). Multiple variables are allowed when using a list syntax that is enclosed in square brackets [ ] and is separated by semicolons.
Chapter 8 | Logical functions 133 The following example shows how to pass named parameters, allowing access to both variable 'a' and variable '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 exa
134 FileMaker Functions Reference Example There are two tables, People and Company, in a database file containing the data shown below. People table CompanyID Employee 100 John Smith 200 Peter Wong 300 Sally Anderson Company table CompanyID CompanyName Code 100 Apple 91234 100 Apple 82345 200 FileMaker 95054 The People and Company tables are related using the number field CompanyID.
Chapter 8 | Logical functions 135 Description Returns the value specified in sourceField using the relationships in the relationships graph. If the lookup fails, the value from the source field in the next lower or higher matching record will be returned, as specified by lower/higherFlag. In order for this function to access the value in sourceField, the tables containing the source field and calculation field need to be related.
136 FileMaker Functions Reference Self Format Self Parameters None Data type returned text, number, date, time, timestamp Description Returns the content of the object in which the calculation is defined, otherwise returns empty. The Self function provides a way for a calculation to reference the object with which it is associated without having to explicitly reference the object. Use the Self function to create a single calculation formula that can be applied to different objects.
Chapter 9 | Number functions 137 Chapter 9 Number functions Number functions are used to manipulate numeric data. Click a function name for details. This function Returns Abs, page 138 The absolute value (a positive number) of a number. Ceiling, page 138 A number rounded up to the next integer. Combination, page 139 The number of ways to uniquely choose a specified number of items from a set of a specified size. Div, page 139 An integer of the specified number divided by the divisor.
138 FileMaker Functions Reference Abs Format Abs(number) Parameter number - any numeric expression or field containing a numeric expression Data type returned number, time Description Returns the absolute value (a positive number) of number. For example, if a negative number appears in a field, the Abs function removes the minus sign and changes it to a positive value. Examples Abs(-123) returns 123. Abs(PriceDifference) returns the positive value of the number in the PriceDifference field.
Chapter 9 | Number functions 139 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.
140 FileMaker Functions Reference Description Returns the next lowest integer value after dividing number by divisor. The Div function is equivalent to Floor(number/divisor). Examples Div(2.5;2) returns 1. Div(-2.5;2) returns -2. Exp Format Exp(number) Parameter number - any numeric expression or field containing a numeric expression Data type returned number Description Returns the value of the constant e (the base of the natural logarithm, equal to 2.7182818) raised to the power of number.
Chapter 9 | Number functions Data type returned number Description Returns the factorial of number stopping at 1, or stopping at the optional numberOfFactors. Useful in statistics and combinatorics. Where n = number and i = numberOfFactors: Φαχτοριαλ(ν) = ν ( ν – 1 ) ( ν – 2 )… ( 1 ) Φαχτοριαλ(ν;ι) = ν ( ν – 1 ) ( ν – 2 )… ( ν – ι + 1 ) Examples Factorial(3) returns 6, which = 3 * 2 * 1. Factorial(10;3) returns 720, which = 10 * 9 * 8.
142 FileMaker Functions Reference Int Format Int(number) Parameter number - any numeric expression or field containing a numeric expression Data type returned number Description Returns the whole number (integer) part of number without rounding. It drops any digits to the right of the decimal point, depending on the number you specify. Examples Int(1.45) returns 1. Int(123.987) returns 123. Int(Players/3) returns 4, if Players contains 13.
Chapter 9 | Number functions 143 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.
144 FileMaker Functions Reference Mod Format Mod(number;divisor) Parameters number - any numeric expression or field containing a numeric expression divisor - numeric expression or field containing a numeric expression Data type returned number Description Returns the remainder after number is divided by divisor. The result has the same sign as divisor. Use the Mod function to test whether a number is even or odd by specifying a divisor of 2 (if the result is zero the number is even, otherwise it’s odd).
Chapter 9 | Number functions 145 Data type returned number 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.
146 FileMaker Functions Reference SetPrecision Format SetPrecision(expression;precision) 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.
Chapter 9 | Number functions Examples Sign(15.12) returns 1. Sign(-175) returns -1. Sign(BalanceDue) returns 0, if BalanceDue is a number field containing 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. Σθρτ = νυμβερ Examples Sqrt(4) returns 2. Sqrt(SquareFeet) returns 6 if the SquareFeet number field contains 36.
148 FileMaker Functions Reference Description Returns number truncated to the specified precision (number of decimal places), without evaluating the value of discarded digits. Use the Round function to round up or down to the required precision. Examples Truncate(123.456;2) returns 123.45. Truncate(-14.6;0) returns -14. Truncate(29343.98;-3) returns 29000. Truncate(123.456;4) returns 123.456. Truncate(29343.98;5) returns 29343.98.
Chapter 10 | Repeating functions 149 Chapter 10 Repeating functions Repeating functions perform calculations on repeating fields. Click a function name for details. This function Returns Extend, page 149 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 150 The contents of the specified repetition of a repeating field.
150 FileMaker Functions Reference GetRepetition Format GetRepetition(repeatingField;number) Parameters repeatingField - any repeating field, or an expression that returns a reference to a repeating field. number - the field repetition number. Data type returned text, number, date, time, timestamp, container 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.
Chapter 10 | Repeating functions 151 Description Returns the last valid, non-blank value in field. If field specifies a repeating field then it returns the last non-blank repetition. If field specifies a related field, then it returns the last non-blank value in the related set. Note The last related value will depend on the way related records are sorted. If the related records are not sorted, then the Last function returns a value based on the creation order of the records.
152 FileMaker Functions Reference
Chapter 11 | Summary functions 153 Chapter 11 Summary functions Summary functions produce a summary of all records in the found set, or subsummary values for records in different groups. Formulas can contain more than one summary function. Summary functions calculate more slowly than other functions because they generate values for a range of records.
154 FileMaker Functions Reference Description Returns the value of summaryField for the current range of records when the database file is sorted by breakField. This produces subsummary values. If the database file isn’t sorted by the break field, the result is blank. When a summary field is also used as the break field, returns the summary field value for the entire found set of records (a grand summary value).
Chapter 12 | Text functions 155 Chapter 12 Text functions Text functions can be used to analyze, rearrange, extract, and build text strings. For example, you could use the MiddleWords function to extract specific words from supplied text. Text functions operate on these parameters: • fields of type text • text constants (in quotes) • expressions having a text result Click a function name for details.
156 FileMaker Functions Reference This function Returns KanjiNumeral, page 166 Kanji numerals converted from Arabic numerals. Katakana, page 167 Zenkaku Katakana converted from Hiragana. Left, page 167 The specified number of characters in the text, counting from the left. LeftValues, page 168 The specified number of values in the text, counting from the left. LeftWords, page 168 The specified number of words in the text, counting from the left.
Chapter 12 | Text functions 157 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).
158 FileMaker Functions Reference Filter Format Filter(textToFilter;filterText) Parameters textToFilter - any text expression or text field filterText - the characters to preserve in the specified text Data type returned text Description Returns from textToFilter only those characters specified in filterText, in the order that they were originally entered in textToFilter. If filterText doesn’t have any characters, an empty string is returned. The Filter function is case-sensitive.
Chapter 12 | Text functions 159 Description Returns a text result containing only the values that were provided in filterValues, in the order that they were originally entered in textToFilter. If filterValues doesn’t have any values, an empty string is returned. Values are text items separated by carriage returns. A value can be empty, a single character, a word, a sentence, or a paragraph. When you press Enter or Return, you start creating a new value.
160 FileMaker Functions Reference Example GetAsCSS(text) returns the example result shown below when the field text contains the word “Frank” and the word Frank has the following text attributes: Font = Helvetica, Font Size = 12 points, Font Color = red, Font Style = bold.
Chapter 12 | Text functions 161 GetAsNumber Format GetAsNumber(text) Parameter text - any text expression or text field containing numbers Data type returned number Description Returns only the numbers in text, as data type number, for use with formulas involving numbers or numeric functions. The GetAsNumber function drops all non-numeric characters from text. Examples GetAsNumber(“FY98”) returns 98. GetAsNumber(“$1,254.50”) returns 1254.5. GetAsNumber(“2 + 2”) returns 22.
162 FileMaker Functions Reference Example GetAsSVG(text) returns the example result (below) when the field text contains the word “Frank” and the word Frank has the following text attributes: Font = Helvetica, Font Size = 12 points, Font Color = red, Font Style = bold.
Chapter 12 | Text functions 163 GetAsTime Format GetAsTime(text) Parameter text - any text expression or text field containing a time Data type returned time Description Returns times or timestamps in text as data type time, for use with formulas involving the Time or Timestamp functions. The format of the supplied time must be the same as the time format on the system where the file was created. Use the GetAsTime or the Time function to enter a time constant into a formula.
164 FileMaker Functions Reference GetAsURLEncoded Format GetAsURLEncoded(text) Parameter text - any text expression or text field Data type returned text Description Returns text as URL (Uniform Resource Locator) encoding, for use as a URL. Removes all styles from text. All characters are first converted to UTF-8 format. Characters that are neither letters nor digits, or digits that are in the upper ASCII range, are converted to %HH format (a percent sign followed by the character’s hexadecimal value).
Chapter 12 | Text functions 165 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.
166 FileMaker Functions Reference 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.
Chapter 12 | Text functions 167 Katakana Format Katakana(text) Parameter text - any text expression or text field Data type returned text Description Converts from Hiragana to Zenkaku Katakana. Example Katakana(“ “) returns Left Format Left(text;numberOfCharacters) Parameters text - any text expression or text field numberOfCharacters - any numeric expression or field containing a number Data type returned text Description Returns the numberOfCharacters in text, counting from the left.
168 FileMaker Functions Reference LeftValues Format LeftValues(text;numberOfValues) Parameters text - any text expression or text field numberOfValues - any numeric expression or field containing a number Important See Design functions, page 35 for information about literal text parameters. Data type returned text Description Returns a text result containing the specified numberOfValues from the list of values in text, counting from the left. Values are text items separated by carriage returns.
Chapter 12 | Text functions 169 Data type returned text Description Returns a text result containing the numberOfWords in text, counting from the left. Note 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.
170 FileMaker Functions Reference 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.
Chapter 12 | Text functions 171 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 Important See Design functions, page 35 for information about literal text parameters.
172 FileMaker Functions Reference 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. Note The ampersand (&) and hyphen (-) characters identify the beginning of a new word.
Chapter 12 | Text functions 173 Description Converts Roman numbers in number to Japanese text. If the value for separator and characterType are blank or other than 0 to 3, then 0 is used.
174 FileMaker Functions Reference Examples PatternCount(“Mississippi”;“is”) returns 2. PatternCount(“Mississippi”;“issi”) returns 1 (the function isn’t inclusive). PatternCount(Attending;“Guest”) returns 1 if the Guest checkbox is one of the items selected in the Attending field. 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.
Chapter 12 | Text functions 175 Proper Format Proper(text) Parameter text - any text expression or text field 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.
176 FileMaker Functions Reference 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. numberOfCharacters - any numeric expression or field containing a number representing the number of characters to remove from text. replacementText - any text expression or field containing the text to replace in the original string.
Chapter 12 | Text functions 177 Data type returned text Description Returns the specified numberOfCharacters in text, counting from the right. Examples Right(“Manufacturing”;4) returns ring. Right(Name;Length(Name) - Position(Name;“ “;1;1)) returns Cannon, when the Name field contains Michelle Cannon. Right(SerialNumber;3) & Upper(Left(LastName;4)) returns 187FERR when the SerialNumber text field contains 00-48-187 and LastName contains Ferrini.
178 FileMaker Functions Reference Examples RightValues(“Plaid¶Canvas¶Suitcase”;2) returns Canvas Suitcase RightValues(names;1) returns John when the names field contains Sophie Bill John RightWords Format RightWords(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 right.
Chapter 12 | Text functions Data type returned text Description Converts from Zenkaku alphanumeric and symbols to Hankaku alphanumeric and symbols. Example RomanHankaku(“M a c i n t o s h”) returns Macintosh RomanZenkaku Format RomanZenkaku(text) Parameter text - any text expression or text field Data type returned text Description Converts from Hankaku alphanumeric and symbols to Zenkaku alphanumeric and symbols.
180 FileMaker Functions Reference 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. If the incrementBy value is a decimal number, then only the integer portion of incrementBy value is added to the last number in text. Any character other than a number is considered a separator.
Chapter 12 | Text functions 181 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. Multiple substitutions are allowed when you enclose each pair of searchString and replaceString parameters within square brackets [ ] and separate them with semicolons. Each search and replace list item is also separated by semicolons.
182 FileMaker Functions Reference TrimAll Format TrimAll(text;trimSpaces;trimType) Parameters text - any text expression or text field trimSpaces - 0 or False, 1 or True trimType - 0 through 3 depending on the trim style that you wish to use Data type returned text Description Returns a copy of text with all leading and trailing spaces removed. Set trimSpaces to True (1) if you want to include the removal of full-width spaces between non-Roman and Roman characters.
Chapter 12 | Text functions 183 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).
184 FileMaker Functions Reference Examples Upper(“Ca”) returns CA. Upper(“12n34p”) returns 12N34P. ValueCount Format ValueCount(text) Parameter text - any text expression or text field Important See Design functions, page 35 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.
Chapter 12 | Text functions 185 Description Returns a count of the total number of words in text. Examples WordCount(“The sun is rising.”) returns 4. WordCount(Letter) returns the total number of words in the Letter field. Note The ampersand (&) and hyphen (-) characters identify the beginning of a new word.
186 FileMaker Functions Reference
Chapter 13 | Text formatting functions 187 Chapter 13 Text formatting functions Text formatting functions can be used to change the color, font, size, and style of the specified text. For example, you could use the TextFont function to change the font of the specified text from Arial to Courier. Text formatting functions operate on these parameters: • fields of type text • text constants (in quotes) • expressions having a text result Click a function name for details.
188 FileMaker Functions Reference 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. Numbers returned by this function can be passed as the color parameter in the TextColor or TextColorRemove functions.
Chapter 13 | Text formatting functions 189 Description Changes the color of text to the color specified by the RGB function. Note Text formatting options will be lost if the data type that is returned is something other than text. Tip To determine the RGB value of a color: • (Windows) In Layout mode, click the Fill Color palette and choose Other Color. Values are shown for each of the basic colors. • (Mac OS) Start the DigitalColor Meter application in the Applications/Utilities folder.
190 FileMaker Functions Reference Examples TextColorRemove(“Red Text and Green Text”) returns Red Text and Green Text displayed in the field’s default font color. TextColorRemove(“Red Text and Green Text”;RGB(255;0;0)) returns Red Text and Green Text with only the pure red font color removed from the words Red Text. TextFont Format TextFont(text;fontName{;fontScript}) Parameters text - any text expression or text field. fontName - any font name expressed in text.
Chapter 13 | Text formatting functions 191 Description Changes the font of text to the specified fontName or optional {fontScript}. Spellings for font names must be correct and are case-sensitive. Text formatting options will be lost if the data type that is returned is something other than text. FileMaker Pro looks for a font that matches the specified font name and font script character set.
192 FileMaker Functions Reference Note The fontScript parameter is not enclosed in quotation marks (“ “), and can have any of the values listed below in Description. Data type returned text Description Removes all fonts in text, or removes the font specified by fontToRemove or the combination of fontToRemove and fontScript. If you don’t specify a font, all of the text displays in the default font that was set in Layout mode for the field.
Chapter 13 | Text formatting functions 193 Examples TextFontRemove(“Arial Text and Courier Text”) returns Arial Text and Courier Text displayed in the field’s default font. TextFontRemove(“Arial Text and Courier Text”;“Arial”) returns Arial Text and Courier Text with the Arial font removed from the words Arial Text for all fontScripts that use the Arial font.
194 FileMaker Functions Reference Description Changes the font size of the specified text to fontSize. The font size is described in points (72 points to the inch). Text formatting options will be lost if the data type that is returned is something other than text. Examples TextSize(“Plaid”;18) returns the word Plaid in 18 point text. TextSize(“Plaid”;24) returns the word Plaid in 24 point text.
Chapter 13 | Text formatting functions 195 TextStyleAdd Format TextStyleAdd(text;styles) Parameters text - any text expression or text field styles - any named style listed below in Description Data type returned text Description Adds the specified styles to text in a single action. You can add multiple styles by using the + operator between style names. Negative values are not valid. All styles will be removed, if the only style specified is Plain. Plain is ignored if mixed with other styles.
196 FileMaker Functions Reference 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. TextStyleAdd(TextStyleAdd(FirstName;Plain);Italic) The following calculation creates two descriptions of styles, then concatenates two phrases using these styles.
Chapter 13 | Text formatting functions 197 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.
198 FileMaker Functions Reference
Chapter 14 | Time functions 199 Chapter 14 Time functions Time functions calculate times and manipulate time information. Click a function name for details. This function Returns Hour, page 199 A number representing the number of hours in a time value. Minute, page 200 A number representing the number of minutes in a time value. Seconds, page 200 A number representing the number of seconds in a time value. Time, page 201 A time result with the specified number of hours, minutes, and seconds.
200 FileMaker Functions Reference Minute Format Minute(time) Parameter time - any time value or field of type time Data type returned number Description Returns a number representing the number of minutes in time. Examples Minute(“12:15:23”) returns 15. Hour(Duration) + (Minute(Duration)/60) returns 2.5, if the Duration time field contains 2:30:15.
Chapter 14 | Time functions 201 Time Format Time(hours;minutes;seconds) Parameters hours - the hour value of a time minutes - the minutes value of a time seconds - the seconds value of a time Data type returned time Description Returns a time result with the specified number of hours, minutes, and seconds. FileMaker Pro compensates when you supply fractional hours or minutes. The result is the time, formatted according to the time format of the field in the current layout.
202 FileMaker Functions Reference
Chapter 15 | Timestamp functions 203 Chapter 15 Timestamp functions Timestamps are used for a wide variety of synchronization purposes, such as marking the exact date and time at which a particular event occurred. This function Returns Timestamp, page 203 A timestamp containing a calendar date and time of day.
204 FileMaker Functions Reference
Chapter 16 | Trigonometric functions 205 Chapter 16 Trigonometric functions Trigonometric functions are used to calculate degrees, angles, and other geometric data. Note All trigonometric functions use radians as the unit of measure. Once you have a result, you can convert the radians into degrees using the Degrees function. Click a function name for details. This function Returns Acos, page 205 The arccosine, or inverse cosine, of a number. Asin, page 206 The arcsine, or inverse sine, of a number.
206 FileMaker Functions Reference Examples Acos(-0.5) returns 2.0943951. Acos(-0.5)*180/Pi returns 120. Degrees(Acos(-0.5)) returns 120. Acos(2.0) ? (not a number). Asin Format Asin (number) Parameter number - any numeric expression or field containing a numeric expression in the range -1 to 1. Data type returned number Description Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -Pi/2 to Pi/2.
Chapter 16 | Trigonometric functions 207 Data type returned number Description Returns the trigonometric arc tangent (inverse tangent) of number. The arc tangent is the angle, in radians, whose tangent is equal to the specified number. Examples Atan(1) returns .78539816.... Degrees(Atan(1)) returns 45. Cos Format Cos(angleInRadians) Parameter angleInRadians - any numeric expression or field containing a numeric expression, in radians.
208 FileMaker Functions Reference Data type returned number Description Converts angleInRadians to degrees. Use this function to translate results from trigonometric functions from radians to degrees. • ανγλεΙνΡαδιανσ Δεγρεεσ = 180 --------------------------------------------------------π Examples Degrees(Atan(1)) returns 45. Degrees(1.0472) returns 60.00014030.... Pi Format Pi Parameter None Data type returned number Description Calculates the value of the constant Pi (π), which is approximately 3.
Chapter 16 | Trigonometric functions 209 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. • ανγλεΙνΔεγρεεσΡαδιανσ = π ------------------------------------------------180 Examples Radians(45) returns .78539816.... Sin(Radians(30)) returns .5.
210 FileMaker Functions Reference Tan Format Tan(angleInRadians) Parameter angleInRadians - any numeric expression or field containing a numeric expression, in radians. Data type returned number Description Returns the tangent of angleInRadians. Note With the Tan function, you cannot use values exactly equal to 90 degrees (Pi/2 radians), or multiples of 90 degrees. Σιν(ανγλεΙνΡαδιανσ)Ταν = --------------------------------------------------------Χοσ(ανγλεΙνΡαδιανσ) Examples Tan(.13) returns .13073731....
Appendix A | Glossary 211 Appendix A Glossary A Access key (Windows) A key that activates a menu, menu item, or control when used with the ALT key. In Windows, this key corresponds to the underlined letter on a menu, command, or dialog box option. Access privileges Permission to view and work with certain records, fields, layouts, value lists, and scripts and to perform selected activities in a file. Account A username and (usually) password that accesses a file with a defined level of privileges.
212 FileMaker Functions Reference Authentication The process of checking the validity of an account and password (if one is defined) before assigning privileges and allowing access to a system or a database file. An account authenticated via FileMaker Pro or FileMaker Server is referred to as a FileMaker Account. (FileMaker Server can also authenticate an account via External Server -- an external authentication system such as Apple Open Directory, or a Windows Domain.
Appendix A | Glossary 213 Browse mode The FileMaker mode in which you enter and edit information in fields. Groups of fields make up the records of your database. You can either view one record at a time (choose View menu > View as Form), or view your records in a list (choose View menu > View as List), or view records arranged in a spreadsheet-like table (choose View menu > View as Table). (Use Browse mode to enter and edit your information; use Layout mode to design how your information is displayed.
214 FileMaker Functions Reference Client A user that opens a database file that is shared on a network, published in a browser, or shared via ODBC/JDBC. FileMaker Network settings and privileges determine how clients interact with databases hosted through FileMaker Pro, FileMaker Server, and FileMaker Server Advanced. Client application The application that requests data (using SQL) from a data source (using ODBC or JDBC).
Appendix A | Glossary 215 Container data type Pictures, sounds, QuickTime movies, OLE objects (Windows), and files of any type can be inserted in a container field. Context The starting point or perspective from which calculations and scripts are begun, and from which a relationship is evaluated in the relationships graph. Convert Opening a data file from another application, which creates a new FileMaker Pro file containing the data.
216 FileMaker Functions Reference Database file A collection of information in a file containing one or more tables pertaining to a subject, such as customers or invoices. (A large database can also comprise many database files.) Database Management System (DBMS) An application that allows users to store, process, and retrieve information in a database. Descending sort order Reverse alphabetical sequence (Z to A) for words, highest to lowest order for numbers, and latest to earliest dates and times.
Appendix A | Glossary 217 DTD (Document Type Definition) A formal description of a particular type of XML. It defines a document structure, including the names of data elements and where they may occur within the structure. Valid XML conforms to the rules established in its DTD. XML parsers (such as Xerces) can check the validity of XML to its DTD. E Email Electronic mail. A system for transmitting messages from one computer or terminal to another.
218 FileMaker Functions Reference External function A function written in C or C++ as part of a third party plug-in that extends the feature set of FileMaker Pro or FileMaker Pro Advanced External script A script used by a database file, but defined in a different database file. Use the Perform Script script step to select a defined script from a related file, or to select a file reference to a database file on your hard drive or network. F Field The basic unit of data in a record.
Appendix A | Glossary 219 File path The location of a file in an operating system as identified by the drive, folders, filename, and file extension. FileMaker Network A communications method built into FileMaker Pro that allows you to share FileMaker Pro files hosted by FileMaker Pro or FileMaker Server with others over a network. The FileMaker Network settings and privileges you set up determine how other users (called "clients") can open and use the shared file.
220 FileMaker Functions Reference Fully qualified name The complete name of a field or layout, expressed using the format tableName::[field or layout name], where “tableName” is the name of the underlying table occurrence in the relationships graph upon which the field or layout is based. A fully qualified name identifies an exact instance of a field or layout.
Appendix A | Glossary 221 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. The Guest account is assigned a privilege set that determines what guests can do in the file. Guest access may be disabled for a file. H Handle One of the small squares at the corners of a selected object used to resize and reshape the object.
222 FileMaker Functions Reference 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. Instant Web Publishing A method of sharing your FileMaker databases with other users via a web browser.
Appendix A | Glossary 223 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.
224 FileMaker Functions Reference Learn buttons Linked directly to context-sensitive Help topics from dialog boxes to learn more about using them: Web Viewer Setup, Button Setup, Specify Calculation, and New Layout/ Report. LDAP (lightweight directory access protocol) A protocol for accessing online directory services. Link On a web page, text or a graphic which -- when you click it -- displays an associated web page or a specific element within a page.
Appendix A | Glossary 225 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.
226 FileMaker Functions Reference 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.
Appendix A | Glossary 227 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.
228 FileMaker Functions Reference 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.
Appendix A | Glossary 229 R Read-Only Access privilege set One of the three pre-defined privilege sets that appear in every file. The Read-Only Access privilege set allows read access to the records in a file, but not write or design access. 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.
230 FileMaker Functions Reference When you create a new table, a visual representation, or occurrence, of the table appears in the relationships graph. You can specify multiple occurrences (with unique names) of the same table in order to work with complex relationships in the graph. Repeating field A field containing multiple, separate values. Report with grouped data A subsummary report that you create using the Columnar List/Report layout type.
Appendix A | Glossary 231 Self-join A relationship between fields in the same table. This creates another occurrence of the table in the relationships graph. Separator A line within a menu that separates or groups menu items. 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.
232 FileMaker Functions Reference Sort order The sequence for rearranging records. Records are sorted by the first field in the sort order list, then the second, and so on. Values within each field are sorted by the order specified (ascending, descending, or custom). Source file The file from which you bring data during importing or exporting, or the file from which you add a table to the relationships graph. Source table The table upon which one or more tables in the relationships graph are based.
Appendix A | Glossary 233 Subsummary parts Use summary parts to view and display information from one or more records. You place a summary field in a summary part to display a summary of information for each group of records sorted on the break field. You can add one or more subsummaries above (leading) or below (trailing) the body. Subsummary value Aggregate values for different categories of data within a field. For example, a subsummary value can be the total of employees for each department.
234 FileMaker Functions Reference Tab panel A component of a tab control. The tab panel is the area displayed when a tab in a tab control is selected. You can place objects such as lines, fields, buttons, portals, imported graphics, blocks of text, tab controls, and web viewers in tab panels. Table A collection of data pertaining to a subject, such as customers or stock prices. A database file contains one or more tables, which consist of fields and records.
Appendix A | Glossary 235 Tool panel In the status area in Layout mode, the collection of tools that includes the selection tool (pointer), text tool, line tool, rectangle tool, rounded rectangle tool, oval tool, field/control tool, portal tool, tab control tool, web viewer tool, and button tool. If you don't see the status area, click the status area control at the bottom of the document window. Toolbar Use items in the toolbar to access many FileMaker Pro commands.
236 FileMaker Functions Reference V Value list To save time and ensure accuracy during data entry, define frequently used text, number, date, or time values as a value list. When you enter data, you can choose from the list of defined values. You can format value lists to display in a drop-down list or pop-up menu, or as checkboxes or option (radio) buttons. The values in a value list can be user-defined or based on the values in a field in the same file or in a different file.
Appendix A | Glossary 237 Website 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. Web viewer A layout object that allows you to display information from websites based on data in your database. World Wide Web An interlinked collection of web pages residing on web servers, and other documents, menus, and databases, which are available via URLs.
238 FileMaker Functions Reference
