Power Apps User Defined Functions: Write Code Once And Reuse

Power Apps User Defined Functions: Write Code Once And Reuse

With Power Apps user defined functions we can write a formula once and reuse the logic many times throughout an app. To do this we choose a function name, determine the inputs and their data types and write a formula to evaluate. Then we can call the function from anywhere in the app. I love this new extension of the Power Apps named formulas feature and I’m looking forward to using it in all of my builds.

Table of Contents
• Basic Example: The Multiply Numbers FunctionEnable Power Apps User Defined Functions FeatureCreate The User Defined Function In The App Formulas PropertyCall The Multiply Numbers User Defined FunctionAdvanced Example: The Net Work Days FunctionDefine The NetWorkDays FunctionPass Arguments To NetWorkDays Using Input ControlsError Handling For User Defined FunctionsCurrent Limitations Of User Defined Functions




Basic Example: The Multiply Numbers Function

The first Power Apps user defined function we will create together is the MultiplyNumbers function. It takes two numbers as inputs and outputs their product.




Enable Power Apps User Defined Functions Feature

Power Apps user defined functions are currently an experimental feature and must be enabled. Open the advanced settings and perform the following steps:

  • Go to the Support tab and update the authoring version 3.24013 or greater
  • Browse to the Upcoming Features tab and toggle on new analysis engine
  • Then toggle on the user defined functions feature




Create The User Defined Function In The App Formulas Property

The MultiplyNumbers function is created as a Power Apps named formula with additional syntax for inputs and outputs.



Write this code in the Formulas property of the App object.

MultiplyNumbers(Number1:Number, Number2:Number):Number = Number1 * Number2;



The syntax for a Power Apps user defined function is:

FunctionName(Parameter1:DataType1, Parameter2:DataType2):OutputDataType = Formula

  • FunctionName – used to invoke the function
  • Parameter – the name of the input. One or more inputs are allowed
  • DataType – argument passed into the function must match this data type
  • OutputDataType – output of the function will be in this data type
  • Formula – the result of this formula is the output of the function



Here is the list of available data types:




Call The Multiply Numbers User Defined Function

Add a text control to the app and call the MultiplyNumbers function just as you would any other Power Apps function.



Use this code in the Text property of the text control. The result of multiplying 3 x 5 = 15.

MultiplyNumbers(3,5)




Advanced Example: The Net Work Days Function

The second Power Apps user defined function we will create is the NetWorkDays function from Microsoft Excel. It takes a start date and and end date as arguments and returns the number of days between them excluding weekends (Saturday and Sunday).




Define The NetWorkDays Function

The NetWorkDays function is defined in the Formulas property of the app.



Copy and paste this code in the Formulas property of the app. It creates a single column table of all the days between the start date and the end date, adds another true/false column named IsWeekday which checks if the date is between Monday-Friday and then sums the number of true/false values. We can sum the true/false values because true is represented as 1 and false is represented by 0.

NetWorkDays(StartDate:DateTime, EndDate:DateTime):Number = Sum(
    AddColumns(
        ForAll(
            Sequence(
                DateDiff(
                    StartDate,
                    EndDate,
                    TimeUnit.Days
                )+1
            ),
            StartDate + Value
        ),
        "IsWeekday",
        Weekday(Value) in [2,3,4,5,6]
    ),
    IsWeekday
);




Pass Arguments To NetWorkDays Using Input Controls

We can pass arguments to the function using Power Apps input controls. Insert two date picker controls onto the screen. One to select the start date and the other to capture the end date.



Then add a text control to the screen and use this code in the Text property to call the NetWorkDays function.

NetWorkDays(DatePicker_StartDate.SelectedDate, DatePicker_EndDate.SelectedDate)



The result showing in the text control changes as the date pickers are updated.




Error Handling For User Defined Functions

User defined functions can result in an error so it is important to include error handling in their definition. For the NetWorkDays function, an error appears when the end date is earlier than than the start date.



Wrap the NetWorkDays function with the Power Apps IfError function to output 0 whenever an error occurs.

