Level up your Power Apps with these formulas

☝

TL;DR - If you are working with canvas Power Apps, you wild find yourself re-using formulas quite often. This can range from one-liners to multi-line functions. Below I have collected many formulas that i frequently use.

Basics

IfError

I found this formula quite late in my developing career. But it is so important. You can add all formulas that you want to run into this expression, then define what happens if any of the formulas returned an error and lastly define what happens if everything was successful. If an error occurs you can display the error message to the user.

IfError(
		//RUNNING THIS FORMULA
    Patch(
        ListExample,
        LookUp(
            ListExample,
            ID = recSelected.ID
        ),
        {
            TextValue:"Works"
        }
    ),
//On failure, notify with a message including the error
    Notify(
        "Could not submit data with the following error: " & FirstError.Message,
        NotificationType.Error
    ),
//On success, notify with a message
    Notify(
        "Successfully saved your data. The status will be updated in 1-2 minutes. You will be redirected to the home screen.",
        NotificationType.Success
    );
    //add more command by connecting them with a semicolon
    Navigate(HOME)
)

On start formulas

Deep linking

I work a lot with URL parameters, to either specific a context with a selected element id or a role. Parameters are values that you can read from the browsers URL when starting an app. You can then navigate to a screen, load specific data or display certain things all depending on those parameters.

//Getting URL Parameters
If(
    IsBlank(Param("item")),
    //No item parameter available
    false,
    //item parameter found
    Set(
        gblIntItemdid,
        Int(Param("item"))
    )
);
If(
    IsBlank(Param("role")),
    //no role parameter available
    false,
    //role parameter found
    Set(
        gblStrRole,
        Param("role")
    )
);

I always create an environment variable and enter the apps id. I then use this link when sending the notification with the deeplink.

https://apps.powerapps.com/play/e/@{workflow()['tags']['environmentName']}/a/ENV-VARIABLE

//optional parameters
https://apps.powerapps.com/play/e/@{workflow()['tags']['environmentName']}/a/ENV-VARIABLE?item=2&role=view

Formatting various data types

//currency in €
Text(NumberInputControl.Value,"[$-en-US]€###,##0.00")
//leading zeros
Text(NumberInputControl.Value,)
//date and time, with predefined formats
Text(DateTime,DateTimeFormat.ShortDateTime24)
//date and time, with custom format
Text(DateTime,"dd.mm.yyyy HH:mm:ss")

Writing data to a database

Add attachments to a list item in SharePoint with a form and patch()

Attachments can be saved with a simple Form, with Patch() or with a flow. I always use patch to write data to SharePoint. Still you can use a Forms to allow attachments to be uploaded. Add a form, add your datasource (where you list item will be stored), then clear a fields except the Attachments. Then in a button patch your data to SharePoint and add your FormName.Updates outside of the item record. The forms will know where to patch the attachments to, you do not need to configure anything else.

Patch(
    LIST,
    {
        Title: "Test Title"
				//more columns
    },
    Form_Name.Updates
)

Use Power Automate to save files to a library or list

In Power Automate create a manual flow that triggers when an app calls (V2) and add file as a required input. You can add more inputs if necessary. Then add the workflows main tasks as actions and finish the workflow with the “Respond to app or flow” action.

In Power Apps create a form temporarily, then cut and paste the attachments controls from its card to wherever you want it to be. Add a button and configure the following as the OnSelect property.

IfError(
    ForAll(
		    //AttachmentsUploadAssessments is the name of the attachments control
        AttachmentsUploadAssessments.Attachments As File,
        //Add your flow to the app and start typing
        'MYFLOWNAME'.Run(
	        //this is the record the flow expects
            {
                contentBytes: File.Value,
                name: File.Name
            },
            //add further inputs if necessary.
            BelongsToItemWith.ID
        )
    ),
    Notify(
        "The files you attached could not be uploaded with the following error: " & FirstError.Message,
        NotificationType.Error
    ),
    Notify(
        "Your files have be uploaded and will be added to the item in 1-2 minutes.",
        NotificationType.Success
    );
    Reset(AttachmentsUploadAssessments)
)

