Better Performance with Less Effort! Use Power Apps Named Formulas

[@benmichels1977]

I work with a lot of really big collections, 7 to 12 thousand rows, with 20 columns depending on the day and data. Named formulas is substantially faster handling such huge collections, applying filters, handling groups, performing calculations and in generally making everything smoother. It’s now become the “if I can put it in named formulas, it’s going in named formulas, if I can’t, can I change how I, doing things” it’s just an enormous performance improvement.

[@benmichels1977]

One I rather like is using it to search/filter/lookup as it can be used to return a row based on a value in a search box/form control settings then you just reference that tiny formula name with the column name to display the data or perform whatever. It ❤is substantially faster, especially developing screens.

[@ShanesCows]

Awesome. Thanks for sharing. 😀

[@JasonAngWeiLung]

I remember that’s what I wanna do previously (even before the named formula becomes generally available). Thanks for sharing Shane Young!

[@toanbui3810]

As i know, there are delegation limits with formula. How do you create large collections with more than 2000 rows by formula ?

[@GroverParkGeorge]

I just learned he same thing after watching this video. Plus 1. And, in addition, using a named formula means not having to do a ClearCollect on a collection when the underlying data source changes. Plus 2.

[@iektech]

Hi Shane, I think there is a mistake, OnStart event doesnt finish everything before loading your start screen, it is true that it fires at the beginning when opening the app, however if you have a significant numer of actions there or a few that take time to finish, like datasource refresh, you will notice that events from the OnVisible event of your first screen will run before the OnStart event is completed. PS. Merry Christmas 🎄

[@ShanesCows]

Merry Christmas. There is a setting where you can tell it to make sure it waits. 😎

[@GroverParkGeorge]

In many programming languages, including VBA in Office apps, we have Public Constants, which is a role that formulas, such as nfUser, fill here. In my apps, for example, I set the App Title as a formula instead of typing it into the header of screens, and I designate an app logo formula.

[@ShanesCows]

I like it.

[@iswired4sound]

NFs track collections changes automatically. Awesome time saver. Thanks for that tip. Also, can it track changes in any other data sources? Dataverse? Sharepoint? That would be super helpful as well.

[@ShanesCows]

No, NF do not know about changes in the Data Source unless the change happens inside of Power Apps.

[@oluwatobiyusuf]

Power Apps Named Formulas will really improve performance. Thank you Shane

[@antoniobranderas]

I like how you use “nf” for the formulas. I use “gvar” for global variable and “cvar” for context variable.

[@ShanesCows]

Nice. Having some way to distinguish is so nice.

[@starmole5000]

Me too! Exactly the same. Works a treat

[@DaHooka]

Thank you Shane, great video!

[@ShanesCows]

Happy to help 🤩

[@louischamberland8152]

Very nice way to do Formulas... I'm using SVG's in my menus, would you consider loading them through formulas or through OnStart section? Great videos and thank you for your content!!!

[@ShanesCows]

Formulas 🤩

[@appen-pq6sq]

Hello Shane, thank you for one more great video! You stressed the use of a semicolon at the end of the formula. In my environment (Norway), we have to add two (;;;)to make this work.

[@ShanesCows]

That is correct. That is because in your language you use a , as the decimal separator where as I use a . for the decimal. In that case, where I use a ; you will always use a ;; and where I use a , you will use a ; I talk about it in this video kzbin.info/www/bejne/fXLHn4iIrZqabsk

[@windytubeful]

Awesome, I have a bunch of collections that are tables with columns added to expose related columns. It looks like if I make the collection a nf, and then change a variable that is used to filter the nf, the nf will automatically rebuild the table? This will mean I can have the definition for the table in one place only, whereas right now I am copying the code to build it between each screen that uses the same collections, and then if I were to change how it's structured, like now, when I am migrating from SharePoint lists to Dataverse, I have to edit every screen. I was thinking of embedding the code into a component that I make invisible on the screen and select when I need to refresh, but this is better!

[@ShanesCows]

Sounds like a great use to me. 🤩

[@jaredt.4048]

Shane, do you have, or will you make, a video on how to mark gallery items as a favorite and then filter the gallery to just your favorites? This would be great for many apps, especially for my facility directory app. Thanks for all you do!

[@StevePhilp79]

Superb video as always. Can’t wait to try this out. Thanks Shane.

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@hellopsp180]

Basically it does not replace collections at all, if the goal was to let the collection be a cache of data that you want to view offline.

[@ShanesCows]

Correct.

[@s6boi666]

