ToolsExpression Cheatsheet

Expression Cheatsheet

81 expressions — click to copy the example

text1 & text2TextPopular

Concatenates two text strings (use & operator)

FirstName & " " & LastName

Text(value, format)TextPopular

Converts a value to text with optional format

Text(Today(), "dd/mm/yyyy")

IsBlank(value)TextPopular

Returns true if the value is blank or empty string

If(IsBlank(TextInput1.Text), "Empty", "OK")

Now()Date & TimePopular

Returns the current date and time

Text(Now(), "dd/mm/yyyy hh:mm")

Today()Date & TimePopular

Returns the current date (no time)

Text(Today(), "dd/mm/yyyy")

DateAdd(date, n, unit)Date & TimePopular

Adds an interval to a date

DateAdd(Today(), 30, TimeUnit.Days)

DateDiff(date1, date2, unit)Date & TimePopular

Returns the difference between two dates

DateDiff(StartDate, EndDate, TimeUnit.Days)

Filter(table, condition)CollectionsPopular

Returns rows matching the condition

Filter(Employees, Department = "HR")

Sort(table, column)CollectionsPopular

Sorts a table by a column (ascending)

Sort(Products, Name)

LookUp(table, condition)CollectionsPopular

Returns the first record matching a condition

LookUp(Users, Email = User().Email)

Search(table, text, col)CollectionsPopular

Finds records where a column contains text

Search(Contacts, SearchInput.Text, "Name")

CountRows(table)CollectionsPopular

Returns the number of rows in a table

CountRows(Filter(Tasks, Status = "Open"))

Collect(col, record)CollectionsPopular

Adds records to a collection

Collect(MyCart, {Product: "Item A", Qty: 1})

ClearCollect(col, table)CollectionsPopular

Clears and repopulates a collection

ClearCollect(LocalItems, SharePointList)

Remove(col, record)CollectionsPopular

Removes a specific record from a collection

Remove(MyCart, ThisItem)

Patch(source, record, changes)CollectionsPopular

Creates or modifies a record in a data source

Patch(Employees, LookUp(Employees, ID=1), {Name: "Alice"})

ForAll(table, formula)CollectionsPopular

Evaluates a formula for each record in a table

ForAll(Items, Collect(Names, {Name: ThisRecord.Name}))

Distinct(table, column)CollectionsPopular

Returns unique values from a column

Distinct(Products, Category)

IsEmpty(table)CollectionsPopular

Returns true if the table has no records

If(IsEmpty(MyCart), "Empty cart", "Items in cart")

Sum(table, column)CollectionsPopular

Returns the sum of a numeric column

Sum(Orders, TotalPrice)

If(condition, true, false)LogicPopular

Returns one of two values based on a condition

If(Score >= 50, "Pass", "Fail")

Switch(val, case1, r1, ...)LogicPopular

Returns a value based on matching cases

Switch(Status, "Active", Green, "Inactive", Red, Gray)

Coalesce(val1, val2, ...)LogicPopular

Returns the first non-blank value

Coalesce(User.Department, "Unknown")

IfError(value, fallback)LogicPopular

Returns fallback if the value results in an error

IfError(Patch(DS, defaults, record), Notify("Error", NotificationType.Error))

With({alias: expr, ...}, formula)LogicPopular

Evaluates a formula in the context of named aliases. Avoids repeating long expressions, improves readability. Think of it as local variables scoped to a single expression.

With(
  {
    fullName: FirstName & " " & LastName,
    age: DateDiff(BirthDate, Today(), TimeUnit.Years)
  },
  fullName & " (" & age & " years old)"
)

With() — avoid re-evaluating expensive callsLogicPopular

With() is ideal when you need to use the result of a LookUp() or complex formula multiple times without calling it twice.

With(
  { record: LookUp(Accounts, accountid = Gallery1.Selected.accountid) },
  If(
    record.Status.Value = "Active",
    Patch(Accounts, record, { LastSeen: Now() }),
    Notify("Account is inactive", NotificationType.Warning)
  )
)

Navigate(screen, transition)NavigationPopular

Navigates to another screen

Navigate(HomeScreen, ScreenTransition.Fade)

Set(variable, value)NavigationPopular

Sets a global variable

Set(CurrentUser, User().FullName)

UpdateContext({key: val})NavigationPopular

Sets a local context variable

UpdateContext({IsMenuOpen: !IsMenuOpen})

Notify(message, type)NavigationPopular

Shows a banner notification

Notify("Saved!", NotificationType.Success)

SubmitForm(form)NavigationPopular

Validates and submits a form

SubmitForm(Form1)

NewForm(form)NavigationPopular

Sets a form to create a new record

NewForm(Form1)

EditForm(form)NavigationPopular

Sets a form to edit the selected record