NetWorkDays(StartDate:DateTime, EndDate:DateTime):Number = IfError(
    Sum(
        AddColumns(
            ForAll(
                Sequence(
                    DateDiff(
                        StartDate,
                        EndDate,
                        TimeUnit.Days
                    )+1
                ),
                StartDate + Value
            ),
            "IsWeekday",
            Weekday(Value) in [2,3,4,5,6]
        ),
        IsWeekday
    ),
    0
);




Current Limitations Of User Defined Functions

Power Apps user defined functions are an experimental feature and have the following limitations. Please leave any other limitations you find in the comments section and I will add them to the list.

  • All parameters are required parameters. There is no way to set optional parameters.
  • Data types for record and table are missing. They cannot be used as inputs/ouputs.





Questions?

If you have any questions or feedback about Power Apps User Defined Functions: Write Code Once And Reuse please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest

35 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Ujjwal
Ujjwal
4 months ago

Based on your recent post on user defined function
Start Date -1st Jan 2024
End date -19th Jan 2024
No workdays should be 15 but according to your formula it is 14.

Similarly for
Start Date -1st Jan 2024
End date -31st Jan 2024
No workdays is 23 which is correct.
I think you need to recheck it.

Eric Brown
Eric Brown
4 months ago

I love the Formulas feature — and the Named Function are even more awesome — but I’ve stopped using it. But I’ve found that the formulas often don’t update when a dependent variable updates. Sometimes while editing I have to go to the formulas and delete and replace a semicolon to get it to ‘fire’. End users also experience the lack of update when running apps.

How stable have you found the Formulas feature in general? Is this teething pain for Power Apps or something peculiar to my set up?

George Hepworth
4 months ago
Reply to  Eric Brown

I find this problem as well, but I think you can get around it by refreshing a data source, at least when the formula relies on one. Otherwise, I don’t have an answer to this one — yet.

In general, I consider formulas to be more or less a nod in the direction of reusable code, which hasn’t been a priority for PowerApps. However, as PA matures, it seems to me that Microsoft really needs to cater both to the “Citizen Developers” and the Power Platform Stack Developer communities.

alexsolex
alexsolex
4 months ago

Hi and thank you for this tutorial.
I think you could use table and object as input using the untypeobject as data type 😉
OK it is not as obvious as standard datatypes but it works

Teo
Teo
4 months ago

This would be great if it worked with tables/records… but I could not get the Untypedobject to do it.

Cody
Cody
2 months ago

A work around is to convert your data types to JSON using the JSON function, parsing the JSON string into an Untyped object using ParseJSON, then passing the Untyped object as a parameter to the User Defined function. The Untyped object can then be converted back to its original type to be manipulated within the UDF.

ParseJSON takes a JSON string as input and returns an Untyped Object of the JSON string. This Untyped object can be converted to any data type using the appropriate constructor, expect a record. However, a record can be converted to a new record using the dot notation for accessing fields.

See more at
https://learn.microsoft.com/en-us/power-platform/power-fx/untyped-object
https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-parsejson
https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-json

Greg
Greg
4 months ago

Thank you for this tutorial. I have tried it and can’t seem to get it going.
I get this error.
‘MultiplyNumber’ is an unknown or unsupported function.

In settings I have version 3.24013.14 selected and “User-defined functions” is On. I know this is experimental but would like to play with it. Any thoughts?
It does this on Chrome and in Edge, normal and incognito.

function error.png
Greg
Greg
4 months ago

Thanks. I will check again. It did revert me to the recommended several times. Maybe time helped the issue.

Teo
Teo
4 months ago
Reply to  Greg

The UDFs got in the latest stable version, e.g. they are in the default version right now, switching is no longer needed.

Gino
Gino
4 months ago

Hi Matthew,

In the calling function section, I believe ‘s’ is missing at the end.
MultiplyNumber(3,5)
MultiplyNumbers(3,5) – should be

On the other hand I was not able to get it worked since received error:
‘There is an error in this formula. Try revising the formula and running it again. Data type: number’