Hang on Shane I get your busy but wheeerrrrrrsssss your intro 😉 lol Another great helpful vid thank you from over the pond.

[@ShanesCows]

I loved that part but it got cut. :( Trying to get to the content faster. SOmetimes when I am recording I say it on accident and have to re do it 😹

[@TracyWork]

Very helpful!!!

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@stefancochrane2723]

Many thanks, Shane! Most of our PowerApps are in front of SPO lists. For lookups into other lists (which don't change while a user is updating one main list), would you recommend using named formulas in PowerApps and simple text fields in the main SPO list (instead of lookups in other lists)?

[@ShanesCows]

A lot of times yes. I prefer maintaining the relationships myself and not using LookUp columns normally because that gives me more control.

[@stefancochrane2723]

@@ShanesCows Many thanks! I just completed one PoweApps form with many lookups. In dev, I'll see if I can redo the list and form this way. I'll keep this in mind going forward, too. Take care & Happy Holidays!

[@kellyliu4225]

Thanks for the very useful video!

[@ShanesCows]

You're welcome!

[@sahrchitect]

Hey Shane, thanks for this video and all the other helpful videos I've enjoyed over the past few years! Hope you're having a relaxing Christmas and New Year with Nicola and Chewie 🐶 I've got a refresh issue: I've set a named formula (nf_mybookings) to pull filtered items from a SP list. When I make a change directly in the SP list, I can see by clicking in the formula bar that the nf has picked up the change, however I'm not seeing the change reflected in the gallery which has items set to nf_mybookings. Am I doing something wrong here?

[@sahrchitect]

I got around the issue in a way that seems really dumb but it works (ain't it always the way). I set a timer to 5 seconds duration, autostart, reset and repeat to true. On timer end: If(refresh_1,UpdateContext({refresh_2: true, refresh_1: false}),UpdateContext({refresh_1: true, refresh_2: false})) Then I set the items property of the gallery to If(refresh_1,nf_mybookings,nf_mybookings). Weirdly this forces the gallery to pick up the change in the data which the nf knew all about but was hoarding like Smaug in the Lonely Mountain 🐉

[@ShanesCows]

Happy Holidays to you and your family also. Another way you could do this with a bit less code would be use Refresh function on the data source. This video kzbin.info/www/bejne/nZ68nX1_n62GaK8 talks about not using Refresh but in this case it would be the only way to get what you wanted. :) Good job finding a work around. 😎

[@giorgiosurian2190]

Does it work with SQL view, would they be automatically refreshed? Also, does it make multiple calls if I use the NamedFormula many time?

[@GroverParkGeorge]

Yes, subject to the problem I encountered (described above), when the app runs on a mobile device. I would love feedback on this.

[@paolovr1970]

Thanks Shane 😊

[@ShanesCows]

No problem. Have a great week!

[@randomgoose]

Hey Shane. I can see the performance enhancement for individual app users, but is the PowerApp able to apply the caching across multiple users of the same app... I guess not, but imagine!

[@ShanesCows]

Yeah, per user for now.

[@nacx12]

@@ShanesCowsfor now??

[@Cybermatik]

Thx Shane, perfect video ;)

[@ShanesCows]

You're welcome!

[@TheBatis10]

Has Anyone had any issues where the formulas will work one day and the next day just not work? I have had multiple apps that I had to rewrite the code because the formulas just stop producing the expected result?

[@ShanesCows]

No, that hasn't happened to me.

[@jenskemeijs]

Hi Shane, in the Naed Formulas I have made two tables/collection with the Filter function. The have the same columnnames. How can I make a union of these two? So just stick table A and table B behind each other? I already tried the following: C = Table(A, B); C = A + B; C = A & B; C = Table({A}, {B}); But unfortunately they all give errors. I understand that A + B and A & B aren't working, but I hoped Table(A, B) worked. I got the following error: Cannot use a non-record in this context. Could you please help me? Many thanks in advance! Greets, miss Meijs 😉 Oh Ps. I asked ChatGPT, but unfortunately he was a bit stubborn and kept answering with functions that don't exist in Power Apps or with ClearCollect. Since that is not useable in the Name Formulas I thought Shane is smarter than ChatGPT anyways 😉😉

[@ShanesCows]

I will try to play with this tomorrow and see what I come up with. I have never had to do Union in Power Apps before. If we don’t figure it out I do know how to do it in Automate.

[@jenskemeijs]

@@ShanesCows Thank you in advance for your time! 😄

[@ShanesCows]

