Formulas - A beginners guide on how to create and call user-defined functions (UDFs)

January 28, 2024

4 min read

Formula

Until now, one of the most frustrating limitations of Power Apps was the lack of support for building reusable Power FX functions.

A common way to apply the same formula in multiple places was to copy and paste the code. Because this results in a lot of duplication, it's not an effective or maintainable way to build an app.

The great news is that Microsoft has started to roll out the 'user-defined function' feature. This enables us to define parameterised, reusable blocks of formula that we can call throughout an app.

This post describes how to get started with user-defined functions and provides some examples of some simple user-defined functions.

How to enable the User-Defined Function Feature

To begin using this feature, the first step is to enable the feature in the app settings. There are two settings to enable.

The first is to enable the "New analysis engine" feature, which is a prerequisite to enabling "User-defined functions".

Once the "New analysis engine" setting is enabled, we can enable the "User-defined functions" feature.

Background - User-defined functions are based on named formulas

To give some background on user-defined functions, this is a feature that builds on top of the named formulas. Named formulas are snippets of formulas that are defined at an application level which we can call from any place in our app.

A big benefit of named formulas (amongst many others) is the deferred execution of code. We can use this to improve performance by moving formula that traditionally runs in the OnStart of an app, to named formulas instead.

User-defined functions build on top of named formulae by enabling parameterized inputs - meaning that we can now pass values in and out of a named formula.

How to create a simple user-defined function

To demonstrate a simple user-defined function, let's implement a function that converts temperatures in Celsius to Fahrenheit. The algorithm for this function is relatively simple - we multiply the input value by 1.8 and add 32.

To create this user-defined function, click the app node in the tree node and enter the following function definition.

CelciusToF (input:Number): Number = (input * 1.8) + 32;

We can now reference this formula from within our app. In the example below, we can add a slider control (Slider1) that enables a user to enter a temperature in Celsius. We can then add a label that displays the Fahrenheit equivalent by using the following formula.

CelciusToF (Slider1.Value)

What's the syntax for defining a user-defined function?

Let's take a closer look at our user-defined formula syntax. The named formula definition begins with the name. Inside the brackets, we define zero or more parameters with their corresponding data types. The parameter name and data type are separated with a colon. Following the input parameters, we enter a colon and specify the output data type. The formula that defines the logic appears after the equals sign.

What data types are supported by user-defined functions?

At the moment, user-defined functions accept the following input and output data types:

Boolean
Color
Date
DateTime
GUID
Hyperlink
Number
Text
Time
UntypedObject

The screenshot below show the supported data types as they appear through the Intellisense window.

The notable data types that are not yet supported include image, record, and table.

Creating a user-defined function with multiple parameters

For another example, here's how to implement a user-defined function to add working days to an input date. This demonstrates how to create a user-defined function with two input parameters.

AddWorkingDays (startDate:Date, daysToAdd:Number): Date = 
      DateAdd(startDate, daysToAdd) + 
      RoundDown(daysToAdd/5, 0)*2 +
      Switch(Weekday(startDate, StartOfWeek.Monday),
             5,If(Mod(daysToAdd, 5)>0,2,0),
             4,If(Mod(daysToAdd, 5)>1,2,0),
             3,If(Mod(daysToAdd, 5)>2,2,0),
             2,If(Mod(daysToAdd, 5)>3,2,0),
             1,If(Mod(daysToAdd, 5)>4,2,0)
      );

The screenshot below illustrates how we can display the result of the user-defined function from a label.

AddWorkingDays(Today(), 3)

Calling user-defined functions from user-defined functions

Calling a user-defined function from within a user-defined function is supported. Let's take an example of two user-defined functions - one to format a date in the format dd/mm/yy and a variation of the AddWorkingDays function to return a text result. The formula beneath works and is valid syntax.

FormatDateDDMMYY(inputDate:Date):Text = Text(inputDate, "dd/mm/yy");

AddWorkingDaysText (startDate:Date, daysToAdd:Number): Text = 
      FormatDateDDMMYY(DateAdd(startDate, daysToAdd) + 
      RoundDown(daysToAdd/5, 0)*2 +
      Switch(Weekday(startDate, StartOfWeek.Monday),
             5,If(Mod(daysToAdd, 5)>0,2,0),
             4,If(Mod(daysToAdd, 5)>1,2,0),
             3,If(Mod(daysToAdd, 5)>2,2,0),
             2,If(Mod(daysToAdd, 5)>3,2,0),
             1,If(Mod(daysToAdd, 5)>4,2,0)
      )
);

Creating user-defined functions that connect to a data source

Writing user-defined functions that interact with a data source is a desirable use case. It's possible to retrieve data from a data source and here's an example of a user-defined function that retrieves a record by calling LookUp.

GetEmployeeNameByID(id:Text):Text = 
    LookUp(Employees, id=EmployeeID, Firstname & " " & Surname);

Although it's possible to retrieve data, it's not possible to update data in a data source. If we attempt to call functions that include Patch, Update, or Remove, we receive the error "Behavior function in a non-behavior property. You can't use this property to change values elsewhere in the app."

Since we can't call behaviour function, updating data from a data source is impossible. Similarly, it's not possible to set variables from within a user-defined function by calling Set.

Conclusion

The ability to create parameterised user-defined functions provides an enormous improvement in the capability of Power Apps. It allows us to build formulas in a much more maintainable and reusable way. This post described how to create and call user-defined functions. At the moment, this is still an experimental feature. The limitations include a restricted set of supported data types and the inability to call behaviour functions. Hopefully, these might be resolved in a future update.