Creating an Employee Recognition App in Power Apps, Power Automate, Power BI, Teams, and SharePoint

hi my name is jeff rhodes and welcome to another video on office 365. and here you can see the book uh today and this example is not in the book but you'll see i actually copied some of the code straight from the book into implementing this so uh you can take a look at it it's available on amazon let's check it out today on memorial day and uh sheriff or the kindle version is doing pretty well number 16 so appreciate that from everybody so let's get started so what we're going to do today is let me just show you the app first normally i like to build these on the fly and i started on that in about 45 minutes and i was realizing it was going to be a very long video so um i'm just gonna to show you the highlights so the concept here is to try to build an employee recognition form and so let's run it it turns out since i'm the only authorized user in my environment even though i have some guest users it only works with like full users so i pick myself here you can see i'm updating my site i can say outstanding job on office 365 deployment that's an optional one is the kind of the concept maybe i'll make it bold just to show some html rich text and then the idea is that you could depict which of the values of the organization the individual uh did so i i picked some air force academy values in this case and so i can say okay we'll just say service before self and then this is kind of some of the fun stuff is do you want to tell the recipient who nominated them so it turns out that that i thought that was going to work really well it turns out to not really work well on the chat and uh and the post but it does work on the email so that one's kind of a work in progress but we'll uh i'll check it for now send via teams chat post to a channel and send via email and then there's a link to the channel so let's submit that so we can see what it looks like and you always see the little dots coming across okay the recognition submitted and what happened is oh you can probably i'll pull this over it's in my far right it said i got a flow there there we go so i got this this congratulations outstanding job there so it came a chat to me and i got back going back to the team i said okay i got some recognition outstanding job an office 365 deployment and then it says you've been recognized by jeffrey road so because i checked that box and so this part all worked fine let's try it again let's go in and uncheck that box so we'll i've got these in slightly weird order here so we'll just do it again and i'll do a slightly line and just say this one doesn't tell recipient oh by the way i didn't show my the email there so that was what the email notice it came from me to me and had the stuff so that was the last email okay so let's do this one we won't check that we'll still do the emails and we'll send it again get submitted i got another flow but let's go look at that at the the chat first oh and it's kind of funny i left something in there we'll find that in the power bi i was trying to hide what we'll show next that's why that we have that style it doesn't really need it so the the chat came fine that you know it doesn't say well actually it yeah i forget actually if this jeffrey rhodes is actually saying who it's from or not but in the post at least if i go over to the post when it comes through it puts this thing jeffrey rhodes used power automate to send this notification so that's kind of kills it there's some stuff online about how to get rid of that and that's what i was trying that span style and it didn't actually work so that's kind of a drag let's look at the email now this one's nice notice that the email now says it came from powerapps and powerautomate and uh and notice it doesn't go in the safe senders list so it could come as spam but it doesn't say who it's from and it gives them a form for for unsubscribing which is nice from an anti-spam thing but it doesn't actually say who sent that so on the email we're doing okay but the the post to the channel that one doesn't uh do it so we'd have to like do a setup disabling that but anyway let's get into the guts of that doing actually while i'm still on teams let me show you the visualization we're going to cover that as well so we come in and save that data to um a sharepoint list so that we can can track it which of our values that people are doing we can track the what's happened the previous seven days we'll uh update that a little bit so there's some interesting things going on there and the other idea on that and uh if we don't tell who it is if we had some sort of stalking behavior or something like that where somebody kept notifying somebody we'd have a record of who did what so we could say hey you know put a stop to it or whatever so we want to make it clear that it's not actually anonymous we're just not telling the user but we're storing it we'd make that clear so all right let's jump over to sharepoint so i'll go back to the browser so i like to put stuff in sharepoint you can see we've uh put those there notice these is outstanding these are the bottom two that i just did and what i did the office it turns out i don't have it it would be important to do the office and my real example we did it in this case the active directory is not all set up so we didn't have any data and then i just did a true false column for each of our values and as well as the different choices that we selected so that's fairly straightforward so let's go over to powerapps so there's some somewhat interesting things going on here so the first one is our our data sources so we're going to collect uh office 365 users that's going to be to look up all our users we're going to point to our recognition sharepoint one and then this is our powerapps let's i can actually look at that one okay but you can kind of kind of tell that's our powerapps uh deal there and by the way when we get to that it's sometimes a little hard to find that powerapps i'm saying even now i'm trouble and trouble finding it yeah so or the i said powerapps have meant power automate that's our power automate and that's how you get to it there so we'll get to that in a second so let's go back and look at all our things so the first one is is we've got a gallery which i've got hidden okay and so we're going to use that office 365 search the users and then this is our search box we're going to use it to look for whatever they put in and grab the top 50 the dot and notice if we come in here that's if we come in let's back that up a little bit whoops so that's a record and then we do dot value and that is a table so that's what we want to link to and then the other thing that's interesting there is well i it looks a little odd because it puts all the people whoever's at the front of the alphabet so i like to set the visible so i've got it is blank i've got a variable var recipient if that's blank if it's not blank then we want it to visible be true or if they've typed something in the search box okay so how do we set the the on visible when it's got a or i mean set that var recipient what's gotten on select so we just say okay on select that method or that event we set that bar recipient to whatever the selected thing of the user is so let's go look at that real quick so notice the minute i start typing in i've got my brother in there okay as i start typing it in it selects it so one other thing you've probably seen this in other videos i kind of find it's a little hard to tell which one's actually selected or which one's a choice i notice when i do select it i update that text which i'll show you in a second but i also like to come in here and select that one that field and i'll set the color or the fill and so if it's selected then it's yellow otherwise it's white and so that's kind of nice you can tell very visually which one it is so while we're at it let's go in and look at the text of that banner at the beginning so i use it somewhere if it's blank then i just say employee recognition if it's not blank they've selected it then i put the name so it's a nice indication of what's going on all right so these check box nothing big going on there we just put the names notice i put the text of the the values okay and then some more check boxes over here for the different choices i put a link in so that one's got a advanced so that's kind of a little interesting so we can launch say we say on select so we can do it here too and we can put this big launch so how do you get that well you just go if i go back to teams i can go to the channel and get link so it's just that link there that we put in so it's a nice little one they can say hey what channel is this going to go to and click it um so let's go look at the visible of this guy so i like to have a text a field like this that tells them what they need to put in so very similar to our visible on that gallery we're going to check that is blank and then we're going to say okay at least one of the values has to be checked and at least one of the transmission methods have to be checked so that they can't submit it with like in it not do anything so again if you watch this i'll select it one and then if i check one of those see how it goes away if i uncheck it it comes back all right last thing before we get into our submit code is it was a little bit challenging to to get the value text over to power automate and so what i did there was a an html field so i can go ahead and set it visible to true so you can see it but that's kind of hidden uh it is hidden so we because you don't really want to see it but we use it programmatically so if we look at our html text pull this down if you're into html it's an unordered list so you put the ul and then the slash ul at the at the back and then we basically say if the first one's checked then we add a list item with whatever the text was if it's the second one and if it's the third one so that's pretty straightforward so let's let's run that and see so notice it goes away but if i check this one check that one and they're always in the right order too and then i just grab the html text we'll see we put that in a variable and send that off to power automate all right so let's close that so before we get into our submit code let's go over or actually let's do the first part of the submit and then when we get to the to the actual sending it to power automate i'll jump out let's do that all right so first thing we'll do is just set some variables so i'm going to do a variable called var body and i'm just going to say congratulations and this is all going to be html so i put some breaks in there you have exemplified the qualities of these values and then there's that html text i just talked to you about so that's that now we're going to keep going if if the details box and that was html as well we're just going to pin that if it's if it's empty we just do nothing but if it's not then we're going to pin that on there so again if i go back to our email again you can kind of see so this is the congratulations that's the html text of the bullets and then this is the details that i typed in that if it had been blank i would just skip it but if not i put it in there and then we say okay was going to check that box tell recipient who nominated if that's true then we say who recognized them okay so that's one thing we do the other thing we do is we're going to take a variable of var submitter and we're going to put the user's full name in there and we're actually going to use that in our logic inside of power automate just to do an if if that's blank we're going to try to skip who it is and if it's not blank we're going to use it so or we're going to tell them who's going on so that's what's going on there and then so we either set it to the user's full name or we set it to blank all right next thing we do is we do an if and then we're going to check that error label so rather than doing all that logic is the var recipient blank and the one of the values checked and all that we just said hey if the error label is visible we just return false and don't do anything else otherwise we're going to patch and i covered all this in another video so we won't spend a whole lot of time on it but we patched the name of our list then we call the defaults which puts any default values in there and then we just set up each of our columns now the hard part on this is is this recipient so let's go back to sharepoint for just a minute kind of glossed right over that oh i guess it's in this to go back to sharepoint this recipient if i come in here and look at the list settings this recipient is type person or group okay so that's why that's difficult and that's why also why i can only put in my registered user because guest users don't really come in as a as a person there and so i have to do all this oh data type and all that and i use that bar recipient and if you have a book you can go right to that let me just bring it over because that's literally what i did i have this is the the book and i did the same thing in my health ticket example and scrolls onto the next one there's that whole thing oh data type and out of there you put the department so that's how you read from you know your power apps that office 365 users and throw it in as a as a person column in this sharepoint that's what's going on that whole big thing and then the rest of this is pretty straightforward you just these are the names of the columns and this is like the recipient's department and then just checking those boxes in the html text so you save all that to to sharepoint so that's what actually adds each to the item then we're going to go in and call our power automate so let's leave that open we'll go into power automate because what you can do you can actually do all this with power apps but um i like the power out of it gives a little more power and also the had trouble getting the chat part it's a little more advanced on power automate than trying to do it all inside of powerapps so here's our our our flow here and and i'm going to edit it and just kind of show you the things to go through so what you first do is you say that the trigger and you can use the template is from powerapps and then we initially initiate all our variables okay so we literally and what i like to do is i put the name in and then i'll rename this and paste the name into the name so that i can see it better because otherwise just says initiate variable and plus it names our variables better that way because what you do here is you come over and then it gives you these names and see if you do that first it puts the name better so that you can tell what's what so that's and then when you see it from powerhouse it's a lot cleaner as well so definitely recommend that so literally we just put the stuff so i'm going to put the display name which is the person that were that we're recognizing their email that values html so those are all those bullets um but the whole thing you know with with the text and everything the submitter's name that's going to be blank by the way if we if we're trying to be anonymous and then do we post to the channel or do we send via chat do we send via email so we bring all those through notice these are boolean ones now my original example is on the government cloud by the way if you have trouble doing this you might have to do an if expression you know so if it's quote true then set it to true but i on the commercial cloud i didn't have any trouble i was able to bring it in as boolean and it recognizes buoyancy that's kind of nice so what i'm talking about is i use that variable post to channel is it equal to true had some trouble getting that to actually take so something to keep in mind so if it's yes then we do another one okay so this is where saying if that vars emitter is not equal to blank so if it has a value then we're going to just post it slightly different so i pick my team pick my channel and then i just put my value html in there notice i have a subject too so i say employee recognition for that person okay and then if it's no then same kind of thing i actually tried it turns out the the at symbol only you know where you tag them only works sometimes that's why i didn't put it over there so let's go back and look at the html so if i go back to my post so for whatever reason so this is the one where i send it as myself and for some reason right now it's not putting the act gives an error that's why i didn't put it in there so i put it in it says who i've been recognized and then this one comes through and says okay i send it as the flow as opposed to the user so it does now work for me to recognize it or that mark mention the person but then i got it put that in this power automate to send notifications so that's what's going on there so this one again i didn't talk this is a post a message and this is post a message it's flow by and that's why because i'm not putting the name of the user all right so the rest of these are fairly similar so i'll do the same for send to chat do is it true do i want to be the flowbot or not again i post this one i try two different ones one's as is number two one's the other one so again i come through i say post as the flow bot and chat with the flow bot and then this one basically does the same it's having trouble to get it to work if it says do it as the user so that's where it's coming through there's a by the way where i tried to change that to uh i had that extra html so i'll get rid of that because it didn't work anyway or i didn't actually need it there and then now are we going to send an email and the real only difference here is which control if i send via outlook then it just automatically sends for me okay but if i use this send mail that's where it becomes anonymous so it's basically a saying don't have any of the other choices so that's pretty much it let me save that since i edited the flow a little bit so let's go back already 23 minutes in but what happens is by the way there's a little trick i'll show you in a minute so when you're trying to set these up don't use your button like this it's already set up because it'll it'll override all your text so i'll show you that in a minute but let's go through it so literally i i would come in here if i just start typing it in notice that it gives me all those variables in the order so back when we talked about how to rename each step that helps because it comes back over here so we literally are passing in the display name the email address that bar body that bar submitter which could be blank and then the boolean values and then we call the notify that's where we say say it's successful and when we reset our thing so fairly cool oh yeah that's not what we want let's go here so here's what i was talking about if you were setting that power in the first place what you want to do is just add its own add your own button and then come up here and say bauer apps and then you want to say oh i've got some other ones to do yeah and see what it does is it puts the text in there in the button then you can copy that over but if you did that on my submit button it would have just blown away all my codes now i can't undo and everything but it's kind of a hassle when you got a bunch of code in there so uh instead now that you know what it looks like then you can just cut that like that go into the other button and then you delete this one come on there we go all right so i think that's pretty much it as far as the power apps and the power automate you saw those in action so let's end on the power bi so i'm going to go over to power bi desktop and that's what we saw earlier let's refresh so we should see some additional uh submissions coming in here there we go see we did a couple there and it's anywhere from zero to eight days on the most recent so the main challenge here is let's go over into transform data i'll just go back here so there's a couple things going on first is this details as html text in it so that's kind of hard to work with this recipient trying to figure out who it is it's a record so we gotta mess with that we basically go in and and uh oh i also expanded sorry first it's a list then we come in and expand the new rows which turns it into a record and then we expand the recipient and then i'll rename it as recipient i've got recipient email so that's how you get that okay somewhere when we go over to created by get the same thing so we'll expand it and put it rename it to submitter the next thing that we want to do is notice our data as we talked about it's just true false but to get that there we want to pivot so what you do or unpivots what you do is you select all the ones like that now we don't do these shared teams we don't really care but we're trying to get these values and you come over here and you say unpivot only selected columns so since i already did that what that does is give us a bunch of new rows but it says attribute and value and since that the value is what i wanted i actually want it to be like that so i rename this to the has value and then i this is the value itself but i don't really care when they're false i only care when they're true so that's where i did a custom column so i'll double click that oops oh actually you've got a couple i didn't do my exact order so let's talk about the days uh since submitted i do this in pretty much every power bi i do i like to to grab and figure out how old the data is so this whole thing date time from day and then parenthesis daytime.local now that gives you today's date and time right when you're running the report and then we subtract the created in there so that gives us the number of days and then i always change that to a whole number so we don't care about that okay so we've got that going let's go to the conditional column ah there we go so going back to the value and has value i'm going to make another column called exemplify value and basically say okay if the has value is true then i'm just going to put this value in there else i'm going to put null in there so let's go there oh actually i didn't i went too far there we go and so we'll notice when it's false it's just null and so we can then filter so we just come in here and say remove empty and that gets rid of all those columns so because again we're expanding if we look the best way to understand that because we'll see it later is if we look for the id i could actually find the id here there we go so we basically well we only had one row for that id but now we have two or three and like this one we did all three so we did four but we don't need the ones for the falses so we're just going to count the ones where it's true all right and then let's see what else did we do i would rename some columns oh and i did one more custom column which i've forgotten what i needed there oh yeah now this is pretty cool i haven't actually done this before so going back to this html you know what it's fairly ugly in there if you kind of look at let me roll over and see if we can see what yeah you can see it so it's got this div class and this you know paragraph tag and all that so for this purpose we just want to have the text and so this is kind of a nice little solution so you use this html.table function that's the name of our column so i renamed it from details to details html i'm going to name the column details and then i'm going to put a new element of that column so that's what we just want to name it and then this print or colon roots as do the entire value it don't start anywhere in particular so we'll notice what it looks like if i go all the way over it turns it into a table and then i can do my same old thing get the details out and expand it so that's what that does and notice i have just the text now it's not the most elaborate text in the world but it's kind of kind of nice it gets in whatever i typed in there all right so i can close all that so now we're just kind of doing our standard stuff the main thing to remember here is that since i did that unpivot i need to always be sure to do count distinct instead of just the count so if i come in here and select on on the number of submissions i just use that exemplified value and then i have the count distinct of the id and then i rename that to say number of submissions same thing for the recipient they're all me but in a real one that would be different people i always like these cards so that's again distinct count of the id it's going to be nice to have a slicer for our exemplified value we're going to have a slicer for our day since submitted and then i can't use a table because it won't look very good let's click on that but instead i can use a matrix so that's this guy here and then you put the items in so recipient and you wanna the things that that caused it to differ you know which caused multiple rows are the exemplified values so we want that to be last so i put recipient created details which will be blank in some cases and then the exemplify value and then you just keep hitting this expand button until it goes all the way down so that's why it's always me but it's got the date and then that's the details and then those are the value or values that the person did and the reason is because i selected that over there so there you go now i can see all of them and then the whole reason we did that on pivot is so we could build this bar chart here so we put the exemplified value along the axis and then the count is that again distinct count the number of submissions and so we can uh you know see how which relative value so both of these are showing similar things but it's kind of nice to be able to to do it that way so both of this and this kind of require that unpivot and then the only thing we duplicate that the only thing we do on the seven days is you put a filter it says that davison submitted is less than or equal to seven so that's why we knocked one we had one that was eight days old when i first built this so all right well lots of stuff there but hopefully there's some value for you guys and i'll look forward to talking to you on the next video

Channel: Jeff Rhodes

Views: 5,607

Keywords: Chats, Emails, Office 365, Pivot, Posts, Power Apps, Power Automate, Power BI, SharePoint, Teams

Id: SmKPEc0yZqU

Length: 33min 35sec (2015 seconds)

Published: Mon May 31 2021