Is this what you are looking for? ClearCollect(CombinedTable, Table1); Collect(CombinedTable, Table2);

[@jenskemeijs]

Hi @@ShanesCows , sorry but no. I thought about that myself, but the goal actually was to use Name Formulas and a (Clear)Collect can't be used in the Named Formulas right?

[@JormaRousseau]

Is this a valid way to work around delegation? Using your example, if your source executives was larger than your delegation limit, and you performed a non-delegable function on nfExecutives, would it work? Ill test this myself when I get home, but I am curious if you have any ideas here.

[@AndyHewco]

I would not expect that to work. The issue with delegation is at the data source, eg: can SharePoint fulfill your filter query on the server, or otherwise delegation occurs and 5000 (or whatever your limit is set to) items are returned for the client to apply your filter query on. The data returned in nfExecutives would be the same and subject to the same limitations of delegation.

[@JormaRousseau]

@@AndyHewco You are probably right. Something from the description in the video made me think the source was being copied into memory, similar to a collection, but it is more likely being referenced. I was just watching the video while getting coffee and didnt fully think things through. Ill still mess with it at home later though, thank you for the response.

[@ShanesCows]

Nope. Nothing is going to out fox non-delegable functions. Sorry

[@ShanesCows]

The data is cached but only up to the delegation limit.

[@michelhegeraat5430]

So, when is the function that the named formula uses evaluated or reevaluated exactly? I can understand if you patch a data source in your app, that it knows the data source has been changed. But what if another user patched the data source?

[@LindseyKyle]

Great question ... I'm wondering the same thing. I'm thinking of using a timer that would run, for example a 30 second interval, and change a variable from true to false that would cause the formula to re-evaluate.

[@michelhegeraat5430]

@@LindseyKyle In an ideal world you would be able to get notified of a change in a data source with a trigger, like how this currently works in power automate. Annoying to know the “hard part” of making that is already done but Microsoft probably doesn’t see the point of providing such functionality.

[@ShanesCows]

It does not know about things done by other users. Sorry. Each NF is only applicable to that user in that session.

[@michelhegeraat5430]

@@ShanesCows Thank you Shane, I have button that generates a new order number, based on the previous order + some rules. It worked fine since june 2021, until yesterday when 2 people hit the button at the exact same time. 🙂 NF won't save me, I will need to think of something else, where both people first generate a record, and then based on the ID generate a new order number.

[@GroverParkGeorge]

@@ShanesCows Can you work around this by adding a Refresh() on the data source before invoking the Named Formula? As is liberal use of Refresh a bad idea?

[@TheKermit2110]

hi buddy, watched my 1st vid of yours since new year. This seems to have enormous potential if delegable. Can i store a filtered table to subsequently ref in another named formula? I was always weary of doing this with collections because collections are apparently non-delegable? Especially powerful if it auto-updates the patching without having to add another clearcollect!

[@geralddahl9159]

Can a named formula include a ForAll? I’m hoping for more elegant code. (Shorter expressions inside buttons.) My citizen-developer brain gets lost when I’m thinking through the logic of long formulas and I’m wondering whether named formulas represent an effective way of compartmentalizing things.

[@ShanesCows]

Nope. They are working on making reputable functions but not yet. Technically you can use ForApp to free a NF but not the way you are thinking I am pretty sure.

[@HeatherR-d6i]

If you have a named formula that gets used on a screen, it gets the data at that point. If you go to another screen that doesn't use the named formula, and then go back, is that a second request? So each time you access the screen with the named formula it's a new request?

[@ShanesCows]

I do not believe so, I believe Power Apps will cache it from your first visit. I guess I should test it but pretty sure. :)

[@ericgeeeee]

@@ShanesCows following to get the answer 🤩 I was looking for this comment because I just wanted to be conscious of the api calls, especially if there are multiple records to be returned.

[@big_cheese2162]

Hey Shane, where would you add variables that you do want to be able to change, if Formula's can't handle variables that change? Since OnStart is being depreciated

[@ShanesCows]

You could put them OnVisible of the first screen if you wanted. I still use OnStart. I know they want to get rid of it, but there isn't a perfect replacement yet so I keep using it. They tell me they will not get rid of it until there is a perfect replacement. 🤷

[@big_cheese2162]

@@ShanesCows Thanks :) In the last 2 days I've been using a combo of OnVisible and Formulas, as well as components, which seems to be doing the trick!

[@richardfarland]

