Primer for Claris Studio calculations

This primer includes a quick list of the differences between calculations in FileMaker Pro and Claris Studio, a list of supported Claris Studio calculation functions, and some known issues. We’ll have more information to share as we progress.

Differences between FileMaker Pro and Claris Studio calculations

The Claris Studio calculation engine leverages common calculation formatting so it is easier for users to build and automate more complex solutions and processes.

	FileMaker Pro	Claris Studio
Mathematical operators
Adds two values	+	+
Subtracts the second value from the first	-	-
Multiplies each value	*	*
Divides the first value by the second	/	/
Raises the first value to the power of the second value	^	**
Sets precedence for order of operations	( )	( )
Comparison operators
True if the items are equal	=	==
True if the items are not equal	≠	!=
True if the value on the left exceeds that on the right	>	>
True if the value on the left is less than the value on the right	<	<
True if the value on the left is greater than or equal to the value on the right	≥	>=
True if the value on the left is less than or equal to the value on the right	≤	<=
Logical operators
True only if both items are true	AND	&&
True if either item is true	OR	||
True if either of the expressions (but not both) is true	XOR	XOR()
Changes the value from False to True or from True to False	NOT	!
Text operators
Appends the text string on the right to the end of the text string on the left	&	+
Marks the beginning and the end of characters to be considered a text constant	" "	' ' or " "
Empty value	""	'' (single quotes) or "" (double quotes)
Marks an operator character to be used as a character instead of an operator	\	Not available
Inserts a paragraph carriage return in the result of a formula	¶	Not available
Marks the beginning and the end of characters to be considered text comments within a formula	/* */	/* */
Identifies a single-line comment within a formula	//	//
Field references
References a field in the current table	Field1	TableA.Field1
References a field from a different table	TableB::FieldX	Not available
Additional operators
Bitwise AND	Not available	&
Bitwise OR	Not available	|
Bitwise XOR	Not available	^
Bitwise signed right shift	Not available	>>
Zero-fill right shift	Not available	>>>
Zero-fill left shift	Not available	<<
Template string	Not available	` `

Claris Studio calculation functions

These are the calculation functions currently supported in Claris Studio. More are on the way. For more information, see the in-line descriptions in the calculation editor.

	FileMaker Pro	Claris Studio
