Dataverse Driven Org Chart for Power Apps - Dataflows and Model-driven

let's build an orchard and power apps so today we're going to do is we're going to walk you through how to use dataverse to pull in your information from Azure ID into a dataverse table and then we're going to show you the power query that kind of goes to get that in there shape that all right but then once you've done that it turns out that model driven apps using hierarchy security have a pretty just add-in it works type of thing so we're going to show you that and then we'll quickly show you how that would also manifest itself over in a canvas app all right so this is all dataverse and instead of me teaching it well I was on vacation all last week right I sit on a beach drinking a little frozen lemonade drink very tasty and so I didn't have time to make the video so my good buddy Juan made the video so we're going to jump over to Juan showing us how to do that I am going to jump in a couple places and interject some pieces also remember the whole reason that I had one do this is because Juan's got an upcoming model driven class Juan teaches a live training class on model driven apps with dataverse where it goes into all this nerdy detail on how to do all this stuff and so since he's the instructor of that it means after this so let's just jump over to Juan's desktop and Taylor hello my name is Juan Grijalva so basically I put together a model driven app and I just threw the users table out of the box they use a table has a feel in which you can store a person's manager once you do that you'll notice that automatically you get this little hierarchy on the left side and I'll show you how this works but basically this allows you to view your org chart in um in a graphical manner you'll see that this displays the organizational chart of your company it is fully dynamic as you can go up up a level you can go sideways depending on how you know how big your company is so I can see that for example you know when you call this at the top and then you have other people that report down for example you can go down and then you can you know you can go down one level and you can keep going up so this is a fully Dynamic organizational chart that's being built just by leveraging a simple model driven up view so let me show you how this is put together the first thing that you must know is the out-of-the-box beta verse comes with a table called users inside this users table there is a column called manager you'll notice that this manager is actually a lookup table and it's a lookup table that references the same table so this is this creates a parent-child relationship right so this is a lookup table that's referencing the same user table right and this is the manager field once you do this if I go over to my relationships I'll notice that I probably have a one-to-many relationship to my user Stables I think on the related table and I can come in here and just search for user and if I come and look at the relationships there's probably one in here called manager what enables the hierarchy that you saw earlier is that once you set up the relationship you can set this relationship to be of the type hierarchical this is what allows you know the representation of the parent-child relationship to be displayed in that form that I'll show you guys you can use this not only with the user table but with other type of relationships that you can work with for example maybe a parent project to child projects in a custom table I'm not going to show you all those details but just by knowing this this type of information that once you create a relationship to a record within the same table and set the check mark to be hierarchical then that allows for the chart representational field so basically what you know what I did in here is I you know I built the solution and I called in my hierarchy I basically built a simple model driven app uh basically just through the user table inside of it and I'm leveraging the out of the box fields that come with the user you know the user form they use review so what you're seeing in here basically is let me go back to the user is the out of the box you know views from the user table and like I said if for example if I log in into any user you'll notice that there's a field in here called manager okay so if you were to update this with your manually you could do this manually with all the you know your company's uh pairing child relationship between managers then that will work but what I also included in the solution which you can download if you subscribe to the channel you subscribe to the training I'll let Shane handle the details but basically I have a solution and this solution I built a data flow and this data flow it's basically bringing in the information from your active directory so if you if you have in your active directory your manager and who they report to then you can build the data flow in order to integrate the data from active directory to the user table in dataverse out of the box that's not happening so out of the box uh active directory integrates a lot of the details like the users that belong to your organization the email and other details but the manager is not automatically being populated so you can build a simple data flow in order to bring that data in I'm gonna go in here and then I'm just going to go and show you guys what I did to build this data flow I'm not going to go step by step into all the details on how to build this data flow but basically the first thing that I did is I obviously bring in all my users from my drive API so basically all I'm doing is hitting the API and I'm getting all the users by default the grab API can only bring up to 999 users so if you work for an organization that has more than 999 users you actually have to find a way to to look you know a token in order to get more than 999 records in my case I have less employees than this so I'm okay with living it in the top 999. you can try you can try out these calls by going to you know the graph Explorer right and they you know that's how I figure this out once I I bring in the information from my users into this data flow basically that's you know I'm gonna step through through the steps so basically I connect to the graph API I'm basically making this call you will have to authenticate the first time and this basically will bring in all the information you know that's how that's coming from the grab API we hit the users Next Step that I did in this data flow is I remove all the columns and the only column that I'm really concerned with with all my users is the email column because the email column is actually a natural primary key as everyone has a unique email address and the next thing that I did in this data flow is I rename the column I just call it you know the user email and then I change the data type to be a type text data types are very important the next thing is I need to be able now to bring in the managers for all my users by also making another API call to the graph API so there's another graph API call in here which you can get the manager of the person right and instead of me passing in the me what I can do in here is I could probably you know type in here my email let me.com have to put the users so basically this is how you know I will be able you know by making this graph API call you know graph microsoft.com forward slash users and then I through the email of the person and then manager this will actually return the managers of that person by leveraging the gravel API so now that I have this information now that you know I said oh and what I can do now is I create a power query online where power query function in order to Loop through all my users and bring in their manager so basically if I go back to here that's what I did I went into this data flow and I actually built the same thing that I'm doing right I built a function within power query and all I'm passing right here is the user email right the same thing that you graph micro users and then this is a variable and then I'm getting the manager so I built this function within power query and then if I go back to the steps that I where I was so I'm here I have all my emails then I'm invoking that custom function in the next step and I'm basically passing all the emails from uh from from the list that I got this will return the manager of all these people basically the next step is I just expanded the column and the next thing that I that I got was the assure active directory ID right so this is the the ID they do it every time you create someone on your active directory you get to go it so that's the next piece of information that I extracted from that from that query that's all I care basically the next thing as I I remove the nulls nulls means that that person doesn't have a manager in active directory so I don't care about updating those in dataverse the last thing that I did was I basically you know turned the data type of this to be the type text once I had you know the user email and then I had the active uh up you know I have the ID of the Azure active directory of the manager with those two informations I can update my user table by leveraging the data flow so I can go and click next obviously I'm not loading the function the next thing is I am I'm basically updating the system user table I set up and I mean I'm gonna go after this but I basically set up a natural key in the user table by you know by using the email address and I'll show you guys how to do this in a second but basically I'm passing two pieces of information this will allow dataflow to do an up an absurd so basically it will it will find an email in the user tables it will find director and it will update the managers table by leveraging this natural key so if I go and I look for the internal email right here you'll notice that I'm just passing these the user email so this is how he finds the record and the next thing that I'm passing over is to the parent system user ID it expects the Azure active directory object ID which I got from the API and this is updating that manager column okay and then I go ahead and I can publish this data flow so this will basically go out there to active directory and it will update your table within the user stable with the information from active directory I talked about the primary key so what you basically have to do is you have to go to tables you have to find your user table or system user right here I went into keys and I created my an additional key right and if you could call this whatever you want in my case I call the natural key primary email and I basically said the internal email or you know they call this primary email or internal email and I said that to be a you know a natural key natural key means that it has to be unique per record you cannot have two people in that table that will have the same email we know that's not the case because everyone has a unique email quick little note here so when I was importing with one solution I was having a hard time with this key it kept failing for me and when I went and looked at my data it turned out that I had two people the same primary email address they were external people so we'd added the same external person twice two different ways so they were in there and so that was causing this to fail so if you have failures when you go to add this key it most likely is duplicate primary emails so I went deleted one bad user and it all worked for me anyway back to one with all these pieces of information I was able to take the data from active directory update my users table in data reverse in order to have you know updated information of managers and I was able to quickly put together a model driven app in which I'm leveraging the out-of-the-box users table everything out of the box and then I'm able to visualize my hierarchy by using the hierarchical relationship within a model driven app all right watching this part I was a little confused one oversimplified how simple it was I see under simplified I don't know but literally all I did was added the users table to his model driven app so it's one of the tables added to the app and then all the things you saw here worked like he didn't do anything special to make any of this work there was no steps to knowledge of an app just add the user table now that this is configured model driven app just automatically sees it and he got this interface super cool you can also leverage this relationship in a canvas app I'm not gonna go into the details of how this was built another you know you can also leverage you know canvas app in order to visualize your organizational chart so you'll notice in here that I have this it works the same way I can click for example Amazon and I can see who reports to Alison I can keep going down one level I can go up one level I can keep going up one level so it is pretty Dynamic all right so I know once that he wasn't gonna explain doesn't mean I can't explain a bit but so what he did to make this work in a canvas app is in reality this is just three different galleries so you can see if I click here this is a gallery we're filtering with a user's primary email is the current logged in users email and so in this Gallery is the current user's manager why he did Mana we never understood but he did and then down here this one is just filtering the users where the primary email equals the login user so this is how Juan chose to you know kind of build this hierarchy chart it's really just three galleries and so I'm not going to explain how to build it all now right remember the whole vacation thing but what I decided was we'll make a version of this next week we'll build a canvas app I know hopefully make it look a little nicer and explain a bit more about the mechanics of this but it's really pretty cool right like it's just those and so you know if I click on Chewie all it's doing is changing those variables to be you know him and so then now you can see it's sorted out by Chewie if I then click on Shane right that's me if I click on my face it goes back here now you see Nicole we click on her face and then we can now see all the people report to her right because she's the actual CEO here um and then I think one of the things we'll do in next week's is we'll make it so that when there's no manager right so the system is her manager because she's the top of our food chain so we'll kind of hide that and I think I also want to figure out you know like so if we go into me again and then a chewy and then Ferguson so then down here you can see that Ferguson has no reports so that comes out pretty clean so anyway that that's the canvas app but it's really just about pulling in those dataverse tables and then filtering out so next week we'll build a org chart and canvas apps right using this data that one just got together for us but we'll build the mechanics okay all right back to him uh all the solution is the is available to you guys if you subscribe to the training you know La shame handle all those all of those details but you will be able to download the canvas app the uh data flow you notice in order for you guys to implement this in your organization all right that's a wrap enjoyed this right like one was talking about model driven class right you spend a week a week about dataverse and model driven apps apps data flows all those craziness and like infinite more details live with one it's a great way to learn I know I've sat through it twice now I keep learning new stuff every time also if you just want to download the solution here you can do that right that's just the YouTube library right so there's a link somewhere up there down there somewhere I don't know go sign up for the YouTube Library training.powerapps911.com that's the way that stuff and if you have ideas and suggestions on what I should put in an org chart app that I'm going to build next week I'm going to start on that like soon so get those in there all right and with that I'm gonna say thanks have a great day

Channel: Shane Young

Views: 11,632

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, powerapps org chart, powerapps organization chart, power apps organisation chart, model-driven powerapps, model-driven apps, model-driven powerapps examples, model-driven app in powerapps, model-driven apps power apps, dataverse data flow, dataverse hierarchy, dataverse user table, dataverse keys, model-driven org chart, powerapps model driven apps, power platform model driven apps, model driven powerapps tutorial

Id: f89lAx8E0jc

Length: 15min 22sec (922 seconds)

Published: Tue Aug 01 2023