As per other users here, we've found named formulas to be unreliable in firing, mainly in Edit (not so much after publishing) but that makes testing a pain. Also, calling them Named Formulas is a bit of a misnomer, they are not - what I REALLY hoped they would have been - a means of encapsulating code reuse. So we can't call behaviour formulas from them. They are just dynamic variable references. Greg Lindhorst hints at this direction in his blog, but perhaps it's a step too far in the dreaded "imperative" direction for his liking. So it's the clunky canvas code components for now...

[@roelmarten3969]

I'm trying to create a monthly report of an inventory management system. Any idea how to do that? I'm searching online for help but nothing.

[@ShanesCows]

I would be looking at Power BI. It is the best for reporting.

[@glennbullion9069]

This is a "live" feature, right? Not a preview? No need to go into app settings and toggle a slider to turn it on?

[@ShanesCows]

Correct 👍

[@NecroWorld]

Leave it to Microsoft to have two syntax systems in the same environment. Outside of that, thanks for the video.

[@kurtbarber5117]

would that new executive record showed up instantaneously if it was added in sharepoint? or dataverse

[@ShanesCows]

No, Power Apps caches that info until you cause it to be refreshed. Typically by changing another record.

[@iamintractable1805]

Changes to a dataset (SharePoint in my case) associated with a named formula are only automatically recognized by the named formula if the dataset is updated by the app making the change. Other users running the app or direct changes to the SharePoint list will NOT be seen by the named formula. Developers will have to explicitly refresh the named formula to see those changes, This is a major drawback. I attempted to replace a collection with a named formula and while I am sure it will work, the Named formula is subject to delegation (displaying triangles) where a collection is not. So, it does not seem practical to replace a collection with a Named Formula. If you disagree, tell me why?

[@ShanesCows]

Collect and ClearCollect are NEVER delegable, even though they don't warn you they are not. Same for NF they are never delegable. So they have that it common. You are correct that NF, nor collections, or any use of a data source know about changes outside of the app. :( So I would say you have the same challenges either way. NF with a table stored in it is really just a collection that is maintained by Power Apps instead of you. But all of the rules of what they know or can do are basically the same. I hope that helps.

[@iamintractable1805]

​@@ShanesCows A delegable function is one that can be executed server side. When a Filter action cannot be executed on the server, the app will get the first max number of rows it can (2000) and then apply the non-delegable function. If a filter is fully delegable, it will be applied to all rows server side and the first 2000 qualifying result rows will be returned. Most functions cannot be sent to the server (Dataverse or SharePoint). So there needs to be some creativity in what you push on the server and what you apply to a collection. Here is my issue. I have a 800 item list. When I pull all the list rows with a simply ClearCollect no delegation warnings are visible and I can then further filter the collection as I see fit. When I do the same thing where the list rows are assigned to a Named Formula, any subsequent filtering on the Named Formula shows a delegation error when the function is not delegable. So all a named formula is doing is creating a new name for a data source, nothing more. What am I missing?

[@raindenverraindenver717]

But, if you will set "items" of gallery Filter(Emploees, Department="Executive") , the gallery will be refreshed also as without make a formula

[@ShanesCows]

True

[@DanielWu-rh8ki]

If you patch in app, don't you need to Refresh(Employees); in order for it to refresh? In my apps I had to do it as they don't refresh after the updates, but I don't know if MS had made an update of that, or my Filter() is more complex?

[@raindenverraindenver717]

@@DanielWu-rh8ki No need to Refresh

[@GroverParkGeorge]

Nice! Thanks.

[@ShanesCows]

You bet!w

[@jpgdesign]

I have an experience that named formulas sometimes doesn't update when using longer more complicated formulas.

[@ShanesCows]

Yuck. I haven’t ran into that.

[@toanbui3810]

I face the same issue in Edit Mode, but after published the app to end user, not received any related feedback so far. Anyway i have another issue with named formula, I uses sharepoint list as data source. When doing filter like UserName = User(). Name, i got error bc actual name of UserName in SharePoint list is Title, everything fine when replacing UserName by Title. I saved the change and published the app. However, when i get back to Edit Mode, nothing changed :(

[@dawnpeacock9548]

Can you use the "//" to add comments to those?

[@davidoliveira8412]

Toma aqui esse like seu lindo

[@ShanesCows]

🤩

[@BigLeafDropper86]

No need for onstart I feel, use a splash screen and perform logic post app load

[@ShanesCows]

That works. 🤩

I moved all my dropdown values to fx Named Formulas

[@ShanesCows]

Love it!

[@innov8iv]

Hi, Haylee

[@ShanesCows]

She will be so happy! Thanks