Formula reference - canvas apps

In this article, learn about all the functions available in canvas apps.

Formulas in canvas apps combine many elements. Listed below are:

  • Functions take parameters, perform an operation, and return a value. For example, Sqrt(25) returns 5. Functions are modeled after Microsoft Excel functions. Some functions have side effects, such as SubmitForm, which are appropriate only in a behavior formula such as Button.OnSelect.
  • Signals return information about the environment. For example, Location returns the device's current GPS coordinates. Signals don't take parameters or have side effects.
  • Enumerations return a pre-defined constant value. For example, Color is an enumeration that has pre-defined values for Color.Red, Color.Blue, and so forth. Common enumerations are included here; function-specific enumerations are described with the function.
  • Named operators, such as ThisItem and Self, provide access to information from within a container.

Other elements include:

A

Abs – Absolute value of a number.

Acceleration – Reads the acceleration sensor in your device.

Acos – Returns the arccosine of a number, in radians.

Acot – Returns the arccotangent of a number, in radians.

AddColumns – Returns a table with columns added.

And – Boolean logic AND. Returns true if all arguments are true. You can also use the && operator.

AIClassify – Classifies text into one or more of categories.

AIExtract – Extracts specified entities such as registration numbers, phone numbers, or names of people.

AIReply – Drafts a reply to the message that you provide.

AISentiment – Detects the sentiment of the text that you provide.

AISummarize – Summarizes the text that you provide.

AITranslate – Translates text from another language.

App – Provides information about the currently running app and control over the app's behavior.

Asin – Returns the arcsine of a number, in radians.

Assert – Evaluates to true or false in a test.

As – Names the current record in gallery, form, and record scope functions such as ForAll, With, and Sum.

AsType – Treats a record reference as a specific table type.

Atan – Returns the arctangent of a number, in radians.

Atan2 – Returns the arctangent based on an (x,y) coordinate, in radians.

Average – Calculates the average of a table expression or a set of arguments.

B

Back – Displays the previous screen.

Blank – Returns a blank value that can be used to insert a NULL value in a data source.

Boolean – Converts a text string, number, or untyped value to a Boolean value.

C

Calendar – Retrieves information about the calendar for the current locale.

Char – Translates a character code into a string.

Choices – Returns a table of the possible values for a lookup column.

Clear – Deletes all data from a collection.

ClearCollect – Deletes all data from a collection and then adds a set of records.

ClearData – Clears a collection or all collections from an app host such as a local device.

Clock – Retrieves information about the clock for the current locale.

Coalesce – Replaces blank values while leaving non-blank values unchanged.

Collect – Creates a collection or adds data to a data source.

Color – Sets a property to a built-in color value.

ColorFade – Fades a color value.

ColorValue – Translates a CSS color name or a hex code to a color value.

Column – Retrieves column names and values from an Untyped object data type.

ColumnNames – Retrieves column names and values from an Untyped object data type.

Compass – Returns your compass heading.

Concat – Concatenates strings in a data source.

Concatenate – Concatenates strings.

Concurrent – Evaluates multiple formulas concurrently with one another.

Connection – Returns information about your network connection.

Copy – Copies text to the clipboard on the device where the app is running.

Count – Counts table records that contain numbers.

Cos – Returns the cosine of an angle specified in radians.

Cot – Returns the cotangent of an angle specified in radians.

CountA – Counts table records that aren't empty.

CountIf – Counts table records that satisfy a condition.

CountRows – Counts table records.

D

DataSourceInfo – Provides information about a data source.

Date – Returns a date/time value, based on Year, Month, and Day values.

DateAdd – Adds days, months, quarters, or years to a date/time value.

DateDiff – Subtracts two date values, and shows the result in days, months, quarters, or years.

DateTime – Returns a date/time value, based on both date and time components.

DateTimeValue – Converts a date and time string to a date/time value.

DateValue – Converts a date-only string to a date/time value.

Day – Retrieves the day portion of a date/time value.

Dec2Hex – Convert a number to a hexadecimal text string.

Defaults – Returns the default values for a data source.

Degrees – Converts radians to degrees.

Disable – Disables a signal, such as Location for reading the GPS.

Distinct – Summarizes records of a table, removing duplicates.

Download – Downloads a file from the web to the local device.

DropColumns – Returns a table with one or more columns removed.