I did what you described, corrected the formula so no clue what is wrong. 🙁

Fred Forest
Fred Forest
4 months ago

Do you know if you can call connectors within UDFs?

Eileen O'Reilly-Hoisington
Eileen O'Reilly-Hoisington
4 months ago

Thank you for this! I’m looking forward to trying it.

Shubham
Shubham
4 months ago

Hi There,
Can we define multiple functions?
Or this is a limitation.
As generally, we have multiple business requirements in one App.

Kireeti Yakkali
Kireeti Yakkali
4 months ago

I love this tutorial Thanks Matt! 😊

Fritz Lenker
Fritz Lenker
4 months ago

Thank you for this tutorial. It’s the first of your examples that doesn’t work for me (The Theme selection tutorial is wonderful). Creating an app and page as shown, I get error “MultiplyNumbers’ is an unknown or unsupported function”. I test apps on my personal computer using my MSDN account extended to Azure capability which is also my default environment. All the setup is done and proved. Thanks for your help and your wonderful articles.

Pat Nolin
Pat Nolin
4 months ago

Hello,
it’s wonderfull. Is it possible to create a User Defined Functions inside a componnents library, so we can use / share it accross applications ?
Thanks

Piotr
Piotr
4 months ago

Great find and quality article. As always.

Antoine B
Antoine B
4 months ago

Thank you for this fantastic article. Finally custom functions – Hooray!

But, after reading the article, I eagerly tried implementing this and encountered an issue. 

I have written a function in App.Formulas that works correctly in the edit mode of the app.
However, I noticed that since I enabled “User-Defined Functions” in the settings, the application publishing does not update correctly in play mode.

When I launch the application in play mode, it indicates that there is an update:
“A new version of this app is coming. We’ll let you know when it’s available.”

But it never actually updates.

The issue is resolved when I disable this feature.
Do you know any solutions to resolve these issues?

Thank you!

Will Page
4 months ago

Thanks for posting this article. This is the first I’ve read about user defined functions. I did a test to see if they can be called within the Formulas property itself, and they can!

e.g
MultiplyNumbers(Number1:Number, Number2:Number):Number = Number1 * Number2;
DivideNumbers(Multiplier1:Number, Multiplier2:Number, Divisor:Number):Number = MultiplyNumbers(Multiplier1, Multiplier2) / Divisor;

Luke Jennings
Luke Jennings
3 months ago

Unfortunate that tables, collections, and records are not currently available. Is there by any chance a means of emulating a void/null return type, calling functions that manipulate global variables, or having a dynamic datatype as input (like a generic Object or void pointer in Java or C)? Ideally, I’d want to be able to emulate a generic Stack data structure using a collection using user defined Push(Stack:Collection, Input:GenericObject):Void and Pop(Stack:Collection):GenericObject functions, but it seems like it’s not possible as is.

As useful as formulas are for DRY, I really want to be able to do functional programming.

Dean
Dean
1 month ago

Would be great to be able to return tables and records. If returning tables we could create a filter function for populating a collection based on input parameters.

Vivek
Vivek
14 days ago

Hi Matt,

We are using a custom connector to store timestamps info in SQL . For some reason we have not been able to get it to work. Does UDF support using a custom connector inside the UDF? Do you have any articles or code samples for this?

Is return type in UDF compulsory since the custom connector just writes to SQL.

Any help would be appreciated 🙂

Edward Martin III
4 days ago

There seems to be another limitation, though it’s possible I am missing Key Nomenclature.

In a user-defined function, one cannot create and use local-to-the-function variables, nor can one use a global variable (using a global variable would be the sloppier way of solving this).

Everything must follow in accordance with that “=” symbol.

Probably for trivial functions, this isn’t much of an issue, but for something that requires some complexity, the inability to create/use local variables (preferred!), or to modify a global variable (will work for many cases, but ugh) means that more complex functions can’t be built.

I guess… using copy-and-paste is still an option… (sigh).

It’s nice to see that there are user functions, though, that’s cool.