Excel like freeze panes effect in Power Apps

hey everyone this is daniel and in today's video i'm going to show you how you can create an excel like freeze panes effect in powerapps and i'll walk you through the whole thing where first we'll go ahead and just create a tabular view where the top row is hidden so that the headers always stay there second i'll show you how using the mod function you can go ahead and actually give even the color effect where each and every rose has a specific color and then finally i'll show you how one column will also be frozen but the remaining ones can slide back and forth giving you that full fledged excel freeze panes effect so stick around but first here's my intro video so let's get started and here is just an overview of what i'm trying to accomplish is that i've come over here it's a very simple table but in excel we've got the power to go ahead and actually format it and based on the formatting i already get this look and feel but i can take it to the next level where i can go in my views and i can go into the freeze panes and i have these options which just freeze the pain freeze the top row go ahead and freeze the top column those are the scenarios that i'm going to replicate in the powerapps so now do you understand what i'm trying to accomplish great so let's now go and get remove this and i've come over here in my powerapps now this is my powerapps canvas studio and i went ahead and made this layout so i can actually show you the full effect all right now the first thing i'm going to do is i'm going to go and connect into a data source and my simple and easy powerful data source i have over here is sharepoint so i'll go ahead and search for sharepoint i've already got a list i'll go and grab that list and at least i have the data to work with so anyway here going at my test site in my test set i have something called devices and there you go i've got a good list to work with now the first thing i want to do is actually go ahead and have that excel where i can go and create that table and then i can even give that table uh look and feel so later on we can stop the freeze panes and how do i do that so what i'm going to do is in my screen i'm going to first get you all comfortable with the concept of containers now the reason i'm going to use containers is so that i can actually even start having the scrolling functionality because as you know in galleries i don't have the functionality to put in scroll i have it in tables but i'm going to show you this trick now where you can go ahead and use the container functionality and with a little bit of tricks you can go and actually put a gallery inside it and make it look very similar to an excel table but also give you that scroll functionality and then also show you the next trick of how to use the mod function so let's let's all get into it right so i'm going to come over here now i'm going to click on the plus in the plus i'm going to go into layout and in the layout i'm going to go and select the vertical container so that's what it is vertical just remember that that's how the columns are showing up over here all right it's vertical as when they're dropping one about each other and what i can do is i can go and make sure that i just select it over here and go and kind of spread it out but you can go and put in some formulas with the app width and just making sure that it's completely nice and um filled up over here so i'll just leave it as is all right the next thing i'm going to do is inside that container i'm going to go ahead and add another container now what i'm going to do is inside that container i'm going to go ahead and add another container why are you doing that daniel it's a good question what i'm going to do is i'm going to make sure that i need to accomplish two things first of all in that freeze pane effect i want the header to be frozen the top row which is going to be the header that's going to be frozen so i'm going to accomplish that over here but at the same time when i'm going and scrolling that right you know to the right uh both the header and the data i should be scrolling together and how do i accomplish that well that's using a combination of me adding a sub container and then the gallery so what i'll do is i'll go ahead and first just add my sub container in here so i'll add that and then inside it go ahead and add another sub container and it's automatically done that way because once you've selected let's go back outside once you've selected that one anything that you add afterwards automatically goes and adds inside it but here's the thing i'm going to now add a horizontal container and when i did that and go back over here now you can see that i've got the horizontal container so for my knowledge over here i'm just going to put that as header container so i know it's not the header in the footer this is the header of the table all right so i've gone ahead and taken care of that but next we got to figure out is i need the header just to be on the header so how do i go ahead and associate all of this for that we've got to go ahead and actually do a little bit of manipulation so inside this header container which is a subcontainer let me just start adding some labels which are going to be my header so let me do that and then we'll go ahead and manipulate the actual layout of it so i'll come in over here and now i'll go ahead and select label and that's going to be my first one so the label in this case is going to be device id because that table which i'm bringing in this spreadsheet one it has an id it has a manufacturer so over here i'll just go and put in device id and then in this case you can go ahead and name the column as well which i mean the name the control so i'll just say device id and then that's what it is and then also i'll be doing a lot of copying and pasting so i'll just make sure that this is a nice color it has all the look and feel that i want and then afterwards i can start doing some copying and pasting now this is a horizontal container so the moment i go and do a copy and then i go and do a paste it'll automatically put it right next to it and i'll that's the effect that i like over here so now i can go and start putting in the other one as well so this is going to be manufacturer and then so on and so forth now keep in mind that they're still all inside the header containers over here so i'm going to quickly scan through and start working on putting on the other ones okay so i've gone and put in the manufacture one i'll go ahead and just rename that as a manufacturer manufacturer all right got it and then i'll just go and start doing some copying and pasting so control c control b this is going to be our device type that's my device type and next again control c control v go over here this is my mod model m o d e l next keep going on control c control v i'll put in a few more because i need to have enough columns for the sake of the scrolling functionality and i just can't put enough you know one or two because that effect of the scrolling goes away so i'm getting close i got the memory i got the hard drive hard drive i'll put in say processor control c control v and i'll be changing the width of these as well so that's why don't worry about you know like daniel how much data do you need for this thing to scroll don't worry about it it's all going to go through just fine um and i think for now i should leave it at that maybe i'll put it in one more we'll see the the screen width so i'll put that in as are the screen um size i'll leave it at that and this is enough for us to work with right now now right here is where we're going to start doing some um setting changes because if you don't do these setting changes it's going to cause you a little bit of um you know confusion as to how you're getting that effect so on the header now this is very key because you got to first make sure that you select the header don't select any one of these select that header and when you select the header you get this feature and functionality all right so what you first need to do is two things this is the stretched one i want to go ahead and select on the left side which is start so it did that which is perfect but the second thing i need to do is i need to go in and switch over the switch off this flexible height functionality because that now will make sure that it doesn't fill up the container because if you remember the second container automatically populated the entire first container and that was what was causing the information but we'll even you know adjust that next is make a guesstimate right now guesstimate is how wide do you want to go um so come up with something you don't have to be completely accurate at this time but what i've done is at least for right now i'm going to put in say i'm going to go outside to about a 1700 let's actually do it at 1500 i'm going to come up to a 1500 so as you can see it has already started going outside which is great it already went and served this purpose but the last thing i want to do is i want to manipulate this header um height because it's consuming a lot more and more when i put a gallery below it the gallery is going to start right here and i don't want that and the best way for me to accomplish this is this guy his header i'm going to go and um change its height so i go out over here i search for its height but i'm going to remove this 200 and i'm just going to go ahead and reference one guy that's why i make sure that the label um device label i had him so i've selected it do double click do a control c click outside and go to the header and here i'm going to paste it and put in height and now that control went ahead and the container went ahead and populated that way so this is basically how i wanted to accomplish is that get that and move forward with it all right cool made a big step forward and now below it i'm just going to go and add a gallery right now i'm not going to put any changes to it yet let me just add the gallery because i'm going to go ahead and accomplish two things add the gallery inside this container but i got to make sure that it sits bright below that to give it that effect because this is going to be our header column over here so i'll come in and the gallery part adding it is again simple i'll come over here i'll go ahead and add the gallery it went ahead and automatically see that's what it did it went and posted it inside and we don't want that i gotta go and cut that first go outside click over here come there and i'll paste it and now i know that it is situated in the right way also immediately realized how this thing went below it and that's the perfect way to do it so before i go and you know select the data source i want to just go and select one item right now then i can go ahead and apply the data source and that's where at least i get that information i'll remove any other things that i don't want for now but i will go ahead and now sort this out in a way because i'll be doing a little bit of manipulation but you know at least get it a nice look and feel so i don't want it too much over here 18 i'll leave it at that and in this case this is just going to be my id column for now so come back here and in my devices i will look for item dot id and i'm gonna pause over here for a minute and say that hey for the sake of this example i've used the sharepoint list and id uh you know that id is a number column and therefore in this case i will run it the delegation at one point and i'm gonna use the filter function um there is the workaround for that but i'll mention you know i want to make focus over here so ignore the delegation which comes in over here uh the focus of this is the formatting and the look and the feel cool all right so i'm going to grab that and at least mark you know align that a little bit and we'll just go ahead and move forward now with the other ones so i've got this i'm going to go and do a control c a control v and i'll start matching at least a little bit of this information so here now in the dot i'm going to put in the um manufacturer name so that's what it is acer got that i'll go ahead and at least align it a little bit we're going to actually go and do some uh most thorough alignments um making sure that the formatting is correct so i'll put in this device type and in my device type right there grab that go and grab the model ctrl c ctrl b right there in fact the model name for here is actually the title in the sharepoint list so now we've already run into this one interesting problem see i need to expand it but because of that container effect i really can't do anything else to it well then what's the workaround for that let's just match what we did for the container in the container i went and put that 1500 i'll do the exact same thing for the gallery i'll go and put in that 1500 for now so at least we've got some kind of similarity other thing i'm already noticing is that i need to go ahead and adjust these columns over here so what i'll do is i'll go ahead and at least start adjusting the width a little bit so i'm coming over here and i'm going to start doing the width and the width i'll set it up to say 100 for now all right that's good and then i'll match this guy to that this is 100. uh manufacturing should be good device typewriter should be good model i'm going to have to expand that a little bit and see i just can't do it this way right here and you know move it off manually for that you got to go ahead and actually manipulate it directly through um the property over here so i'll double it i'll go and put it as 300 for now because i know some of the model names are a little bigger so it's kind of important that we start aligning these over here so i'll do it like that nice and clean because there are some of the columns over here they are going to fill it up all right so now one of the things i'll pause over here and told you that in the gallery we can go ahead and get its clean height and the way we can do the clean height is through this very simple formula because what i want to do is i want to say that hey fill up the height of this gallery but leave that little space on the top and the way you do that is because because the y position of this is automatically situated because we put it in a second item in the container it's why is already taking care of that so basically what i'm trying to say that hey this gallery go ahead and give it the height of the app but subtract its y location and that's the very simple formula so i'm going to come over here and i'm going to use the height and in its height it's by default 575 i'm going to go and say give me the app's height right there and then subtract based on this gallery itself y location and to reference an item when you're using the item the best function to use is the self so i'll just use self dot y and it went and selected it and now you notice it automatically went and filled it up it's a very clean and efficient way to do the formatting and that's why i wanted to kind of use this as an example that hey here's how you do the y over here all right so let me just keep moving forward and i'll kind of speed up a little bit on this so i'll come here i'll do ctrl c i'll do ctrl v and i'll do the memory here so in the memory i'm going to go ahead and add memory got this functionality and now we're going to expand this a little bit more too so it's width i'll go ahead and say i'll put that up to about 200 so we're getting that and then this one i'll at least shorten it now i'm going to go ahead and match these up a little bit more cleaner so let me just move i'll take care of all that so again ctrl c ctrl v going ahead and grabbing it it's hard drive space here in fact i think this is called a storage so we got the storage cool we are making some amazing progress now processor is going to be bigger because it's got a bigger more text so i'll start with first coming over here and making its width i know i'm going to have to double that so i'll go ahead and please double it to 300 right now uh my let me go and just be on the safe side and double it to increase it to 400 so i'll grab this i'll do a ctrl c ctrl b and i'll put this in i'll go ahead and expand its width 2400 for now and obviously you get the correct column so i'll go ahead and select that select that and that's going to be the processor so at least we're filling up see i already need more information over here because at 400 just wasn't enough but that's fine i'll go ahead and expand that now there's a couple of things that has happened over here first of all is we're already coming into this roadblock of the distance let's see right now i know i went and put in for uh 400 but it's blocking and it's making it s300 so what do we do with stuff like this first thing we want to do is actually go ahead and make sure that i have us align these properly and to do that i got to go ahead and use the nice technique that we've used is use the x of the original one and then go ahead and put in the width and to do that always use the next one so let me explain that to you in a much easier way i want to make sure that this one is always after the the on the second to the right this one is always after this one and so the reason the best best way to do it is first select the one on the left i'll go double click on it do a ctrl c and then i come back over here and then in here i go and select it all the way to its x and now i'll just paste the before one i go and get its x plus and then paste that one again and i go and grab this width so that's how it's going and aligning it nicely now in addition what i do is i go ahead and now select that one double c i mean double click ctrl c and now i go to the next one and in the next one i go and look at its x coordinate so i go and grab the x coordinate control here x plus control width and there you go it's nicely aligning up now here i got to go ahead and make sure its width is a little bigger so i can assign it to that but when you move it around you just got to make sure that it's not too small um and it is aligning up to the device so you could take it to the next level by first matching up you know instead of matching it to this one you can match it to that x coordinate over here but it does get a little confusing so i kind of always match it to the x coordinate of this location over here which is this one so if you that what i just said got you confused ignore that just pay attention to what i'm just doing so i'll select this one again okay i first select this one come back over here do a double click control c go to the next one and then in that next one you go to the x paste x down here plus and grab the width so let's keep going now i selected that one ctrl c go to the next one it's x right there there x and keep moving forward ctrl c now this one's width is a little bigger so in fact i kind of this one's with a little bigger so we can make that and now this one should go ahead and populate that all right grab that here it's oops it's x interesting thing to always see is how those numbers are growing because you now very soon know that you're going to be expanding out of this location you know out of this one over here so for example right now it's x i mean if i come over here now i've selected that one double click on it but if i go to the next one you see how its x location is it's x location is already at 1055 so it's very interesting to keep track of how much you know what is the distance that you've already gone outside okay so now it is moving outside now i also got to go ahead and say the processor is there um next thing i went and put in was to go ahead and even grab the uh the screen size so let me go and just make sure i've got the screen size in um yep i did add a screen size so here i need to go and add a screen size as well so i'll go ahead and click on that ctrl c ctrl b now in this one that gets a little sketchy because i've got two so what i need to do is i gotta first make sure i select the one on the bottom all right and i do a ctrl c for that so double click ctrl c now when i go and click on the top one i know that this the one on the bottom was already a processor because i can see it here the one on the top that is going to be my screen size so i'll do the screen size get it but i can also go and make sure that its x location is correct so this guy's x location is actually going to be right here x plus with so it already went outside a little bit but for now i'll go and leave because we gotta go and manipulate now the entire gallery section so watch this things are gonna get very interesting so i go ahead and come and click on the header container over here leave this one as us click on the header container just make sure that everything is good you gotta go ahead and make sure that that wrap is there give that number just make sure you confirm this in case for whatever reason yours is not looking at mine two things over here stick it on the extreme left and go and turn this off now come to the topmost container and in the topmost container you want to confirm these two things first of all the top location over here justify should be on the start align should be on the start but here the horizontal scroll overflow go here and you add the scroll and voila you get this functionality and just to quickly take a look at it you can click on play and you see that scroll functionality is coming in and that awesome that really makes me happy now look over here look at it and kind of understand okay you couple of things we haven't thought of well one of them is remember initially we gave an estimate of that width was 1500 i think we need to bump that up so i'm going to go with 1700. now remember a mistake which i have been making i don't want you to make it is don't modify the top one the top is container level don't modify that because at the end of the day this container is just going to be the overall screen that you see it's the one in the bottom that we're going to go ahead and actually allow it to scroll now granted the scrolling is on the top so don't get that confused the scrolling function is going to be on the top but everything that you see is always going to be the width of the app the bottom one that one it's with you got to expand it because that's the one which is going to scroll so this is the one where you come in and i'm going to go and bump that up to 1700 and the other thing that i've noticed works really well is i match the container the inside container and the gallery to the same width and when i do that the overall look and the feel is a lot more cleaner so if i go in now over here you will see that i've got a lot more scrolling functionality but now that we've got the scoring functionality let's go and clean this up a little bit so i'll come here and i'll go back and expand on my gallery this one which was the overall processor we'll leave that as this but i'm going to double click on that and then the one on the top i will go ahead and make sure that it is also aligned properly so i can't really see it though because as you can see now when you scroll it looks a little messy but that's okay because what happens is that we are controlling the whole thing using these properties and controls over here i'm not clicking over here and manually doing it the moment you start doing that things get a little messy so this is where you gotta start you know focusing on the accuracy of the x locations and the wire locations and here's just a great example because check this out um i want to go ahead and manipulate the width of this processor but if i click over here i have actually it looks like i clicked on it but i've actually clicked the um memory so the only way i can confirm that i am in fact clicking the processor label is i come over here click on it and then i see yep that's the processor one and what i'm going to do is i need to go ahead and manipulate that width a little bit so it's at 311 i'm going to bump that up to 400 that looks a little nicer now it goes and bumps that up over here all right so that is the processor one which i click i'll double click on the processor and i'll go back now to the screen size and it's screen size width i'll put that over here to x up there to width and now it looks a little cleaner now for some reason this guy is uh when down fact was in some reason this guy's width the processor one was a little bigger so let me just go and click on the processor and go and grab its y location so my location is eight so i'll come over here to the text and i'll go grab its y location see that's what happened so we're making you know it's making progress but check this out we've just accomplished something awesome is we've got this functionality scrolling functionality and also because the top one is in a container but this section over here is in a gallery we also got that effect over here it's like whoa this was awesome i mean i'm excited because we just replicated an excel effect directly over here inside powerapps and it looks a little bit more accelerated but you want to take that one step forward is danielle how do i get that excellent type of look and feel i'm talking about you know this excel over here the coloring which are a little different and all that how do i get that effect and to do that it's actually really simple it's using the mod function now in the when to use the mod function you got to go back and focus on your gallery and in your gallery you actually go to what is called as a template fill now if any of you who have seen me in workshops or you know how we build the canvas apps uh you know that this there in fact if you've done the app in the day you know that there is this if function with the this item is selected um so what basically what it says that if this item is selected which is always the default on top but if it is selected go ahead and give that specific selected item a color and whatever that color may be so we are going to continue using that if functionality but here we are going to use the mod what mod is it's very similar to divide but divide if you divide something with the something it will give you the remaining amount plus the balance mod is basically doing a division but it says hey if it is even give me a zero if it is not then it's not a mod i mean it's it's not zero and that is what we can use to find which are the odd and the even numbers and then based on the results we'll be able to go ahead and put in a color so if the mod returns a zero it's an even number which means i can go and apply a color over there so if your wheels are already turning you already know daniel i need a number to decide this and i should hopefully already have a number for each of the items hey a sharepoint list item already has an item id so i'm just going to leverage that but in case you are doing this say on a sql site well sql if you've already generated its primary id then you've got a number over there as well but if you're doing it somewhere else say for example excel sitting in onedrive then you've got to factor that in this i need some kind of primary ids to do this mod functions kind of keep that in the back of your mind all right so here let me just go ahead and put in my formula so i'm going to put in the if and if i'm going to start with mod and mod is a function so what i'm going to say mod this i this item right here this item id if this item id comma 2 which is saying the divisor see that's the number that's the divisor so in my case it's 2 and if i always get a 0 out of that then give me a light blue which is right here otherwise just keep it to that color and check this out moment i close it bam right there i already got that excellent type of effect so it's pretty awesome just to show you it goes ahead and actually grabs this entire effect it gives you that excellish effect so we've already accomplished two things on the top and the bottom i've got the scrolling functionality the top header is already frozen and i gave you that excel thing so if you already accomplished the first and the second one gave the excel one acceleration look and feel over here with the scrolling functionality and we went ahead and added the freeze pane effect for the top row now the next one is not that easy because what i'm going to do is i'm going to go ahead and take this model and then the model i'll put that all the way to the left and that will always be frozen all right which means it's not going to slide back and forth but the remaining items will slide back and forth all right so you might probably think daniel that's fine i'll just put a gallery over here i'll put a gallery over there and it'll work but here's something that is really not as easy the second is if i'm scrolling this side which is scrolling one gallery the other side will also scroll but it will make sure that it has that mirror functionality well how do you accomplish that because trust me i have spent a lot of time thinking about this in fact i went ahead and you put it i'm definitely going to use the gallery effect for that no no doubt about it but how do you make two galleries scroll such that their scroll is locked together so if one section is scrolling the other section will scroll the exact same number of rows at the same time that was not easy and therefore i had to think outside the box a little bit and the thing outside the box was i completely removed the option for the gallery's manuals flooring and i went ahead and added an external controls that is what i was controlling this floating and i put a different put the filter functions over there so i'm gonna i'm getting excited i'm gonna slow down let me show you all right now before i move even forward one thing which is bothering me is this entire width away so daniel you are losing so much real estate space in fact you could probably see twice the number of rows over here how do i fix that and for that you make sure that you're going for select that gallery and in that gallery then you come to what's called as the template size and this is where you do some manipulation so what i'm going to do is i'm going to stick the minimum to say about 45 and i'll remove this and i'll remove this section so i'm going to just keep it as the self height and there you go we literally was at eight now we doubled it and we got that 16. so this is that section is use the template size and then just manipulate the formula a little bit um 45 was i know it's a hard number i've used but that is what worked for me in your case you can go and match it to something else so now with this you know look and feel if you just go back and take a quick look at it oh man it looks so much nicer scrolling back and forth a very excellent type of effect right so now let's go and now work on that final piece over here so i'm gonna first come in and let me go and save it just just in case anything happens i don't lose my work so i'll come in and now i'll create a new screen so screen two is very interesting because in that case we are going to use the container functionality but i also have to use things outside the container and for that i need to kind of first let me focus on those things outside the container then we'll go ahead and get the container piece in so for that i'm going to go ahead and now make sure that for your knowledge sake i'll put in two items um i want you guys to see that id column because i'll be referencing that and in the id i'll just go and fill that up go ahead and give it that color which is right there and i'll just you know yep well id label that's fine there and i'll just make that a little smaller and then ctrl c ctrl v this is going to be our title and just to make sure i got that correctly it was actually our model so let me grab all that and i'll change that to model so what i'll just do is i'll go and put the model label here that makes it easier and then below that is going to be a gallery so what i'll do is here i can come in to the next screen and i can just go grab the copy of that gallery and come back over here and i'll go paste it and then i can always go ahead and do not duplicate it delete it paste it cool so in this case all i want is i'll go ahead and keep that id because i'll that way you can always see what's going on um this one i'll delete that in fact you know what keep that one because all the other ones are referencing it so just let's go ahead and delete the other ones so right here this one this one i'm holding my shift button down by the way so see that's why i was trying to just hold on to these two only because that's why this one came up so i'll just delete that one awesome keep that over here and then acer the one which is right there i'll go and change that to the device name and make it a little bit bigger just align it nicely there there make that a little bit bigger because this is going to fill up a little bit in fact for here what i have to do is i might actually go ahead and even uh um make the font size a little smaller but we'll we'll cross that bridge when we get there actually we need to do that right now make that say 16. okay make it a little bigger because as you can see brown over here it is overlapping and i don't like that so i'll come back here make it a little bigger okay very good so this is good now this gun had its own scroll functionality we'll deal with this because that that's going to have to disappear so we took care of this section over here now we got to go ahead and focus on the right side so focusing on the right it's fairly simple what i do is i go ahead and grab this now there is a slight bug into this and i'm going to report that to microsoft but when i go and copy this and paste it over here a couple of things are going to go crazy watch this is what it looks great right now but when i copy and paste it it's going to go a little messy so i come in i'll come over here to copy here and i go paste it first of all it went and populated the whole thing it went and filled everything up we don't want to do that what i'm going to do is i'm going to go to the container i'm going to go to it's x and in its x i'm going to say go ahead and start your x after the gallery so it's right there plus again gallery 1 underscore dot width and that one i'm just going is the gallery that we created so i entered and confirmed that and now it does look a little cleaner now if you notice the inside things went down and got a whole little crazy over there i didn't like that it's doing that i've gone ahead and reported that to microsoft i said hey there's something fishy going on inside these containers i had a nice clean look and feel on the top one it went and got messed it up so i'll go ahead and fix that really quick and it's the same process that i went ahead and you know put in the x of the previous one which is right there inside that sub containers go ahead and always take the first one find what the value is and then go ahead and kind of sort that out sorry not inside here but inside on each of the gallery ones click on the gallery item don't just go looking over here click on it and then you'll be able to see what it is so give me a few seconds and i'll come back all right so we are back and all i did was the exact same thing you saw me do over here for this container specifically inside the gallery as i went ahead and selected the one on the left which was in this case one on the bottom selected that did a double click ctrl c and then click on the next one and in its next one when you do the x you type paste the name plus and then you paste the name i mean paste the name dot x get its value plus same name dot width so that's all i was doing over here too in that gallery i went ahead and just followed the same process and that's how i was able to clean them really rich to copy and paste would work but fear not i have gone ahead and reported that to them so already you are seeing something very awesome is i've got one on the left side it is static or it's frozen right the column got frozen see that excel is excel-ish effect that i just talked about now this one does scroll over there static column this one scrolls again giving the column effect we do have a problem though because over here if i scroll this one down that one doesn't scroll uh but hey after a while what i thought i was let me just step back and think outside the box because what am i trying to accomplish i am trying to accomplish scrolling down that's all i've seen like the first 15 or the first 16 items i want to see the next 15 items so instead of me manually scrolling it i said i'll go ahead and put in some icons over there and when i click on it it'll go to the next patch and so on and so forth and then same thing if i click on the top it'll go to the previous batch and so on and so forth so i eliminated the scroll functionality and i went ahead and added the external scroll functionality and that's how i was able to go ahead and create that lock effect where both of them are scrolling at the same time and that was what i do i didn't think outside the box for that so you're gonna love this next step that i'm showing all right so let's go back in now and couple of things i need to do is i can go ahead and clean this up a little bit so right now in the in fact right in the header over here since initially we had 1700 at the top but now our x location is a little bit more on the right you got to bump this up to whatever you think is right so i'm going to actually go to the 1900 and if you remember when i just said it's nice for that container and the gallery to have the same amount so right here you got to come over here go all the way down to the width it's at 1700 i'll change that to 1900 so let's go take a look at it how does that look yeah it's actually pretty good i mean i can go ahead and now you know match up to this up right over here but i was able to get that and then i mean this is the easy part you already know like and then just just as a quick you know step i'll come back over here i'll see that that's the uh the screen size one so it's the one below the one below is the processor one so i can go ahead and bump that person's width up a little bit so i come over there i can increase the width to now say 450 450 and that goes out a little bit and that goes out a little bit and it gives you that much cleaner effect so kind of play along with that piece and you get the entire site the screen size as well but our focus is a little different our focus is now to make sure that they scroll down together so first let me just go and get some icons i'm going to click over here and as you know icons i just click on it and then i go and click on the first one i don't even care which one it is because once i've got my icon that's when i go ahead and search on it so my icon here i'll go and click on that and this is going to be the refresh one so i grab the refresh one do a ctrl c ctrl b this one is going to be the top which is arrow arrow going up on the top it's actually up not top control c control v and i'll take that down a little bit and this one is going to be arrow down all right so we've got the scroll functionality next i'm going to actually click on these ones where both the galleries and i'm going to hide the scroll right there click on that gallery right there and i'm going to hide the scroll oh and then made it invisible no daniel make that visible again i'm going to go ahead and hide the scroll so took care of that all right one thing i do is instead of going to the preview if you hit the alt button now i can go and get that back now i want to kind of show you something over here it does look a little messy to some extent because we are going to make sure that they scroll side by side right because they have to be lined up together but they are not they actually look a little messy so how do you go ahead and fix that the fixing of it is actually to go ahead and make sure that they align up so what i'm going to do is go play and make sure i scroll up a little bit and you see that over here we are off by just a little bit so what i'm going to do is instead of bringing this one down i'm going to take this one up and that is all inside our it's on screen number two it's all inside this container so let me go to the y now this one actually y is good because it's already on the top it looks like this one is a little bit on the bottom so let me actually go and grab um the cont the icons and go and grab the gallery here and the gallery its wire location i need to go ahead and make it a little bit bigger so that 38 all the way to 40. and now if you take a closer look it's like yeah that looks a lot nicer and sure that labels and everything you can change that but it's the table effect the one on the left and the one on the right should be as if they are the same table um or the same gallery but this one is long so i kind of wanted to point that out because you know it it doesn't look good in the eye it just doesn't look good in the app as well cool so now we want to go ahead and now add some variables because we're going to control the whole thing with the variable also make a mental decision right now of how much you want to show at a time because what i'm going to do is i'm going to be constantly showing in the bunches of or in the bunches of 15s and the reason you want to make that decision ahead is because all these scrolling down or the scrolling up we're going to do them in chunks of that number so in my case i made the decision of chunks of 15 so over here i'm going to add a number to decide what is the cur i'm gonna use two variables i'm gonna use a variable is what is my current number and what is the next number so here here we go on on the app on start i'm going to go on the onstar and i'm gonna put in two variables and i'm gonna say set bar current current comma 0 semicolon and i'm going to say set bar next now remember i said i'm going to do it in chunks of 15 which means after 15 my next number is going to be 16 so that's what i'm doing the 16 is over here now don't always think of it as well after you do 16 15 there's 30 so i need to put in another variable for 31 or you know 62. no it's not the reason you're just getting chunks of a certain number which in our case is 15 which means i just want to know the next quantity number which in this case is 60. so kind of keep that in mind all right and then as you've already guessed when you hit the reef of the refresh refresh is basically the exact same thing you had in onstar so i'll go ahead and i'll highlight it come over here paste it so i've gone and taken care of that refresh and then here i'll go ahead and actually do something on the top and then do something on the bottom and i will control these two so let's let's start on actually the bottom one because we're going to be going in bottom so in its on select i got to go ahead and the current value is 0. so i got to go ahead and add 15 more and then in the 15 more i'm going to go and say if this is the 15 then the vr next should be again what is the current value plus 16. so it's a little confusing but let me write it down over here and then you'll see it so once i've selected the down button i'm going to say my vr current is going to be whatever is my current variable which is this one here plus 15. that's going to be it and if you realize previously for the onstart we had 0 and 16. so over here it's 15 the next one is also going to be plus the 16. so that's what we're going to do now in the next one you've got to go ahead and update the vr next but you're going to use the vr current and the vr current you're going to put that to 16. so keep that in mind if you're walking side by side over here this one the one on the left you're going to use the set vr current and its value is going to be the vr current plus 15. the vr next is also going to be vr current plus 16 so keep that in mind all right and while we are doing this i'll go ahead and actually update the one on the top as well the one on the top to scroll up is a little bit different so what it does is i'll be scrolling up but i just want to make sure i go scroll up to the next 15. i don't really care about the we next over there i just want to make sure that i'm going up to the next 15. basically over there you don't need to see whatever after 15 what is the negative 16 or the negative 17 number you just got to make sure that hey i need to go up to the 15s therefore in that case i will be coming over here and i'll go both for the vr current i'm going to go to vr current minus 15 and then back over here set we are next it's going to be the vr next minus 15. so on the top it's the exact same thing you take the same variable subtracted by 15. it's going on the bottom we're going to keep using that vr next one all right so we are making some great progress over here what we got to do now is focus on how the heck are these galleries going to work so let's start with the one on the left it's all about what you're seeing so here i'm going to now go and do some manipulation in the items now in the filtering we are going to use that variables the next the current and the next and i'm going to use the ampersand and function in between to always make sure that i'm seeing one chunk of those 15s and that is where i can go and make sure that this one and this one matches and gives me that exact you know match over there so i come to the filter comma and over here i'm going to type in the id id number which is the same thing as the device id in this case it should be greater than the vr current and all right not r and it should also be less than the vr next now do you see why i put that plus 16 at the beginning instead of 15 it's kind of making sense all right so it's going and doing that now i know it went ahead and actually you know disappeared but watch this if i go outside over here and if i just refresh that it showed up and only the 15 over there showed up because that's the number that we came up with so i'm going to now go and do the exact same thing for the other side and it's literally a copy and paste so i come over here and go ahead and copy that right there ctrl c go into this gallery find its items i'll paste it so if i come back here and if i do a refresh they both line up say it's the exact same thing side by side now let me just go ahead and make sure that i've got all my data over here so this is for what we did for on select it goes up the next 15 this one goes up to the next 15 and 16. so if we go and start playing with it it should actually work so remember we are doing batches of 15 so this is from 1 to 15 which means when i click down this should be 16 1 6 and this should be 30. so let's see that and it works 16 30. next starts at 31 and goes all the way down so that number 15 which we made the decision on and the next for vnx 416 it's actually working really well but just to make sure let's do that go up there went back to the 1630 here went back over here one in the 15. now there's another crazy thing happens if i go over here on the top it goes blank and it still lets you do that so what i want to do is i want to actually go and change the visible function specifically for that add over here and i'll just put in something crazy i'll just come back over here to top arrow and on the top arrow i'll scroll all the way down to the visible and the visible i'm just going to say if var current is less than or equal to zero go ahead and print as a false otherwise f-a-l daniel i'm going to put that as a false true and so now it disappears so if i go and do another testing i'll just refresh that shows up now it's blank because we are you know it's less than uh or equal it's one and so like over here goes down to 16 there goes down to 30 and it works and it works so well see the scrolling functionality came up but this one is right there and it is frozen so we accomplished now the third one which is also where freeze one column the top row is already frozen freeze one column as well but the other one should be able to slide we just did that over here thanks to the combination of the galleries the filter functions and the mod functions wasn't that awesome we were able to successfully recreate the excel frozen panes effect in powerapps using a combination of galleries and some other formulas the important thing that you want to notice is in the first one where we were able to go ahead and create that frozen header or the top row and also add the horizontal scrolling everything else happens inside the container in the second scenario where we had the frozen horizontal one which was one column was frozen but the other one goes ahead and you can scroll back and forth the one which is the horizontal that's frozen that is outside the container the rest is inside the container so that was just the little change which is in the second scenario but overall it worked so hopefully this was helpful and as always keep power wrapping thank you so much for watching my youtube video remember this is all free with fresh content that is updated on a weekly basis so if you've already subscribed to my channel thank you and spread the word if you haven't already subscribe click on the bell notification and let the learning begin

Channel: Daniel Christian

Views: 1,303

Rating: 5 out of 5

Keywords: Excel like freeze panes effect in Power Apps, Daniel Christian MVP, powerapps excel, powerapps tutorial, how create editable excel like table in powerapps, powerapps excel like table, powerapps grid gallery, power apps mod function, powerapps layout container, powerapps container control, excel freeze row and column, excel freeze panes, shane young powerapps, reza dorrani powerapps, april dunnam, Power Apps Self, powerapps scrollable container, power apps, learn powerapps

Id: 8CBosd_PG74

Length: 47min 12sec (2832 seconds)

Published: Sun Jul 25 2021