Patch a single person field

Patching a person field in SharePoint requires a fixed schema. Technically, not all properties of that schema need to be filled. The most important is the Claims property. The rest can stay empty. Claims only requires the Email address of the person you want to patch.

Patch(
    LISTS',
    {
        PersonSingle: {
           '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
           Claims: "i:0#.f|membership|" & User().Email,
           Department: "",
           DisplayName: "",
           Email: "",
           JobTitle: "",
           Picture: ""
        }
    }
)

If E-Mail value not present

With this you need to make sure that E-Mail always has a value, if it doesn’t it will fail. The only way to patch an empty person field is to precheck if the desired E-Mail value is available. I do this by checking and setting a context variable before I patch (below). Then we patching I use that context variable.

If(
    IsBlank(ComboBoxEmployee.Selected.Mail),
	  //Set record value to empty
    UpdateContext({recEmployeePerson:Blank()}),
	  //Set value to actual record with mail
    UpdateContext({recEmployeePerson:
			{
			           '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
			           Claims: "i:0#.f|membership|" & ComboBoxEmployee.Selected.Mail,
			           Department: "",
			           DisplayName: "",
			           Email: "",
			           JobTitle: "",
			           Picture: ""
			        }
		})
);
Patch(
    LISTS',
    {
        EmployeePerson: recEmployeePerson
    }
)

Reading data from databases

Canvas Power Apps have a limitation when it comes to large lists. By default, a canvas app can load up to 500 items from SharePoint. You can increase this to 2000 if necessary. In general, working with large lists requires thought and consideration. It might make sense to pre-filter data or allow users to filter based on dropdown values or search through specific fields.

Filter based on dropdown values

With Filter() you can filter SharePoints lists, Dataverse tables or internal collections. Filter() works quite similar to the filter queries in Power Automate. A item or file is returned if conditions results in true. Lets imagine you have a dropdown that offers status choices for “All, Open, In Progress and Done”. You want to filter the gallery based on the selected option. All should show as many items as possible, the others only the ones in the respective status.

This filter query will either return all tasks that have the selected filter option or return all if the filter status is set to “All”. In the “All”-case all items from that list, will return true for the filter condition and that is why all items are displayed. Technically, you are not even comparing to the data in the list. You are comparing directly in-app.

Filter(
        MyList;
        (TaskStatus.Value = FilterStatus.Selected.Value 
				//This "or-expression" will return all items when the dropdown has "All" selected
				|| FilterStatus.Selected.Value = "All")
)

Miscellaneous

Sequence - simple continues list

Sequence will help you generate simple continuous lists. Recently I had to create a list with values from 1-20 for a dropdown with values of this format: OrderId-GRP-GroupId (e.g. 2023-5-GRP-1).

Clear(colOrderGroups);
ForAll(
    Sequence(
        20,
        1,
        1
    ) As Sequence,
    Collect(
        colOrderGroups,
        {Value: OrderId & "-GRP-" & Sequence.Value}
    )
)

Sequence - to show the next 10 days as items in a dropdown

You can extend that function to generate the next 10 dates from today. Use that table in a drop-down to make it available to your users.

ForAll(Sequence(10),DateAdd(Today(),Value,TimeUnit.Days))

Use timer for a count down

For timers the text property will show the current timer state. Usually the timer will increase. If you want to have it decrease and count down, you can just reverse the value its showing. Add this to the text property:

Self.Duration/1000 - Int(Text(Time(0, 0, Self.Value/1000), "ss"))

//Self.duration/10000 will automatically take the timers duration in milliseconds and convert it to seconds. From there it will be subtracted by the timers actual current value. The timer usually displays in this format “hour:minutes:seconds”. With this change it will only show the seconds left.

Checking if a user has automatic replies on

First add the Office 365 Outlook Connector to your app.

The GetMailTipsV2 function expects a table or array of email addresses. In my case I only need one address, so I added it into the square brackets. Since it expects an array, it will also return an array. Even if you just added one email address.

