Complex Queries in Power Apps

where we're going to be building out an application today with power apps in SQL Server that's right guys we're going back to the onboarding application which has been the largest application we've built yet on this channel so we're using SQL Server we're using uh SQL Server reviews we are making a multi- tenant multi-organization onboarding application let me make sure all my uh other browser windows are closed there there we go and um let's see let me move this over so you guys can see my ER diagram here in SQL Server management studio so we got quite a few tables going on here don't we Elvis so uh we are so I'm setting this up to be uh so people can utilize this program and perhaps sell it as a service so I'm keeping track of organizations and tenants environments applications users all that good stuff so uh I see you guys uh here in the live chat yeah thanks for thanks for joining guys um so we will just move on from here I'm going to bring up power apps and um let's do a little bit of recap of of where we've been so far so there is a a playlist on my channel you can go to so if you're on the channel just go to playlists and what you'll find is a playlist called the onboarding application okay so if you missed any of those if you wanted to go back and see how we got here you can always look at those but we're learning key things in each session as we go along we're building out this application okay so looking at our ERD diagram again the most encompassing uh table we have here is organization okay so we have organizations okay so here we we have Koso LTD which is the fictitious company that uh Microsoft uh uses as an example for a lot of their training material here's Kurt LLC there's n LLC and here's a new org name and we can make uh something like this active or inactive so hello Vincent and Patty Nick and Rick thanks for joining us this morning so uh we can manage our organizations here okay then we've got tenants and you notice that I can filter based on an organization okay or I can not select anything that's going to show me everything okay so now we can manage tenants let's go into environments look at this okay we could click on a tenant let's say let's go with NIS let's see what we have so we have Darren's environment that we've uh marked as inactive and then we have uh let's go here to power laabs us there's Kurt's uh test sandbox Okay so we've got those screens done now if we go down to Applications we really haven't done much with this this was a listing we utilized a view where it it joined in all the data the organizations the tenants environments and uh the applications so this is something that we will be building on to so I'm going to go back to the environments screen and what I'm going to do here is I'm going to uh duplicate this screen okay welcome back yeah all right so I'm duplicating this uh manage environment screen and what we're going to do here is we're going to make this into the manage applications screen okay so this SQL Server data that we hear is going to span all these things right so I'm going to move to different screen comeback I'm going to grab the screen name to this and then actually go into this screen and instead of going off to Dev user management I'm going to paste in this screen name there and now we'll see that we'll actually go to this new screen that we duplicated and I love to duplicate screens as long as the previous screen was set up just right um I love to duplicate that's sort how you can save yourself a lot of time as you're building applications okay so um here within this gallery for the dev manage apps uh I'm going to go in here and change some stuff around here so instead of environments we're going to look at applications application and we are going to make sure this is um if we wanted a filter based on something what is set up here the tenants okay we want to filter this we could go by environments we say environment environment ID matches up with that drop down there selected ID or if that's blank so let's see uh what we have here we do have I do notice we have um some airs here or sometimes we call them measles um and um so we've got the uh selected it looks like like we've got a form is that what we did there before yeah I think we had like a little delete popup that we had there so in this case I'm gonna make that application where is application here you know I could just Ty Here There It Is application okay and um let's see make sure this is all set up right so instead of looking at uh tenant here we're going to look up application now this drop down list we want this to be environments and this will be application name and we want to select just one environment in which this application lives and there is an appid what do we have here let's see what's going on with this one here okay so this drop down list should be looking at environments I'm going to look at name there okay we the ID we're going to look up environments Boom the environment with the ID equals environment ID here we go okay now from um the last time I believe this is two weeks ago when we were um creating this screen that we duplicated this from um something that Kurt suggest let's let's add a delete or remove here so if I click delete it's going to bring this this is actually a a forum view control okay and now that that's up not going to look at this and let's see what's going on here so this form which I really don't like forms all that much but I was looking for a quick and easy way just to display the information that they were about to delete so okay so this will be now the application okay that's good what is this guy here complaining about the field this all looks fine okay let's see what this button does down here application application whatever selected there and I'm still trying to figure this guy out here this eror appears most commonly when a formula refers to something that no longer exists so it's got a problem with this particular data card environment data card default now this might be the case where we are um that particular app let's go look at the data here yeah good morning data's King so let's go in here and look at this for the applications I'm going to right click um it's not going to allow me to do it from there let's go over here to the side and let's go look up application and we will bring up some rows here yeah we only have one and we have I'm expecting to see in yeah there's the environment ID there so instead of looking at that we this should be environment GD um app quid that's what it should be looking at click on that field name there w and then that should also beid okay there we go so I'm going to save I'm going to click cancel so now we can um based on an environment so this this app is in this environment so if I go to Kurt's test sandbox we have nothing there okay so we might want to add a few more apps here um let's add one and we'll call this uh kurts now Kurt isn't here to defend himself so let's let's create an app called Kurt's worst app and we'll put it in his sandbox is gonna freak out we'll get them back for not showing up this morning um okay so there's the environment now instead of actually saying environment GD I I want this to say appd that's what should say all right and what I'm also going to do is this is by far the worst app that hurt has ever built exclamation point so we don't have an appid yet because it's not actually created but um I'm going to hit save okay and now I'm going to go into um let's see here what what did I do did did that add it as an application no it added as environment so whenever you duplicate screen always got to remember there's all kinds of different places you need to um replace things right yeah this is good we got some good test test data in here um that we can uh later delete so we got environment so this should be application got that and uh [Music] there and let's see what it's complaining about here the patch has valid arguments now what's funny about variables in power apps is once you put something into a variable it doesn't like anything else being put in there so we might have to back out of the app and come back in um these control names should be fine and then over here we'll need this to be an application and this will be appid yeah quit now that should be good I'm going to hit save and then what I'm going to do is I'm going to click back once it's done saving and open it again guys are quiet this morning open it back up there we go all right so now I'm going to go into our developer screen click on Apple let's see the applications we have here um we have an environment called C Kurt's worst app so um so what we'll want to do is well in order to delete that we'll go into the environments and what we'll do here is we'll find this environment that's not really an environment it's an app so um I'm G to copy this I have to type it again I use notepad plus plus on a different screen to P things all right so I'm going toit delete and we'll see how this little delete uh popup works here okay delete so now I'm GNA go back go to Applications add new here's Kurt's worst app this by far the worst app Kurt has ever built save now I hit save and we have an air it's so good to uh enter some test records and test it out a little bit we still have some issues here tenant ID does not exist okay so what we need is an environment ID okay and that's good because I didn't want to put it in Dar environment I want Kurt's worst app to be in Kurt's test s box I want to hit save there we go we go so if I go look at his sandbox he's only got one app and if I change it over to my sandbox I've got the onboarding app okay so you guys have any questions about that we effectively did a screen in what 15 minutes that wasn't bad huh so let's go back to our uh ER diagram here so we now have screens that will manage all four of these things okay now if we scroll down here we've got users that can be assigned to each of these um so if you're a user and you've been given access to an organization um or let's say you don't want to associate them with an organization you just want them associated with an application and that's it so these four tables we would call probably mapping tables um whenever you have a many to many relationship up you need a a mapping table in the middle okay so for example as a user I could be a part of multiple organizations I could create different corporations uh maybe some f fictitious organizations so I could be a part of many organizations and then one organization could be associated with many users so that right there if you ask yourself those two questions you can identify uh your many to many logical relationship between those two so let's see here um okay so now I will look at how I want to manage users for for each of these four things now power apps just uh did something to me that I don't like it when it happens um I think a lot of people are experiencing this and it's sort of good that it happens so if it happens to you know that you're not the only one it is happening to it just you're your canvas it it w brings back a blank canvas now if I if I switch screens okay it looks like it it sort of worked there I go back in the developer screen okay this this is looking better a lot of times I have to close down my project come back in so maybe they're improving it it happens to me too yeah it does okay yeah I think it's a bug yeah it's it's it's pretty annoying huh um okay so what I'm gonna do let's go to [Music] um I think it's important uh to describe the significance of why this is important because if I was servicing an organization an organization purchased the software I would add their organization I would add the main user that could manage that organization they could add additional tenants they could add additional environment so on and so forth but they might have four tiers of security there might be just the the boss you know the owner of the business would have um you know control over the organization record but they but he might or she might add in um the IT director or somebody in it that would add the tenants you know so you can manage uh different things at different levels so just because you create a row here for a user organization and they're part of the organization um you know you may not want them to have access to to other things um so if you set things up in this way it allows your application to be very uh flexible and dynamic which is what I like in in my application so I click on users it goes to yeah the screen that we looked at the very beginning of the session here and um perhaps might what might be good is to um go back and look at one of these screens so I could do something like this I might just use the um yeah this app screen this one that we just did here and we could have users associated I might want to go from scratch yeah I think I'm going to use this one I'll duplicate this one I think this screen might be really different than the the uh previous four we were working on so make a copy there okay and I'll call this Dev manage users and what I'm going to do is I'm going to move this down with my other Dev screens there we go and I'm going to set this up to look at users and in this first field I will put in if it allows me to maybe okay I need to go find the label I'm working with here okay so this is the first label and I click on text and nothing comes up here now I know there was something in there because in the other data source we had something now if I try to type in here nothing happens is that funny so um sometimes it takes some let me go over here to this a red dot and try that one nope let me try this one over here nope uh it's a little frustrating so I think I might back out and come back in and it looks like we've loaded the app is loaded again here okay now I'm going to go back over to Dev click on well that button users won't take us to the right screen just yet so let me go get the name of that and then over here I'll make sure that button goes to the right okay good and let's see if we can actually yeah so do you see you see the difference sir so I tried to go into this air and every time I go up here it was blank and I couldn't I couldn't go in there so something weird was going on so let's see if we have like a full name actually we probably have okay we're working with SQL server and I think we might be using name yeah and then over here I might do something like um just for right now M365 email we'll do that okay something I'd like to do when you manage users I always like to see their photo there um I don't even know if I want to have this other column here we have their photo and their name um that might be the only two pieces of information that we want here so what I'm going to do is for the template size for this Gallery I'm going to make this more like 120 so we got a little bit of space for that um and I think we'll probably want to filter this a little bit um later on down the road so what I'm going to do is I'm going to insert a photo here on classic not photo image we want an image control very good and I'm GNA move this around I'm going to get rid of that email I'm going to get rid of this heading and up here I'm just going to say users okay so there's Kurt there's Darren okay this is looking good um we might already have some screen here that allows us to um yeah so here we've got Okay add new user okay we have this screen Dev you I do like this what we were doing here um so here's the organizations we can make somebody a part of and then we can select the tenants we can select the environments um so I like how I was thinking a few a few weeks back here um now this should probably be set up so it's only visible if we're using that toggle uh for debugging stuff so Global um show debugging there we go I do like this um let's go back over to this screen and let's look at what we have here so we're using that view that's got all the joins um but if there are no users you know if an organization doesn't have a tenant or a user an environment Associated I only want to see the users here so what I'm going to do is I'm going to filter out the ones that don't have a tenant um or where they're um their username is blank that's really what I want to cut out so we sort by columns there's that we we'll filter it here okay filter that okay so there's that and then we will say uh full name full name maybe it's called username okay so I'm going to I'm going to use that in my criteria here and we'll say not is blank okay now what I don't like about that is it's giving me uh delegation uh warnings okay um all right so I'm trying to filter out the ones that that um where we don't have that so we don't have any username okay so I want to build this in a way so that if we get up over 2,000 records we're not going to have any issues at all so I've already set this to 2000 which is the max and think what I might do we could actually filter on the organization tenant environment could actually do that here that would probably help us out quite wait a bit so in order to get some drop downs I always recommend going to the copy controls from here screen and grabbing our uh drop down lists and um s here now that's not the one I wanted to go to I wanted to go to Dev user management so at Alex says any side effects to a very large app uh with many data sources um I'd say um where you want to um be careful is when you have an application with a lot of screens and a lot of controls um and then also think about the assets that you have so if I go over to multimedia you know I've got some images a lot of these things are under one megabyte um you can have up to 200 megabytes of assets there so all these things play into the the side effects or making your application sluggish so I typically like to keep uh my application under 25 screens when it gets to be that size or over I'm thinking maybe I should have broken this out a little bit and uh the best time to identify that is when you're Gathering requirements you're planning you're analyzing before you jump into power apps and uh this might be the case where I have a whole separate you could you could break it up into separate apps and you have one app for a security role like somebody who's managing all of these organizations and tenants and everything yeah Al like says yeah the multimedia is dragging it yeah so there is one I actually did this to prove a point is you could upload an image here now this image on the home screen if you guys remember maybe it was the splash screen that we had yeah I got this down I downloaded this is a very large image um so it's always good to have your image as small as possible without uh sacrificing too much of the quality of the image so um before I'm done with this I need to take that image and you know upload a better sizing for this um absolutely all right so I'm going to go back to this screen here and what I want to see are the listing of my users but I liked having more um application admin that's not the one I wanted see inage applications this is it so this is the screen that I want them to go to now you noticed at the beginning of this or you know about 10 to 15 minutes ago I was starting to build a new one um this screen could be used more with the app admins because I wouldn't want app admins that maybe are managing just an environment or one tenant to be able to see everything uh organization wide so we'll probably use that for that and then over here for users instead of Dev manage users paste that one in G to click users okay and then here is where we can um drop in our drop downs that we'll use as a good filter for this screen so I'm going to grab this drop down list here and go into users paste that here okay make sure it doesn't go inside of your gallery and um I'm going to open up the debug controls here so you'll see I've got these little squares here that that help me sort of space things out and align things nicely now also what I'm going to do is I'm going to insert a text label okay make sure that's on the right typically have my labels above controls like like this uh to be 30 uh 30 in height and then for the color I will say GBL theme and tie in the primary dark okay now for this first one I'll put the left padding at zero and this first thing will be organization I'll select both of these make sure this is all lined up zoom out a little bit okay so for this we will have organization uh and it's pulling in an email the created by so I'm going to go find the name for that there we go now something that uh we'll want to do for these drop downs especially for filtering is we'll go find a property for a drop down and it's called allow empty selection allow empty selection we'll set that to be true like that Dev user management now I'm going to duplicate this before I make too many changes to it because I might want to use a version of this and the future for a different screen okay organization I'm going to give this a meaningful name here DRP for drop down list say oration filter okay I just gave it a different name and now it's blanked out for some reason okay there we go so I could select some something here very good and I'm going to make this a little smaller maybe 260 I'll grab both these controls and I think I might make have them uh have the same width I like that 260 there so select both of these here copy paste so we'll have um this one will be tenant this one will be arment and application right and we'll make sure all this is lined up here so the Y is 180 that's 180 okay this is all looking good and I like these four uh drop down L to go across um so I think I might add to the width of this to be something like 290 maybe 300 okay 300 this should be D good this should be environment should the application does seem like it's a little scrunched in there doesn't it there's all the tenants there's all the environments there's all the apps now we should have a cascading filtering going on shouldn't we cascading dropdowns so let's do that too uh before I do that I want to make sure the spacing is all right on this so I'll do a copy paste zoom [Music] in we have here the 300 okay so so instead of 300 let's do 295 zero that's supposed to be the width not the top padding okay I think this looks a lot better so now I can go in here okay okay that looks right on hit save save early save often all right so now I'm going to move this this up a little bit and the heading should go along with it very nice there that looks good now I think the padding is off here for some reason um so this should all be 10 p adding top okay all right so for the tenant we got the DRP work filter this should be DRP ten filter DRP and the DRP appliation fter right find items for this typically I just click on items over here on the right Pane and I'm GNA open this up and let's filter this out filter to look at only the tenants within the organizations that have been selected in the other one so we'll go look up the um org ID there it is equals DRP or filter do selected ID all right we probably only want to we probably want them sorted as well do we have these sorted let's make sure everything's sorted here sort on the name okay and the default is as sending sorting sort name environment so let's filter this out where the yeah the tenant ID equals DRP tenant filter. selected do ID okay we'll hit format text and also work in a sort here say name toddler is trying to get into my my studio okay we're sorting we're filtering we'll go over to this application one and make sure this is set up right F to this where theet I equals V RP tenant filter. selected ID now it doesn't seem to like that why doesn't it like that tenant ID isn't recognized okay well let's go take a look at that application needs to be environment ID Erp environment filter. selected ID okay sort by name that should be good all right so we'll run this you notice that nothing is going to be populated in these three dropdowns why because it's all dependent upon the first one we we picked the first one okay okay there's n LC oh there's power Labs okay and these are all the environments that are in there and here's Kurt's worst app there we go all right so now if I select n LLC so all these are set up to um allow blank selection right so if I select Nissa salc will make that blank okay so I'm wondering how I want to do this here um this is good station tenant environment application so what I'm going to do so I only want to see the users down here so what I'm G to do G paste that there I'm gonna modify quite a bit so I'm gonna comment out what we had before and what I want to do is user what's our are uh want to look at users and having a table name called users is a little scary for me because we have a function called users so anyhow we can get rid of that now what we could do is let's filter this based on it's in order to find find out who's part of what so we have these um mapping tables in the middle exactly do I want to do this so for a user we do have a tenant ID okay what I might do is question is do we have these form mapping tables in power apps that's a good question user organization we do very good I think whenever this thing changes what I'm going to do in [Music] the on change that changes I'm going to go get the data out of that mapping table okay so I'm going to do a clear collect and I'll it um Co which is what we prefix all of ours um Rick says sell Kurt's worst app for five bucks I think I might I'm going to go out to Fiverr and list this a $5 app and see people bu it and we'll give him a hard time every day that it doesn't sell how about that all right so we'll go in here and we'll say um user orer org and um us your organization okay but we're going to filter that based on the value that they selected in this drop down list so here I am going to filter the filter that and the criteria will be yeah the organization ID okay organization equals DRP organization filter. selected. ID okay and that should be good so I'm going to save that and so uh 808 OG just got here absolute Noob um yeah just to let you know this is more of this is going to be a large size app and it's got lots of complexity in here people like to really complex examples um I do have a lot of if if you actually join my free community I'll put the information up here at the Top If you are more on the beginner side check out the link at the very top of the screen tinyurl.com powerplatform Community or you can email me at support at nesus got lots of beginner level content so um if you're enjoying watching an application being built uh please uh feel free to to stay on otherwise I'd recommend starting with more of the beginner level content but uh otherwise I I appreciate you being here and my toddler might be breaking through these doors any moment so how should I handle this so what I do is I'm loading up this data okay and we're getting user IDs and we get their name okay but don't have their photo we don't have any of this other stuff okay now this might be a case where we could create a view that will give us exactly what we need um I think in this case I'm going to um still go get this this so this will give me a listing of users in the organization okay and um so something else that I'll do at the same time is I'll go get all the users for that organization okay so I'm going to say clear collect give me all the users let see filtered subset of the users and save filter and I'm going to go look at users now this is something that um will be interesting to see how this all works out here so I've got this collection here and it'll have a bunch of user IDs that are in that organization so I now I'm going to the users and I'm going to get just all the users for this organization so where their ID is in this Co user org dot user may now is that going to work well looks like we have a delegation issue here okay um there's a better way of doing this yeah well what we will do instead of doing something like that we will let's go back and look at our data here we'll have this loaded [Music] in we have these user IDs okay but this is a case where we might want to yeah instead of filtering which I'll put down here somewhere I'll do a for all which is how we Loop in power apps and what we will do yeah we'll do a forall based on that collection that we just got I'll use an alias as uh my which is my go-to Alias for one I'm using a for all yeah and I see um w w is it wand or Wanda wandi just found your channel thanks for the advice on joining the community I'm just starting out on my bars Journey well welcome yeah so within um you know this power apps Community uh well here on the YouTube channel a lot of times people ask for help and uh you know it's not really a the greatest place to share screenshots and can't really share a screenshot or uh show the air that you're working with so having a community that we can go to and post videos maybe a loom.com uh screen share um so you can share what's going on on your screen and actually post that there it's a little bit like a Facebook group but uh in my opinion like 10 times better you don't have advertisements there um you don't have all these other competing interests going on so yeah so please do uh join join us over in the free community yeah uh by the way if you guys join the free community um I am offering right now my uh power apps crash course uh for free for those that join that so all right so I'm going to Loop through what we got up here and then what i' like to do so I like to add to it inside the loop okay going to cut that I'll put that there so now I'll do this now right before the for all I want to do a clear on that collection because I definitely want to use that collection okay now it doesn't like it because it hasn't really been defined since I commented that out so here say collect so we're going to go through these and for every record in there we'll go do a lookup on the users table and we'll use ID to match up with my DOT user ID okay giving the lookup function just two parameters will give you the whole record okay if you wanted a single value out of that then you would give it a third parameter which would be the column name okay so then we'll have after doing this we'll have this users filtered okay so I'm going to use that here instead of the current data source okay right now there's probably nothing in there I'm going to save I'm going to run I'm going to select niss LLC now we have these labels we got a bunch of measles because these labels aren't looking at the right thing so I'm going to say dot full name do we have a full name for users what do we have uh probably just name that would include both okay we have any records in this thing we don't have any records in there okay so user organization so I'm going to right click on user organization and let's see if we have any records in there because we may not have any which would make sense why nothing's being returned okay so what I'm going to do is when I add a new user this is where those tables will be populated so we have organizations tenants we need one for environments as well and um let's see what record has been selected here move these around a little bit and I think what I'll do let's look at some of these things so this title doesn't have any default I'm going to put in a little test button that eventually we'll we'll uh rip out of here but I just want to have a test record in here so what I'm going to do is I go back over here and and go into users on the click event of this Gallery do we have anything that passes anything over and we do let's go no doesn't look like unselect no you don't in the add new user we have this Lo new record true let's make sure that's false on this button the fs and then we'll also do an update context well I should probably be doing it in the same call right Lo record I'll do a look up on the users table do we want go look okay my ID is seven okay so I'm gonna say ID equals s okay I'm going to run that and now what I should have for the name here the default should be L record Dot name probably have a separate place for first and last name last name okay something else like to do for the default actually you need to do not just like to do but you need to check to see if it is a new record say l new record if it is a new record then you probably want just an empty string otherwise then put in the value from the context variable there uh for last name I'm pretty much going to paste in what I have there and just change this to be last name so let's see what we have here um run this and let's click on nus and if I say is yeah I want my user account to be a developer and then I want to say NIS oh there we go look we're uh these things are being populated correctly or at least this first one the tenants so this should be populated from this so if I go into these data source I'm going say environments say and so we do have a label here for selected orgs this could be selected attendants s Kurt LLC I don't think that we have anything down here for LLC but if I go here okay so these environments should be filter the tenant ID is in the label selected orgs ID we need to make that this label okay and then the environments we should have the environment should have names okay so this filtering back these things here now do we have we don't have yeah we have two names so why isn't this being populated in that label because it should be in there yeah this item. name um have two records there that's really strange environments let's go take a look at environment here and we do have a name for both of those well maybe what I'll do is I'll do a Refresh on environment it should be bringing that um back to us there we go this cascading correctly here it's looking at the tenant or the org ID selected orgs you guys know noce these IDs changing when I click on a different one it should be should be changing let's go look at this selected or IDs okay and it's looking at the ones that had been selected okay so it doesn't matter if we click on a different one what we need to do is actually turn the toggle on that makes sense so if you wanted to look at all of them you could select you know okay I like how that works okay so whenever we save a user here this is what's going to tie it all together we're going to patch what we need to do is patch this middle table if I could bring up my uh my ERD diagram here need to patch this middle table okay I'm going to move this up here so I can see all the fields as I'm working with it and was going to be user oranization and um what we will check is that this is a new or existing so keep in mind we are adding a record adding a road to this table for each one of these toggles that are that are turned on that's what I'm trying to do this a little bigger so we yeah see there's four of them there total okay so what I'm going to do do a for all on each those items that's in this uh Gallery here go grab the name of that Gallery got all items then I also want to filter it okay and we're looking for the toggles so tog and I think it's that one let's just go click on it and make sure that is the exact name that is the toggle name my very good so this is what I'm thinking about guys we're going to go through here and Patch we're going to create a a record for each one of these but what if there's already a record there we need to check to see if it's already there or not so I want to D these guys [Music] over okay we need to go check so we will do a lookup okay we're going to do a lookup on user organization and we're going to go look for to see if this user ID is in there we have an loc record which should be a user here we go ID we're going to do a look up there and then we will say might want to do a with here or okay I think he's so now we have a good reference to this uh record if it does exist and then uh what we can do here is say if user org. ID I'll say is blank if it is blank then what we're going to do is say I think we should actually do that if right side here okay I'm trying to indent here we go so if it is blank what we're going to do is say defaults from the user organization otherwise what we'll use is the user org record the patch now we need to have a third parameter for the patch which are the values that we're going to update and we need to have um something in the name so I will take the um organization that we're working with which is in my Okay so we've got name my my DOT name that should be the organization name and then we'll just put a little Dash in here and we'll say also Lo record do n. ID uh let's do name okay so we have a good value in name we have a user ID well that's going to be in Lo record ID we have an organization ID that's going to be found in my do ID is active well we need to have a uh toggle for that which is okay is the user active or not right so we'll say this one will be tog user active and we'll say the default should be true for that instead of it saying let's go look at the true text true text should be is active I can spell it right is active and let's go to the false text is inactive okay there we go so now we'll just use that toggle to say whether it's active or not very good what else do we have last modified on okay well that would be now that's Modified by now we've been using let's pull this down here let's see what we're using for inside of [Music] users yeah we're using email addresses and that is great I like that last modified buy I'm going to say um user. email [Music] that should be good for now I am going to save this and hopefully it actually adds that uh to that middle table there okay let me go back to the ERD diagram and and uh so we come into the users that the the screen that we're currently working on and for every organization they tick off we're going to make sure there's a record here in this table for the user that has been selected for the screen so this project is is going to increase in complexity and it's a good thing that we're getting all this uh sort of laid down as a foundation um and and working right so we have less headaches later on so I'm going to get rid of that debugging thing there all right so I'm going to select all four organizations and I'm going to hit the save button let's see what happens yeah that doesn't look quite right to me what is going on what are these defaults uh set up for okay well goes and looks in there um okay that's interesting let's go see if there's data in that table that we just uh added the code for user organization okay so just added me for Koso and that's and that's it just the last one isn't that interesting um let's go look at our code and maybe try to figure out why that is is possible here okay so this first part right here what we're doing for all well look what the cat drug in well hello I I just finished up the whole app man did you really no that's good because I need an nap I need an appp I I think everybody's taking a nap on me I I'm uh good morning y'all thanks for uh joining us M Chandra yeah and um Elvis has been joining me I think he's just having some connectivity uh issues okay and um I do have something to show you Kurt so if we go over and we take a look at our organizations here we go we got all of our orgs we can go over and look at all of our tenants look at all these tenants that we have we have Kurt larson.com uh we got all these all these guys here right now I can go look at environments okay we have a Darren's environment I'm gonna make Darren's environment active again we go over to Kurt's sandbox okay Kurt has a Sandbox now let's go to Applications let's see here there was an app that was added here and it's in your environment it's called Uh Kurt Kurt's worst app this is by far the worst app that Kurt has ever built so these are the things that happens Kurt whenever you know you're not here to defend yourself okay I get some records that are um things for you I get to see what everybody really feels about me here then huh that's right so we got quite quite the um schema here Kurt don't we I mean look at this yeah we got all this going on and then we got all these mapping tables in the middle and they all map up to this user so a user could be set up to be you know they're going to manage all the organizations so they manage everything down or they might might just be associated with the tenant and and so this is all taken care of by these little mapping t tables in here and what I was trying out if I go back and go into users and um try to select myself uh now this thing isn't working right because we don't have any uh data in the mapping table so I go over here I've got this little button I'm going to rip out later on but I go load up my record Darren n and I wanted Darren to be in all of these organizations yeah and this button should be going around and for every organization I ticked off in those toggles to be true should be adding a record to each one and also I don't want to add them if they're already there so I will show you my code before I click on because there's a bug in there don't want to click it just yet of course so we've got a loop here and we filter all these organizations we just get the ones that have the toggles now in this case I've I've selected all of them I want to be a part of all these organizations yeah so it should go through here and I do a wi I go get um this may be where where where it's messing me up here should be the my right yeah I should be looking at and DN and where the or ID yeah the or ID equals my DOT there it is ID that's what I like that's what I was looking for right see what happens when you join me Kurt it's like yeah I have a brain cell you have a brain cell you got your stink I put my stink on it we make colog now if Rick was here per yeah be perfume you might actually be in the um yeah he he was the very first one that commented um is he even here today now um well not in the private chat I saw him in the the public chat okay so he's probably watching yep smells good smells [Laughter] good all right so this might work now now that that um you have me explain my code a little bit you know and uh okay so I go so with I I go get that and if it's blank I use a defaults for that mapping table and if it does exist then I I use that with there in fact I should probably indent this stuff over a little better what are you putting in that defaults after you create the blank record what I do is if you look here I I uh create a name I put the user ID in there I put the org ID pretty much all this stuff okay so you are populating it good yeah so let's try this out again I'm gonna run this I've selected all four I'm gonna click save What's it gonna do see now Darren Darren does I gotta explain something about Darren to you guys he does things a little bit different um I I and and and I'm not sure it's not a bad idea I think I think it's probably a pretty good idea I'm not this isn't really a dis on Darren at all but it's different most of the time I am I am inclined to go put some some I go into the tables by hand and I put some data in there to make sure that I've got stuff in there whenever I create um the program when I create the interface I'm gonna be able to pull up the data that's already in there he does do that nope he'll go through there and he'll do the whole program and then he makes the program put the record in there and that's his test I don't know which one's better um obviously mine's better his is his is GNA be better in the end however you better have it all lined out right first because otherwise you're gonna get it in there right you know so I I you know it's it's kind of interesting how you do that it's actually probably better in the end I think the way you do it yeah because you have to create the crud screen anyway right you have to create it so you might as well go ahead and do it and use that to test you know so yeah just it's almost exactly backwards compared to the way I do it yeah so it looks like it did work I got four there and now so when I go back here uh whenever they they change these little filter things up here I go grab some collections I'm going to format my text to make it look even prettier here and uh so I go get that makes it run better when it's formatted that's true makes think it runs a little bit faster because it can read it better not um so the users filtered based on that so I go get those go get those users and I put it in users filtered okay so if I use that collect down here which is what I'm doing here so I'm G to open this up they should be getting that so now I should have some data let me change this to Kurt LLC oh there he is so Darren should show up no matter what organization I pick just like a bad Penny Rick and he Rick isn't he just like a bad Penny just keeps on showing up doesn't he just oh all right so if I go in and I I try to bring Darren back up by clicking on this button here and let me say well Darren's not going to be a part of Koso anymore I'm gonna hit save hopefully it handles that okay so now I should have three rows but you know what Kurt I don't have anything that will delete it out if I oh man Kurt better go put a little Trash Can icon there there or what I should be doing is removing if I'll just say true here and then use this has to be my DOT right with um so what I'll do here is my DOT toggle if that's false or you You' probably be disappointed in me if I said equals false right if if that toggle isn't set up then I should probably go do I remove remove right on user organization oh my goodness I'll have to go do a lookup yeah on that now if you in a collection if you are in a collection I wouldn't want to do this in your in your uh in your persistent data but if you were in a colle I think it would be cool to go ahead and remove anything that you had in that collection pertaining to to darise and then go find the ones that have been toggled on it it would be probably could you just go ahead if you got four tenants it's gonna go in there and rip out the four tenants and and then you're gonna add the tenants in as you're as you're doing your for all here so I I like that you brought this up because the sort of piggybacks on something that we discussed earlier this week with the accelerator students where we talked about the power of Collections and just throwing a collection at a patch statement right yeah yeah so if you remove any rows out of a collection it has no ID to associate with the back end that's right so that little cool trick of like update doing Mass ads or updates in the collection and just throwing at a patch and this one call it does not work with deletes it does not keep track of things that were removed okay it's it's I I wish I wish it did keep track of that yeah because back in 2001 we had disconnected record sets with ado.net withn net and if we deleted stuff it would have an XML document behind of it that would say oh these things were deleted and it would delete them but uh we might maybe we'll get that type of functionality in here um at some point because collections are really powerful okay so I'm going to do a look up okay on that of the one that we need to get rid of and it's going to be something on lines of this so that will give us the record that we'll remove okay I got three and we actually have four in the database I'm hoping that how we have things set up the new code it'll actually move that one give it a moment to run it has all four selected now again isn't that interesting I'm going to rerun this and see if we have three or four I do have four is it is it tied is it tied to the uh is the toggle's default tied to it you mean in this this code here yeah no no in the the toggles if F did it set them all the true when you said all items so what it should be doing is does account row filters user organization and it looks organization ID user equals that so as that changes it should take care of that now I wonder if I had done a um user organization let's let's do a user or refresh here here there's actually still four of them here and they were last modified on it should have deleted the one I and why I was suspicious was that it got turned on first and then you're testing to see if it's not toggled on and that's when you're deleting it so if it if they all got turned on it's not going to delete anything yeah so yeah turn it back on so explain to me what so say that again okay so so I'm thinking that the defaults is looking at that table yeah and it is right it's looking at that table when you do that all items when you sit in that four all items you set uh the filters true for all the items so so go back to your filter real quick here okay so you're saying filter all items true that means you're collect you're bringing them all in oh you know what I have an if here yeah and then I remove it what I need to do is have an else block and all this would be the well the else block right I think so I think you're right I like you as my code okay yeah I'm having a hard time keeping up with chat gbt as a co-pilot but you know or is it having a hard time keeping up with you I don't yay cologne colog cologne mic drop oh my goodness so here we are an hour and 40 minutes into trying to make all this let me see all of this sort of work together in an application the more relationships you have and hierarchical data it can get a little what I what I've heard you uh call HT when we we were writing apps back in 19998 1999 sometimes as the app grows or the complexity grows you're like woo trying to wrap your mind around something maybe that you looked at a few weeks back like oh this is hairy this is you know it's not really clean cut I mean right ERD diagram sort of helps us but it's sort of hard to keep all of these relationships and and things in your mind yeah when you're doing a mapping table it becomes that's that's one more degree of difficulty right it's easy when you got a one to many but then when you got a one to a many over here and a many over here and you're connecting it into this map now you got and and for me that becomes a lot harder yeah you know that's that's when sometimes you need a another another person to sort of look at it and and give their thoughts and um yeah I guess that's maybe the whole idea behind extreme programming yeah where you got two two programmers sharing a keyboard and a mouse I think that would drive me nuts but well the thing of it is is you operate real fast you're you're you're very you're very quick you're like an F-16 pilot out there on these things man you know so so think I'm really slow though too so right but but the thing is is so people trying to keep up with you that's kind of hard but once you see it you you've got it now understand like you just said two people at looking at something makes it a lot easier but it's really cool that you're going through this because most people if they're watching they're realizing that that's their life their their life is most of the time 10 o'clock at night wanting to go to bed but I got this one more bug in here and I can't figure it out but I can't go to bed until I figure it out and you're by yourself you don't have that other pair of eyes looking at it so yeah um you know it's it's really good that you show it like this because this world is not pristine it you know it doesn't work exactly the way you see it in the videos that's true that's very true unless you're watch on our videos and then you're gonna see so if you take a look at all these uh apps that we have here something that um that Rick suggested that we do is the the that um that's Curts for St we're gonna sell that on Fiverr for five bucks that was his idea yeah was it was Rick's it was Rick's idea started my freelance career is right here huh all right so let's sort of let's try to pull all this together um so we'll go over to now our users and then based on these things here so the one organization I'm not in is I think it might be Koso LLC or LTD then Rick wants a 25% commission he just never stops does he he never stops so something I noticed is when I came in here of course I got some measles going on right got the measles um this is going to be listing of users so H I think what I'm going to do is make this maybe 120 I'm going to add in uh an image control mean bada [Music] boom why don't we have an image because we're not in there we go there's Darren okay is that dar it's like Mel Gibson Mel Gibson um should have pulled in this photo um I guess I just pull this over here for now okay and I think I'm GNA comment these out for now um not sure exactly how I want to work all that morning Travis yeah good morning Travis throw a sh blue scale po all right so I think what was important to me so we're looking at three different tables we got organization I I pick an organization like give me all the users that are attached to that one if I go to Koso LTD I'm out so what did happen here I want to make sure that I don't have any uh measles here say ID we should be good for that when we first loaded up I wanted [Music] to change maybe if we did that in the unvisible sometimes the timing and the firing of the events don't really match up very well but we'll try it okay now I'm going to go home and then go back the developer screen go into users okay that looks right and I'm a part of these others okay very good and if I select that what what I want to have happen if I don't have any organization selected I want all the users presented so we have call users filtered and and um so in the change event of this guy I would say um if self. selected text. value is blank then just give me everybody all the users then I'll ENT these over and then finish off the if there okay select something select nus select again unselect all that oh look there's Kurt now I got two darrens now you know what I should have done the story of two Daren the implication of me implications of me it's probably still out there somewhere run around so I'm doing a collect okay so what we need to be doing is is do a clear here and now we don't need it here hey y yay you did it I didn't know if I was going to accomplish anything in two hours today to be honest this was here I haven't I haven't looked at this in in like two weeks or three weeks or so I don't yeah then but this is this is real development work this is what it's like it's not all uh no um you know and Nick says very good yeah I was sitting here uh thinking which isn't uh the greatest thing for YouTube retention me just sitting here like how am I GNA do this um so now like Kurt you said there's two types of programmers there's a a hippie programmer and a nerd programmer is that right that's right that's the way I've got yeah and I feel like I've just won I've just I've just completed the screen because I figured out the first drop down now the screen is not done I got a filter based on tenant environment and application if they select anything there right but the fact that I got the first one done I feel like okay the rest of it's easy the rest of it's downhill is that how you feel too is is that the hippie programmer or is that the nerd programmer no that's that's the nerd programmer that's that's the nerd because the the hippie programmer he's they're done at the thought of this program as soon as I figur oh I'm gonna do this program here okay let's go to the next one oh so hippie programmers are like Architects then yeah because once they create a diagram and and say this is how it should be built they'll just walk off to the next project right I tell you though after after working with a couple Architects that's a that's a nerd programmer of a different sort really I mean that's that's a whole another animal there you know yeah i' agree it sure is but um yeah you know you just you get it in your head that's the hippie programmer I got this thing mapped out in my head I know what I want to do and everything okay I'm done now I'll I'll hand that off to somebody that wants with the green the green visor and the pocket protector and they got little Jewel spittles on the corners of their mouth and they're just over here just eating eating uh what do you call those um hot and drinking Mountain Dew that's the nerd programmers I've been both I've been both very good very good all right keeping an eye on the comments and um let's see what do we yeah it's been quiet out there since I got came in I haven't seen very many just a few yeah it's been a quiet day um even though I know you guys are out there watching um yeah so I do have something to share with you guys check this as as a little uh short clip of what we do in the accelerator program you guys might find this very interesting power apps has been the key ingredient for the hundreds of people that we've helped dominate in the organization with this one skill you're able to simplify processes for countless tasks like tracking leads doing inspections logging attendance booking desks in a shared office generating reports tracking employee performance and the list goes on if you have a project that you need to get done or you're an IT professional or a developer or if you plan to event do some freelancing work with Microsoft power apps I am literally the only person you need to accomplish your goals spreadsheets and all the old ways of doing things are becoming redundant and the people who are able to build power apps are going to accelerate the professional career so much faster than everyone else who's okay being average I have over 20 years experience as a software developer and have been working in power apps for over 3 years we have lots of course material for you but if you're eager to accelerate your expertise with power apps at a much faster pace and want to work directly with me this is what me and my team are going to do for you number one we're going to figure out exactly what projects and skills you're working on and the overall end goal number two we're going to get clear on exactly what data sources are needed for your projects if it's a particular project with a deadline we're going to work with you to make sure that you meet that deadline and number four we're going to work with you to make sure that you master the platform so not only can you finish any projects that you're currently working on with the projects that come up in the future and you might want to take those skills and become a freelancer in the future if that sounds like something that you're interested in and you want to expedite the process of being able to build apps that can literally be used for years and you want to be an important asset to your organization in any future companies you work for click the button below and we can have a 15-minute chat about what you're needing and how we can help very cool very cool I do see uh some messages in the chat uh fa hopefully I'm pronouncing that right says hello I need help power app so I'd recommend if that that you check out our our free community it's at the top of the screen there go there I do have a whole crash course so if you're the if you're a beginner that crash course is really good I wish I had that crash course when I was first learning power apps taught me a lot of things that um that it quite frankly took me months to to learn um that will really help you out in your power apps journey and uh now if you're uh still looking for help after that you might want to take a look at the accelerator program that we uh do offer and um so I I guess the question now is what video should you watch next some reason YouTube thinks you're going to like this video next let's see if they're right or you can select this playlist which I've selected for you based on the content you're currently watching guys got to hurry click one of them otherwise YouTube's going to autoplay some other video

Channel: PowerApps Tutorial

Views: 1,524

Keywords:

Id: G4sJc3xZ_M8

Length: 114min 12sec (6852 seconds)

Published: Sun Nov 05 2023