Power Apps — With() Function/Streamline Your Formulas

The With() function is a way for you to simplify your formulas, reduce calls to the data source, and increase your app’s responsiveness. It will evaluate the argument you give it, then retain the values returned as a record so that you could reference them again within that formula. There’s many, many ways to implement this, but let’s first focus on one aspect with a more concrete example:

"Welcome " & 
Office365Users.UserProfile(User().Email).GivenName & 
"! Your department is " & 
Office365Users.UserProfile(User().Email).Department & 
", and your Company Name is " & 
Office365Users.UserProfile(User().Email).CompanyName

Placing this in a label will return a sentence based on the currently-logged-in user’s O365 profile:

Welcome Name! Your department is Human Resources, and your Company Name is Google

While this is an over-simplified example, it illustrates a few of the main reasons why a With() formula would be preferred. Let’s explore by placing this in a label:

With(
    {wUser: Office365Users.UserProfile(User().Email)},
    "Welcome " & wUser.GivenName & 
    "! Your department is " & wUser.Department & 
    ", and your Company Name is " & wUser.CompanyName
)

You’ll find it returns the same sentence as the first formula — but what’s the difference?

Why bother?

Always look for ways to reduce duplicated efforts, not just in business or life, but in your apps as well. On top of reducing the number of controls on a screen, another way to increase your app’s responsiveness is to make your calls to your data sources as succinct and limited as possible:

• In the first example, the formula makes 3 separate calls to the Office365Users data source to return the user’s GivenName, Department, and CompanyName.
• In the second example, the formula makes a single call to the Office365Users data source to return the entirety of the user’s O365 record, and stores it as wUser (could be named whatever you prefer to name it, of course). Then through the record of wUser we can access the various specific columns of data (such as Department and CompanyName).

So what?

Talk to your tech department and ask them how they feel when a database receives an inordinate number of requests — they’re not happy, and the database isn’t happy either.

A database in motion will remain in motion until acted upon by an outside force, such as your tech department.

Besides that, our example highlights another pitfall of variables that I find is fairly common — don’t limit yourself to setting a variable equal to a lookup’s single value when you don’t have to, for example:

Set(varUser,
LookUp(Database, User().Email = Title, Department)
)

When you do this, your variable varUser will only be equal to its result, in this case the Department. Instead, consider this:

Set(varUser,
LookUp(Database, User().Email = Title)
)

With this (pun intended), you can now access all of the columns of that LookUp()’s record simply by adding additional arguments to your variable varUser:

varUser.Department
varUser.EmployeeID
varUser.OtherColumnName

Other ways to implement

Now that we’ve developed the idea of storing the entirety of a record in a variable, take this time to expand on it — how can you use this in your apps? If you have a form in your app, the benefits can be immediately felt.

Forms have a property of LastSubmit; you can access it by:

FormName.LastSubmit.ColumnName

Instead of submitting a form, then attempting to LookUp() the results of the submitted form, you can avoid the unnecessary calls to the data source by using LastSubmit! Instead of this:

SubmitForm(FormName);
LookUp(DataSource, ID = Gallery.Selected.ID)

Consider only this:

FormName.LastSubmit

You can access the results of the (presumably successful) form submission without needing to query for it again.

Summary

This was a quick-and-dirty rundown of With() and accessing columns of a record, but it illustrates how important (and easy) it is to reduce the number of calls to a data source, and reduce the need for redundant variables.

Now, go to your tech department and tell them how you reduced the number of connections to their server by half in under 45 minutes; I’m sure they’ll love you.