Math functions
ABS	Abs ( number )	ABS ( value )
CEILING	Ceiling ( number )	CEILING ( value, [factor] )
EVEN	Not available	EVEN ( value )
EXP	Exp ( number )	EXP ( value )
FLOOR	Floor ( number )	FLOOR ( value, [factor] )
INT	Int ( number )	INT ( value )
LOG	Log ( number )	LOG ( value, [base] )
MOD	Mod ( number ; divisor )	MOD ( dividend, divisor )
ODD	Not available	ODD ( value )
POWER	Not available	POWER ( base, exponent )
ROUND	Round ( number ; precision )	ROUND ( value, [places] )
ROUNDDOWN	Not available	ROUNDDOWN ( value, [places] )
ROUNDUP	Not available	ROUNDUP ( value, [places] )
SQRT	Sqrt ( number )	SQRT ( value )
SUM	Sum ( field {; field...} )	SUM ( number1, [number2, ...] )
MAX	Max ( field {; field...} )	MAX ( number1, [number2, ...] )
MIN	Min ( field {; field...} )	MIN ( number1, [number2, ...] )
Logical functions
AND	Not available	AND ( exp1, [exp2, ...] )
FALSE	Not available	FALSE ( )
IF	If ( test ; result1 {; result2 } )	IF ( logical expression, value_if_true, [ value_if_false ] )
NOT	Not available	NOT ( exp )
OR	Not available	OR ( exp1, [exp2, ...] )
TRUE	Not available	TRUE ( )
XOR	Not available	XOR ( exp1, [exp2, ...] )
Claris functions
GET	Get ( )	GET(predefined_variable) predefined_variable: AccountName, RecordID, UserID, or UserName
GETFILEATTRIBUTE	GetContainerAttribute ( field ; attributeName )	GETFILEATTRIBUTE ( table_field, [attribute_name] )
Information functions
ISBLANK	IsEmpty ( field )	ISBLANK ( expression )
ISERROR	EvaluationError ( expression )	ISERROR ( expression )
ISEVEN	Not available	ISEVEN ( expression )
ISLOGICAL	GetAsBoolean ( data )	ISLOGICAL ( expression )
ISNUMBER	Not available	ISNUMBER ( expression )
ISODD	Not available	ISODD ( expression )
ISTEXT	Not available	ISTEXT ( expression )
TYPE	Not available	TYPE ( expression )
Date and time functions
DATE	Date ( month ; day ; year )	DATE ( year, month, day )
DATEVALUE	Not available	DATEVALUE ( date_text )
DAY	Day ( date )	DAY ( date_serial_num )
DAYS	Not available	DAYS ( end_date, start_date )
HOUR	Hour ( time )	HOUR ( time_serial_num )
MINUTE	Minute ( time )	MINUTE ( time_serial_num )
MONTH	Month ( date )	MONTH ( date_serial_num )
NOW	Not available	NOW ( )
SECOND	Seconds ( time )	SECOND ( date )
TODAY	Not available	TODAY ( )
TO_DATE	Not available	TO_DATE ( date_serial_num )
WEEKDAY	DayOfWeek ( date )	WEEKDAY ( date_serial_num, [ return_type ] )
WEEKNUM	WeekOfYear ( date )	WEEKNUM ( date_serial_num, [ return_type ] )
WORKDAY	Not available	WORKDAY ( start_date, num_days, [holidays] )
YEAR	Year ( date )	YEAR ( date )
Text functions
CONCATENATE	Not available	CONCATENATE ( value, [value, ...] )
FIND	Not available	FIND ( search, text, [starting] )
JOIN	Not available	JOIN ( delimiter, [value, ...]
LEFT	Left ( text ; numberOfCharacters )	LEFT ( value, [number] )
LEN	Length ( text )	LEN ( value )
LOWER	Lower ( text )	LOWER ( value )
MID	Middle ( text ; start ; numberOfCharacters )	MID ( value, starting, length )
REGEXEXTRACT	Not available	REGEXEXTRACT ( value, regex )
REGEXMATCH	Not available	REGEXMATCH ( value, regex )
REGEXREPLACE	Not available	REGEXREPLACE ( value, regex, replacement )
REPLACE	Replace ( text ; start ; numberOfCharacters ; replacementText )	REPLACE ( text, position, length, newText )
REPT	Not available	REPT ( text, repetition )
RIGHT	Right ( text ; numberOfCharacters )	RIGHT ( value, [number] )
SEARCH	Position ( text ; searchString ; start ; occurrence )	SEARCH ( search_for, text_to_search, [starting_at] )
SUBSTITUTE	Substitute ( text ; searchString ; replaceString )	SUBSTITUTE ( text, search, replace, [occurrence] )
T	GetAsText ( data )	T ( value )
TRIM	Trim ( text )	TRIM ( value )
UPPER	Upper ( text )	UPPER ( value )
VALUE	GetAsDate ( text ), GetAsNumber ( text ), GetAsTime ( text )	VALUE ( value )

Notes 

• To reference a field in Claris Studio, use the format TableName.FieldName. Unsure where to find a view’s table name? Currently, you can only see a table name in a spreadsheet view (just above the first column). If you don’t yet have a spreadsheet view using the same data as your current view, you can create one.

• While Claris Studio does not allow you to specify a result’s data type (for example, number, text, date), Claris Studio will try to choose an appropriate data type. For example, during concatenation, if a string is detected (either from a text field or a text constant), then Claris Studio will return a string. If two numbers are detected, Claris Studio will return a number.

• The template string operator makes it easier to write an expression that includes multiple text constants (literal text), fields, and functions.

  Example: `Text constant ${Function or Field reference} Text constant ${Function or Field reference}`

• Default browser whitespace collapse behavior will display multiple spaces in a text constant (for example, "       ") as a single space. The string value is not affected.

Top known issues

• References to fields from a different table aren't available.

• Date, time, and timestamp fields are supported, but you may need to set the calculation field's Format option to Date, Time, or Date and Time so that the calculation result is shown as expected. For example, this expression adds a week to the value in a date field and, if Format is set to Date, it returns the result as a date:

  Tasks.Date + 7

• In the Object Actions dialog, setting a field's value using the calculation engine is supported only in short text, long text, rich text, number, email, and currency field objects for now.