Formula Cheat Sheet

A quick cheat sheet to get things done with formulas

Remember that you can evaluate your formulas directly through the UI.

Show How

General computing

Simple Conditions

if condition then result else else_result

For example

> x: 100
100

> if x < 999 then "low" else "high"
"low"

> if x == 100 then "congrats" else "not cigar"
"congrats"

Both the then and the else keywords are optional

> if x < 999 "low" "high"
"low"

Leaving out keywords can make your formula more readable if you want to check a series of conditions until the first one is true:

if age <= 1   "baby"
if age <= 3   "toddler"
if age <= 5   "preschool"
if age <= 12  "gradeschooler"
if age <= 18  "teen"
if age <= 21  "young adult"
else          "adult"

Complex Conditions

For value mappings, consider pattern matching

match my_prefix
  "D" -> "IT Development",
  "T" -> "Telecommunications",
  "S" -> "Services",
  default ->  "N/A"

Pattern matching is very powerful. You can implement complex conditions on nested data structures, using structural patterns, predicate functions, and guard expressions.

Access data in a nested list or dict

Use the container access operator [] You can chain them to navigate deep structures.
Assuming the variable person has the following value:

{
  :name "Sherlock Holmes",
  :location {
    :latitude 51.523767,
    :longitude -0.1591042
  }
}

You can access the latitude like this:

person["location"]["latitude"]

Working with strings

Concatenate strings

You can use the string concatenation operator ..

> "hello" .. " - " .. "world"
"hello - world"

Join strings in a list

Use strings.join

> strings.join(["a", "b", "c"], "-")
"a-b-c"

Uppercase or lowercase a string

Use strings.upper_case or strings.lower_case

> strings.upper_case("hello")
"HELLO"

> strings.lower_case("HELLO")
"hello"

Split a a string by a separator

If the separator is static, use strings.split

> strings.split("one,two,three,four", ",")
["one", "two", "three", "four"]

If the separator is a regex pattern, or you want to limit the splitting process, use regex.splitting

Tip: use single quotes to define the pattern. You’ll only have to worry about escaping other single quotes within your pattern. If you use double quotes, you will have to escape backslashes, leading to less readable patterns.

> regex.splitting('\D+')("10 cats, 20 dogs, and 40 sheep.")
["10", "20", "40"]

If you wish to retain the separator match in the results, use lookahead and lookbehind

> regex.splitting('(?=/)|(?<=/)')("alpha/beta/gamma")
["alpha", "/", "beta", "/", "gamma"]

Everything you need to know about regular expressions

Working with lists

Add an item to a list

You can use the splat operator to create a new list that contains all items in your list and a new item.

> xs: [1, 2, 3]
[1, 2, 3]

> [...xs, 4]
[1, 2, 3, 4]

> [0, ...xs]
[0, 1, 2, 3]

Working with datetimes

Extract calendar information from a datetime

You can use various functions from the standard library such as time.month

> time.month(2017-02-21T)
2

You can also cast the datetime to a dict, which gives you access to all parts of the datetime at once:

> d: 2017-07-23T23:12:32.298+02:00@Europe/Berlin
2017-07-23T23:12:32.298+02:00@Europe/Berlin

> parts: d as dict
{
  :month 7,
  :day_of_year 204,
  :hour 23,
  :zone "Europe/Berlin",
  :nano_of_second 298000000,
  :offset_seconds 7200,
  :day_of_week 7,
  :week_of_year 29,
  :day_of_month 23,
  :year 2017,
  :second 32,
  :minute 12
}

You can then access the parts individually as needed

> parts["year"]
2017

> parts["month"]
7

> parts["day_of_month"]
23

Calcuate the time period between datetimes

Use one of the between functions in the time library such as time.years_between, time.months_between, time.days_between etc.

> time.years_between(1981-08-16T, 2020-01-23T)
38

> time.months_between(1981-08-16T, 2020-01-23T)
461

> time.days_between(1981-08-16T, 2020-01-23T)
14039

For a consolidated calendar distance use time.period_between

> time.period_between(1981-08-16T, 2020-01-23T)
{
  :years 38,
  :months 5,
  :days 7
}