Expressions

Functions

Every function you can use in an expression, by category. Function names aren't case-sensitive. In the expression box, the helper shows each function's arguments as you type and lists them all under Functions & fields. This page is generated from appTown itself, so it is always complete.

#Logic

#AND

AND(a, b, …) → yes/no

Yes when all of them are yes.

AND([Paid], [Shipped])

#COALESCE

COALESCE(a, b, …) → the first non-blank value

The first of them that isn't blank.

COALESCE([Nickname], [Name])

#IF

IF(condition, then, else) → the then or else value

Picks a value: then when the condition is yes, else otherwise.

IF([Qty] > 10, "Bulk", "Single")

#IFS

IFS(condition1, value1, condition2, value2, …) → the first matching value

The value of the first condition that is yes (blank when none is).

IFS([Score] >= 90, "A", [Score] >= 80, "B", TRUE, "C")

#ISBLANK

ISBLANK(value) → yes/no

Yes when the value is empty.

ISBLANK([Email])

#ISNOTBLANK

ISNOTBLANK(value) → yes/no

Yes when the value is not empty.

ISNOTBLANK([Phone])

#NOT

NOT(a) → yes/no

Turns yes into no and no into yes.

NOT([Done])

#OR

OR(a, b, …) → yes/no

Yes when any of them is yes.

OR([Status] = "Open", [Urgent])

#SWITCH

SWITCH(value, match1, result1, default, …) → the matching result

Compares a value with each match and gives that result, or the default.

SWITCH([Status], "Open", 1, "Done", 2, 0)

#Math

#ABS

ABS(number) → number

The number without its sign.

ABS([Balance])

#AVERAGE

AVERAGE(list, …) → number

The average of a list of numbers.

AVERAGE([Scores])

#CEILING

CEILING(number) → number

Rounds up to a whole number.

CEILING([Qty] / 12)

#COUNT

COUNT(list) → number

How many items a list has.

COUNT([Tags])

#FLOOR

FLOOR(number) → number

Rounds down to a whole number.

FLOOR([Hours])

#MAX

MAX(list or a, b, …) → the largest value

The largest of a list, or of several values.

MAX([Start], TODAY())

#MIN

MIN(list or a, b, …) → the smallest value

The smallest of a list, or of several values.

MIN([Price], 100)

#MOD

MOD(number, divisor) → number

What's left after dividing.

MOD([Number], 2)

#POWER

POWER(number, exponent) → number

The number raised to the exponent.

POWER([Side], 2)

#ROUND

ROUND(number, decimals) → number

Rounds to that many decimals (0 when left out).

ROUND([Price] * 1.2, 2)

#SUM

SUM(list, …) → number

Adds up a list of numbers.

SUM(SELECT(Orders[Total], [Customer] = [_THISROW]))

#Text

#CONCATENATE

CONCATENATE(a, b, …) → text

Joins values into one text (the same as &).

CONCATENATE([First], " ", [Last])

#CONTAINS

CONTAINS(text, part) → yes/no

Yes when the text contains the part (ignoring case).

CONTAINS([Notes], "urgent")

#ENDSWITH

ENDSWITH(text, end) → yes/no

Yes when the text ends with that.

ENDSWITH([Email], "@example.com")

#LEFT

LEFT(text, count) → text

The first characters of the text.

LEFT([Postcode], 2)

#LEN

LEN(text) → number

How many characters the text has.

LEN([Code]) = 6

#LOWER

LOWER(text) → text

The text in lower case.

LOWER([Email])

#MID

MID(text, start, count) → text

Characters from the middle (the first character is 1).

MID([Code], 3, 2)

#RIGHT

RIGHT(text, count) → text

The last characters of the text.

RIGHT([Phone], 4)

#SPLIT

SPLIT(text, separator) → list of text

Splits the text into a list.

SPLIT([Tags], ",")

#STARTSWITH

STARTSWITH(text, start) → yes/no

Yes when the text starts with that.

STARTSWITH([Phone], "+44")

#SUBSTITUTE

SUBSTITUTE(text, old, new) → text

Replaces every old part with the new one.

SUBSTITUTE([Phone], " ", "")

#TEXT

TEXT(value, format) → text

A value as text, optionally formatted (dates: DD MM YYYY; numbers: 0.00).

TEXT([Due], "DD/MM/YYYY")

#TRIM

TRIM(text) → text

The text without spaces at the start and end.

TRIM([Name])

#UPPER

UPPER(text) → text

The text in upper case.

UPPER([Code])

#Date & time

#DATE

DATE(year, month, day) → date

Makes a date from its parts.

DATE(YEAR(TODAY()), 12, 31)

#DATEADD

DATEADD(date, amount, unit) → date

Adds an amount of "day", "week", "month", "year", "hour" or "minute".

DATEADD([Start], 30, "day")

#DATEDIFF

DATEDIFF(start, end, unit) → number

How many whole units from start to end.

DATEDIFF([Start], TODAY(), "day")

#DAY

DAY(date) → number

The day of the month.

DAY([Start])

#EOMONTH

EOMONTH(date, months) → date

The last day of the month, months later (0 = this month).

EOMONTH(TODAY(), 0)

#HOUR

HOUR(date and time) → number

The hour (0 to 23).

HOUR([Starts at])

#MINUTE

MINUTE(date and time) → number

The minute (0 to 59).

MINUTE([Starts at])

#MONTH

MONTH(date) → number

The month of a date (1 to 12).

MONTH([Start])

#NOW

NOW() → date and time

The current date and time.

NOW()

#TODAY

TODAY() → date

Today, in the person's timezone.

[Due] < TODAY()

#WEEKDAY

WEEKDAY(date) → number

The day of the week: 1 = Sunday … 7 = Saturday.

WEEKDAY([Start]) = 2

#YEAR

YEAR(date) → number

The year of a date.

YEAR([Start])

#Person

#HASROLE

HASROLE(role) → yes/no

Yes when the person holds that role in this app.

HASROLE("Dispatcher")

#USEREMAIL

USEREMAIL() → text

The email of the person using the app.

[Assigned to] = USEREMAIL()

#USERNAME

USERNAME() → text

The name of the person using the app.

USERNAME()

#USERROLES

USERROLES() → list of text

The names of the roles the person holds.

COUNT(USERROLES()) > 0

#Lists

#ANY

ANY(list) → one item

One item of the list (the first).

ANY([Technicians])

#IN

IN(value, list) → yes/no

Yes when the list has the value.

IN([Status], {"Open", "Waiting"})

#INDEX

INDEX(list, n) → one item

The nth item (the first is 1).

INDEX([Tags], 1)

#LIST

LIST(a, b, …) → list

Makes a list (the same as {a, b}).

LIST("Open", "Waiting")

#SORT

SORT(list, descending) → list

The list sorted (descending when the second value is yes).

SORT([Scores], TRUE)

#UNIQUE

UNIQUE(list) → list

The list without repeats.

UNIQUE([Tags])

#Other tables

#FILTER

FILTER(table, condition) → list of links

Links to the rows of a table where the condition is yes; [_THISROW] is this record.

FILTER("Jobs", [Customer] = [_THISROW])

#INPUT

INPUT(path) → any

In an automation started by a webhook or API call: a value from the JSON it was sent.

INPUT("customer.email")

#LOOKUP

LOOKUP(value, table, field, return field) → one value

From the first row whose field equals the value, the return field.

LOOKUP([Code], "Products", "Code", "Name")

#SELECT

SELECT(Table[Field], condition) → list

A field's values from the rows where the condition is yes.

SELECT(Jobs[Price], [Customer] = [_THISROW])