When someone is not OOF, then the message part of the automatic replies value is empty.

Set(arrMailTips;Office365Outlook.GetMailTipsV2("automaticReplies";[EMAIL]).value)
Set(boolPersonOutOfOffice;If(IsBlankOrError(First(arrMailTips).automaticReplies.message);false;true))

//Combined it can look like this for a single person
Set(
    boolPersonOutOfOffice;
    If(
        IsBlankOrError(
            First(
                Office365Outlook.GetMailTipsV2(
                    "automaticReplies";
                    [Self.Selected.Email]
                ).value
            ).automaticReplies.message
        );
        false;
        true
    )
)

Calculate working days between a date and today

Depending on your configuration (Calculating to the past or future) you might get negative numbers from this formula. That’s why it is enclosed in Abs().

Abs(RoundDown(DateDiff(ThisItem.StartDate, Now(), TimeUnit.Days) / 7, 0) * 5 + Mod(5 + Weekday(Now()) - Weekday(ThisItem.StartDate), 5))

Attachment control - check file name length

In this case I wanted to add a prefix to a file before I saved it to SharePoint. Some times users choose file names exceeding the 255 character limit. This is just a simple way to notify users, nothing more. Use the context variable boolBlockFileUpload to disabled buttons to submit the attachments, set the variable to false OnRemoveFile of the attachment. Edit the word but into PrefixLength.

//Check if file name fits the assessment prefix
With(
    {
        FileName: Lower(Last(Self.Attachments).Name),
        PrefixLength: Len("Assessment_")
    },
    If(
        Len(FileName) + PrefixLength > 255,
        Notify(
            "Filename to long, please remove this file, then reduce the length of the file name by at least " & PrefixLength & ".",
            NotificationType.Error
        );
        UpdateContext({boolBlockFileUpload:true}),
        UpdateContext({boolBlockFileUpload:false})
    )
)

Attachment control - check file extensions

Use the context variable boolBlockFileUpload to disabled buttons to submit the attachments, set the variable to false OnRemoveFile of the attachment.

//Check if added file is allowed file type
With(
    {
        AllowedExtensions: [
            ".pptx",
            ".docx",
            ".xlsx"
        ],
        FileName: Lower(Last(Self.Attachments).Name)
    },
    If(
        CountIf(
            AllowedExtensions,
            EndsWith(
                FileName,
                ThisRecord.Value
            )
        ) = 0,
        Notify(
            FileName & " is not allowed. You can add file with extensions:  " & Concat(
                AllowedExtensions,
                Value,
                ", "
            ),
            NotificationType.Error
        );
        UpdateContext({boolBlockFileUpload: true}),
        UpdateContext({boolBlockFileUpload: false})
    )
)

People Picker

Built for SharePoint

Combobox - OnChange

ClearCollect(
    tablePeoplePicker,
    ShowColumns(
    AddColumns(
        Filter(
            Office365Users.SearchUser(
                {
                    searchTerm: Coalesce(Self.SearchText,""),
                    top: 500
                }
            ),
            !(Mail = User().Email) && AccountEnabled = true && !(IsBlank(Mail))
        ),
        '@odata.type',
        "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        Claims,
        "i:0#.f|membership|" & Mail,
        Email,
        Mail,
        Picture,
        ""
    ),'@odata.type',Claims,DisplayName,Department,JobTitle,Picture,Email)
)

Combobox - Items

tablePeoplePicker

Download link from gallery

This is based of a gallery that contains attachments (Items = recSelected.Attachments) from a selected item. Its depended on a full link to your file. Attachments have an AbsoluteUri, which you can split to build a URL that only has the link until /sites/yoursitename. This allows you then to add the /_layouts/… url parameters to have the browser download the file instead of opening a new window.

Launch(Concat(FirstN(Split(ThisItem.AbsoluteUri,"/"),5),Value,"/") & "/_layouts/download.aspx?SourceUrl=" & ThisItem.AbsoluteUri,Blank(),LaunchTarget.New)