E

EDate – Adds or subtracts months to a date, without changing the day of the month.

EditForm – Resets a form control for editing of an item.

Enable – Enables a signal, such as Location for reading the GPS.

EncodeHTML – Encodes characters that need to be escaped to be used in an HTML context.

EncodeUrl – Encodes special characters using URL encoding.

EndsWith – Checks whether a text string ends with another text string.

EOMonth – Adds or subtracts months to a date, returning the last day of that month.

Error – Create a custom error or pass through an error.

Errors – Provides error information for previous changes to a data source.

exactin – Checks if a text string is contained within another text string or table, case dependent. Also used to check if a record is in a table.

Exit – Exits the currently running app and optionally signs out the current user.

Exp – Returns e raised to a power.

F

Filter – Returns a filtered table based on one or more criteria.

Find – Checks whether one string appears within another and returns the location.

First – Returns the first record of a table.

FirstN – Returns the first set of records (N records) of a table.

ForAll – Calculates values and performs actions for all records of a table.

G

GroupBy – Returns a table with records grouped together.

GUID – Converts a GUID string to a GUID value or creates a new GUID value.

H

HashTags – Extracts the hashtags (#strings) from a string.

Hex2Dec – Convert a hexadecimal text string to a number.

Host – Provides information about the current host running the app.

Hour – Returns the hour portion of a date/time value.

I

If – Returns one value if a condition is true and another value if not.

IfError – Detects errors and provides an alternative value or takes action.

in – Checks if a text string is contained within another text string or table, case independent. Also used to check if a record is in a table.

Index – Returns a record from a table based on ordered position.

Int – Rounds down to the nearest integer.

IsBlank – Checks for a blank value.

IsBlankOrError – Checks for a blank value or error.

IsEmpty – Checks for an empty table.

IsError – Checks for an error.

IsMatch – Checks a string against a pattern. Regular expressions can be used.

IsNumeric – Checks for a numeric value.

ISOWeekNum – Returns the ISO week number of a date/time value.

IsToday – Checks whether a date/time value is sometime today in the user's time zone.

IsType – Checks whether a record reference refers to a specific table type.

IsUTCToday – Checks whether a date/time value is sometime today in Coordinated Universal Time (UTC).

J

JSON – Generates a JSON text string for a table, a record, or a value.

L

Language – Returns the language tag of the current user.

Last – Returns the last record of a table.

LastN – Returns the last set of records (N records) of a table.

Launch – Launches a webpage or a canvas app.

Left – Returns the left-most portion of a string.

Len – Returns the length of a string.

Ln – Returns the natural log.

LoadData – Loads a collection from an app host such as a local device.

Location – Returns your location as a map coordinate by using the Global Positioning System (GPS) and other information.

Log – Returns the logarithm in any base of a number.

LookUp – Looks up a single record in a table based on one or more criteria.

Lower – Converts letters in a string of text to all lowercase.

M

Match – Extracts a substring based on a pattern. Regular expressions can be used.

MatchAll – Extracts multiple substrings based on a pattern. Regular expressions can be used.

Max – Maximum value of a table expression or a set of arguments.

Mid – Returns the middle portion of a string.

Min – Minimum value of a table expression or a set of arguments.

Minute – Retrieves the minute portion of a date/time value.

Mod – Returns the remainder after a dividend is divided by a divisor.

Month – Retrieves the month portion of a date/time value.

N

Navigate – Changes which screen is displayed.

NewForm – Resets a form control for creation of an item.

Not – Boolean logic NOT. Returns true if its argument is false, and returns false if its argument is true. You can also use the ! operator.

Notify – Displays a banner message to the user.

Now – Returns the current date/time value in the user's time zone.

O

Or – Boolean logic OR. Returns true if any of its arguments are true. You can also use the || operator.

P

Param – Access parameters passed to a canvas app when launched.

Parent – Provides access to a container control's properties.

ParseJSON – Converts JSON document represented as text to an Untyped object value.

Patch – Modifies or creates a record in a data source, or merges records outside of a data source.

PDF – Export contents from the current screen to an object for use in multiple scenarios.

Pi – Returns the number π.

PlainText – Removes HTML and XML tags from a string.

Power – Returns a number raised to a power. You can also use the ^ operator.

Proper – Converts the first letter of each word in a string to uppercase, and converts the rest to lowercase.

R

Radians – Converts degrees to radians.

Rand – Returns a pseudo-random number between 0 and 1.

RandBetween – Returns a pseudo-random number between two numbers.

ReadNFC – Reads a Near Field Communication (NFC) tag.

RecordInfo – Provides information about a record of a data source.

Refresh – Refreshes the records of a data source.

Relate – Relates records of two tables through a one-to-many or many-to-many relationship.

Remove – Removes one or more specific records from a data source.

RemoveIf – Removes records from a data source based on a condition.

RenameColumns – Renames columns of a table.

Replace – Replaces part of a string with another string, by starting position of the string.

RequestHide – Hides a SharePoint form.

Reset – Resets an input control to its default value, discarding any user changes.

ResetForm – Resets a form control for editing of an existing item.

Revert – Reloads and clears errors for the records of a data source.

RGBA – Returns a color value for a set of red, green, blue, and alpha components.

Right – Returns the right-most portion of a string.

Round – Rounds to the closest number.

RoundDown – Rounds down to the largest previous number.

RoundUp – Rounds up to the smallest next number.

S

SaveData – Saves a collection to an app host such as a local device.

Search – Finds records in a table that contain a string in one of their columns.

Second – Retrieves the second portion of a date/time value.

Select – Simulates a select action on a control, causing the OnSelect formula to be evaluated.

Self – Provides access to the properties of the current control.

Sequence – Generate a table of sequential numbers, useful when iterating with ForAll.

SendAppNotification (preview) – Send an in-app notification with optional actions to a recipient.

Set – Sets the value of a global variable.

SetFocus – Moves input focus to a specific control.

SetProperty – Simulates interactions with input controls.

ShowColumns – Returns a table with only selected columns.

ShowHostInfo – Displays information to the user in the app.

Shuffle – Randomly reorders the records of a table.

Sin – Returns the sine of an angle specified in radians.

Sort – Returns a sorted table based on a formula.

SortByColumns – Returns a sorted table based on one or more columns.

Split – Splits a text string into a table of substrings.

Sqrt – Returns the square root of a number.

StartsWith – Checks if a text string begins with another text string.

StdevP – Returns the standard deviation of its arguments.

Substitute – Replaces part of a string with another string, by matching strings.

SubmitForm – Saves the item in a form control to the data source.

Sum – Calculates the sum of a table expression or a set of arguments.

Switch – Matches with a set of values and then evaluates a corresponding formula.

T

Table – Creates a temporary table.

Tan – Returns the tangent of an angle specified in radians.

Text – Converts any value and formats a number or date/time value to a string of text.

ThisItem – Returns the record for the current item in a gallery or form control.

ThisRecord – Returns the record for the current item in a record scope function, such as ForAll, With, and Sum.

Time – Returns a date/time value, based on Hour, Minute, and Second values.

TimeValue – Converts a time-only string to a date/time value.

TimeZoneOffset – Returns the difference between UTC and the user's local time in minutes.

Today – Returns the current date-only value.

Trace – Provide additional information in your test results.

Trim – Removes extra spaces from the ends and interior of a string of text.

TrimEnds – Removes extra spaces from the ends of a string of text only.

Trunc – Truncates the number to only the integer portion by removing any decimal portion.

U

Ungroup – Removes a grouping.

UniChar – Translates a Unicode code into a string.

Unrelate – Unrelates records of two tables from a one-to-many or many-to-many relationship.

Update – Replaces a record in a data source.

UpdateContext – Sets the value of one or more context variables of the current screen.

UpdateIf – Modifies a set of records in a data source based on a condition.

Upper – Converts letters in a string of text to all uppercase.

User – Returns information about the current user.

UTCNow – Returns the current date/time value in Coordinated Universal Time (UTC).

UTCToday – Returns the current date-only value in Coordinated Universal Time (UTC).

V

Validate – Checks whether the value of a single column or a complete record is valid for a data source.

Value – Converts a string to a number.

VarP – Returns the variance of its arguments.

ViewForm – Resets a form control for viewing of an existing item.

W

Weekday – Retrieves the weekday portion of a date/time value.

WeekNum – Returns the week number of a date/time value.

With – Calculates values and performs actions for a single record, including inline records of named values.

Y

Year – Retrieves the year portion of a date/time value.