EditForm(Form1)

Len(text)Text

Returns the number of characters in a text string

Len("Hello") // 5

Left(text, n)Text

Returns the first n characters from the left

Left("Hello World", 5) // "Hello"

Right(text, n)Text

Returns the last n characters from the right

Right("Hello World", 5) // "World"

Mid(text, start, n)Text

Returns n characters starting at a position

Mid("Hello World", 7, 5) // "World"

Lower(text)Text

Converts text to lowercase

Lower("HELLO") // "hello"

Upper(text)Text

Converts text to uppercase

Upper("hello") // "HELLO"

Trim(text)Text

Removes leading and trailing spaces

Trim(" Hello ") // "Hello"

Concatenate(...)Text

Joins multiple text values together

Concatenate("Hello", " ", "World") // "Hello World"

Substitute(text, old, new)Text

Replaces occurrences of a substring

Substitute("Hello World", "World", "PA") // "Hello PA"

Find(findText, inText)Text

Returns the position of text within text (1-based)

Find("World", "Hello World") // 7

StartsWith(text, start)Text

Checks if text begins with a given string

StartsWith("Hello", "He") // true

EndsWith(text, end)Text

Checks if text ends with a given string

EndsWith("Hello", "lo") // true

Split(text, separator)Text

Splits text into a single-column table

Split("a,b,c", ",") // table with a, b, c

Value(text)Text

Converts a text string to a number

Value("42") // 42

Year(date)Date & Time

Extracts the year from a date

Year(Today()) // 2026

Month(date)Date & Time

Extracts the month from a date (1–12)

Month(Today()) // 4

Day(date)Date & Time

Extracts the day of the month from a date

Day(Today()) // 12

Hour(datetime)Date & Time

Extracts the hour from a datetime (0–23)

Hour(Now())

Weekday(date)Date & Time

Returns the day of the week (1=Sunday by default)

Weekday(Today())

Date(year, month, day)Date & Time

Creates a date value from year, month, day

Date(2026, 12, 31)

SortByColumns(table, col, order)Collections

Sorts with explicit sort order

SortByColumns(Items, "Name", SortOrder.Ascending)

First(table)Collections

Returns the first record of a table

First(SortByColumns(Items, "Date")).Name

Last(table)Collections

Returns the last record of a table

Last(Gallery1.AllItems).Value

FirstN(table, n)Collections

Returns the first n records of a table

FirstN(Sort(Items, Date), 5)

Clear(collection)Collections

Removes all records from a collection

Clear(MyCart)

AddColumns(table, col, formula)Collections

Returns a table with a new calculated column

AddColumns(Products, "Total", Price * Quantity)

GroupBy(table, col, group)Collections

Groups table rows by a column value

GroupBy(Sales, "Region", "Items")

Ungroup(table, col)Collections

Reverses a GroupBy operation

Ungroup(GroupedSales, "Items")

Average(table, column)Collections

Returns the average of a numeric column

Average(Scores, Value)

Max(table, column)Collections

Returns the maximum value in a column

Max(Sales, Amount)

Min(table, column)Collections

Returns the minimum value in a column

Min(Temperatures, Value)

And(cond1, cond2)Logic

Returns true if both conditions are true (also: &&)

And(Age >= 18, IsVerified)

Or(cond1, cond2)Logic

Returns true if either condition is true (also: ||)

Or(IsAdmin, IsManager)

Not(condition)Logic

Reverses a boolean value (also: !)

Not(IsBlank(TextInput1.Text))

IsError(value)Logic

Returns true if the value is an error

If(IsError(Value(TextInput1.Text)), "Invalid", "OK")

Round(number, digits)Math

Rounds to a specified number of decimal places

Round(3.14159, 2) // 3.14

RoundUp(number, digits)Math

Always rounds up

RoundUp(3.01, 0) // 4

RoundDown(number, digits)Math

Always rounds down (truncates)

RoundDown(3.99, 0) // 3

Abs(number)Math

Returns the absolute value

Abs(-42) // 42

Mod(number, divisor)Math

Returns the remainder after division

Mod(10, 3) // 1

Power(base, exp)Math

Returns base raised to an exponent

Power(2, 8) // 256

Sqrt(number)Math

Returns the square root

Sqrt(144) // 12

Int(number)Math

Rounds down to the nearest integer

Int(3.9) // 3

Rand()Math

Returns a random number between 0 and 1

Round(Rand() * 100, 0) // random 0–100

RandBetween(min, max)Math

Returns a random integer in a range

RandBetween(1, 6) // dice roll

Back()Navigation

Navigates back to the previous screen

Back()

Launch(url)Navigation

Opens a URL or launches another app

Launch("https://example.com")

ResetForm(form)Navigation

Resets a form to its default state

ResetForm(Form1)