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 2+2 or 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.
Types
The following is a quick overview of ActivityInfo's formula types:
| Type | Description | Examples |
|---|---|---|
| String | A "string" of characters | "Alice", 'Bob' |
| Number | A real-valued number | 42, 1.5, -3000 |
| Boolean | Either true or false | TRUE, FALSE |
| Local Date | A year, month, and day | DATE(2022,1,15) |
| Week | A year and week number | WEEKVALUE("2022W3") |
| Month | A year and month | |
| Quarter | A year and a quarter | |
| Instant | A specific moment in time | _lastEditTime |
The following section provides more details these types.
String
String values in formulas, like in ActivityInfo forms, are a set of a Unicode characters.
Number
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.
Local date
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.
Blank values
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.
Field types and formula types
The following table provides a summary of the correspondence between different types of fields in ActivityInfo forms and formula types:
| Field type | Formula Type |
|---|---|
| Serial number | String |
| Quanitity | Number |
| Text | String |
| Multi-line text | String |
| Date | Local date |
| Week | Week |
| Fortnight | Week |
| Month | Month |
| Single Selection | String |
| Multiple selection | String |
| Attachment | Struct |
| Calculated | Depends on formula |
| Reference | String |
| Geographic point | Struct |
| User | String |
| Section header | No value |
| Subform | No value |
| Barcode | String |
Converting between types
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
Converting to text
The TEXT function converts its argument to the string.