Each expression in ActivityInfo's formula language has a specific type that determines which values the expression is allowed to have. For example, an expressions like
42 both have the type "number", which can include any real-valued number. The expression
CONCAT(FIRST_NAME, " ", LAST_NAME) on the other hand, has the type "string" its value will always be a combination of characters.
Activity's formula values are related, but distinct, from field types. Different form field types, like the text field and the serial number type, have very different behaviors with regard to data collection and management, but when used in formulas, they both have the "string" type.
Understanding ActivityInfo's formula types can be helpful in writing complex formulas. Certain functions, for example, can only be used with specific types.
The following is a quick overview of ActivityInfo's formula types:
|String||A "string" of characters||
|Number||A real-valued number||
|Boolean||Either true or false||
|Local Date||A year, month, and day||
|Week||A year and week number||
|Month||A year and month|
|Quarter||A year and a quarter|
|Instant||A specific moment in time||
The following section provides more details these types.
String values in formulas, like in ActivityInfo forms, are a set of a Unicode characters.
Number values in formulas include a real-valued numbers between +/- 1.8 x 10³⁰⁸. In practice, all numeric values are stored as 64-bit floating point numbers.
The local date type, which we generally refer to as "date" in the documentation, is a date without a time-zone in the Gregorian calendar (standardized as ISO-8601) calendar system, such as 2007-12-03.
ActivityInfo local date values are limited to the range between 1000-01-01 and 9999-12-31.
Each type also includes an exceptional "blank" value. ActivityInfo's blank value is similar to
NULL in SQL, the
NA value in R, or a blank cell in Excel.
The semantics of ActivityInfo's formula language is closer to Excel than SQL. For example, in an SQL query,
1 + NULL is
NULL, while in Excel
1 + A1 is
1, even if
A1 is blank. ActivityInfo adopts this more "forgiving" handling of blank values in arithmatic.
For more information, please refer to the article on blank values.
The following table provides a summary of the correspondence between different types of fields in ActivityInfo forms and formula types:
|Field type||Formula Type|
|Calculated||Depends on formula|
|Section header||No value|
In some cases, you may want to change a value from one type to another. For example, to change a numeric value to string value. The following functions provide
The TEXT function converts its argument to the string.