Power Automate: HTML Tables [Power Platform Series - Ep. 8]

[Music] hello folks my name is matt peterson i'm a trainer at pragmatic works where we do everything from on-demand learning hackathons virtual mentoring um and you saw the intro video you know what we do and one of the other things we do here is we have a youtube channel where we like to post videos for for situations that can hopefully help you learn uh throughout the different products that microsoft has and my series this year has been all over the power platform and i'm on episode 8 right now so if you enjoy what you watch and you want to work from the beginning to the end where i've talked about some power apps some power bi and now i'm in my power automate section so what i want to bring to you in this video is the ability to send out a nicely formatted table of data on a routine basis so within power automate we can put flows on schedules and send out dynamic content but what if you want a whole bunch of records at once and you want it to be nicely formatted well this is where the creating of an html table can come into play i want to show you how easy it is done and how also we can do a little bit more advanced features with it as well so without any further ado let's take a look at how this is going to be accomplished so what i have currently is i have a sharepoint site that has different travel requests and so currently i have three different travel requests in here going to different places different start dates and different end dates and what my end goal here is is i want to at the end of every week send an email out to the person who's in charge of travel to let them know of all the travel requests that have come in and i'm going to show you how we can make this a little bit more dynamic and only give them exactly what they want so in order to get this done what i'm going to do is head on over on that power automate i'm in my matt peterson's environment this is a community plan environment here i'm going to hit create and i'm going to go with the flow that's going to be on a schedule so i'm going to click on scheduled cloudflow i'm going to give this something weekly travel request update something like that we can call it whatever we want here's where i put in my start time in my in what i wanted to start i can choose when i wanted to repeat so i'm going to choose here i want this to repeat every week and i want it to be just given out on fridays i'm going to keep it at 10 a.m at this point i'm now going to hit create so this is my trigger my trigger is the the date that we want this to happen the times that we want it to happen and if i go into the trigger we can do even more options with that retrigger here if i go into advanced options you can see that there are a lot of things to change here i'm going to keep it basic for right now because i really want to focus on this html html table creation so once we have this here i'm going to go into a new step and what i want to do for sharepoint is called get items i want to get all the items from a sharepoint list now do know what i'm showing here with the sharepoint data source this can be also used with dataverse table this can be used with sql server tables so this is not just limited to sharepoint only so once i have get items here i'm then going to go to my site address so i'm going to click it here this is going to be my app site and my listening i'm going to go into that travel request and so at this point one other thing to know is i can come up here and hit rename give this a little bit more of an explicit idea so get items i'm returning travel requests records all right so i like to do this here to keep track of everything that's going on and now i'm going to come to a new step so this first step is going to get all of those records for me now i want to make an html table of all of those rows that were returned from the sharepoint list so how we get this accomplished is in the choose an operation it is called create html table so create html table which is right down here so when i select it it says all right in order to do create html table you need to pass me an array well when we do a get rose or a get items function that is going to give me an array that i can now use so once i have this here i'm going to say from go over to my dynamic content and i want to get that list of items right here so value list of items once i put this in technically that's all i need to do and it's going to make an html table for me of all of my columns but if i want to be a little bit more precision with this and only return certain columns we definitely can do that as well and how i do that is i come over here to my advanced options so in the advanced options right here we're going to come over from custom from automatic we're going to change it into custom and now for the header i just put in my header names here so i'm gonna do something like uh i want the trip so it's gonna be the name of the column and then i return what do i want in this column so for here i'm gonna put in my title column so as i'm going over to dynamic content i'm able to pull any content from my steps above so i'm going to pull in the title i'll pull in a few more here i'll pull in the description of this event and so over here this is i believe i don't think it's called description in here this is going to be called my reason for travel that is the name of that column in this sharepoint list oh i need to come over and put in the correct value over here so now i need reason for travel and then i'm also let's say i just want to put in the airfare so we'll go with airfare at this point i'll hard code the money sign in come over to airfare and estimated airfare and we are good so now this is going to create the html table now what i want to do is i want to use this and send it out as an email so the next step i'm going to add here is i'm going to come to new step i'm going to come on over down here i'm going to go send in email and we're going to send an email v2 with office 365. i'm going to send it to myself today all right so we're going to send this to matt peterson the subject i'm just going to say something like the travel requests and then now for the body here's what's so cool with this html table i'm going to use the output from my create html table step once i put it in i'm going to hit save you're seeing a little warning here we'll talk about that warning in just a few seconds it's not an error it's just a warning and it's saying well let's talk about it now it's saying hey you're going to return travel request records you might want to use a filter to improve the performance of your flow what that's really telling me is right now i'm going to get all of the records from this list so they're saying hey this is a really long list this could take a while for the flow to run we'll talk about how we can do a filter on this in just a few moments so i've saved it i'm going to test it can do a test manual here i'm going to test in just a second i should get an email and after i hit the run flow and i'll bring that email right on over to show you what this looks like and here is the email right as you can see notice what it's doing here's the trip here's the description here's the airfare pretty cool because we get all the records we return the columns that we wanted and it's a nicely formatted table here so that's the basic part now i'm going to show you some more the advanced features of what we can do with this html table so i'm going to get rid of this email and we're going to come back in here and we're going to do some editing of this flow so i'm going to get rid of here and i'm going to edit and what i want to do is in between before i do the send an email step i'm going to put in a compose action and this is going to show me how i can do some formatting of this table i don't have to keep that default html table they came up with so i'm going to go to compose and in the inputs here i'm going to paste in a nice css style sheet and i will put this in the the description of the video i will put this in so you can just copy and paste this out if you want to use uh this style sheet and then if you know anything about your css styling you can come in here and start to modify these properties for your table to look specific the way that you want it so your company colors the font style the sizes all of that good stuff if you have any of that background knowledge you can definitely tinker around with this and see how it will work but let's see what mine currently gives me so once again i'm going to save my flow here but that's not going to be enough so all i've done is i've saved the styling of the html table but now i need to use this part of the html table so i'm going to head on down to my send an email action and in front important piece here we need to be in front of the output from the create html table we're going to add the compose and again i would have renamed this to like styling of the table but we kept it basic i just kept it as compose i'll hit output here i will save and then in a second i'm going to test this one more time with that last trigger and we will now see what the new email looks like so we're going to go with the recently used trigger i'll hit test and in just a second i will bring this new email up we see that it's now running it's going to create the html table it's going to make the styling and then it's going to send the email and in just a second here when i can pull this up here we go take a look it looks a little bit better i like the styling that we have here i've got some a blue background color here i put a gray color to the the table itself and that's why i said you can take that css styling uh script there and start to modify to your liking now cool right but what if you don't want to send all of the records back what if you only want to send maybe you want to see all the travel um start dates that are greater than or equal to today so you don't want to know about all the old travel requests you only want to see travel requests that have a start trip date that is greater than or equal to today's date well let me show you how we can do that with an odata filter so i'm going to get rid of this email here again and what i'm going to come in here to do is in my get items so i'm going to go back into edit mode in my get items under advanced options i'm going to filter this query down so to filter this query i need to put in the exact name of the my column which in this case is called travel start date now i will tell you there are experimental features that you can turn on in power automate so if you go to your settings icon you can turn on your experimental features which makes your own data filters a little bit easier to do however they are in preview they're experimental so sometimes they're not the sometimes they look good when you're using them you come back to the flow and all of a sudden it looks like all of your stuff is no longer there so i'm not using the experimental features too much but i do want to let you know it is another way you could do this and you get drop down capability rather than having to write out the code but let me show you how to write the code because you know experimental features sometimes they turn them off they go away so i want to make sure you know the official way to get this done so what i'm going to do here is i'm going to say the travel start date ge ge stands for greater than or equal to and i want it to be greater than or equal to today well i can come over and write an expression to return you to utc now once i have utc now in here i hit ok however i'm going to show you this is about to fail so i'm going to save this and i'm going to test it and it's going to fail here and this is a common troubleshoot that when i was first learning power automate i made this mistake quite often so i want you to learn from my mistakes so i'm going to test out over here and in a second oh it fails right away and my failure here is due to the fact that my utc now it needs to be returned as a string uh it's a date time string here and so what i have to do in order to fix this if i go back to edit i have to wrap utc now in single tick marks now if this was an integer value that i was doing this over the the tick marks aren't there but for your string values you have to put the tick marks in so now things should work so if i come over i'll hit save i will now hit test and i'll do my failure test here we'll rerun this flow is running it's doing the get items oh yeah and over here in just a second my email should pop up and here it comes right on over and now notice i only have two results being returned initially when we were first doing this we had three results because i had one of my travel start dates a few weeks prior now it's only getting any travel start dates that are from today or after and so you can filter on any of the columns that you want to but where you can run into a problem is if here's the thing if we do a filter and let's say we did greater than or equal to today and all of my travel requests had prior start dates and there were no requests that have a start date that's greater than or equal today well the flow is still going to run it's still going to go to the create html table step it's still going to compose the styling and it's still going to send me an email and the email is going to have an html table located within side of it however there's going to be no data you're just going to see columns of headers but no rows returned so you might say well look i don't want to get an email if there is no records that are actually satisfying this filter query how in the world can we get that done well i'm glad you asked because i'm going to show you how we can get that accomplished so what i'm going to do here is i'm going to come back out of here and what we want to do is we want to do some kind of a check to see do we even have any records and how i'm going to get that accomplished is by using a variable so watch how this is done so after my get items let me go back into edit and i'm going to come up here in between the create html table and the compose i'm going to do a new action which is called initializing a variable so initialize if i can spell today initialize variable so i'm going to click on initialize variable i'm going to give it a name something like var count records and i'm going to do int to let myself know that i'm using an integer so my type is i'm going to use an integer now for the value what i need to do is figure out what the length is of the list of items that is returned and then i'm going to use that number to do a conditional check so to get the length of this uh array that's returned i'm going to come over here to expression and then right here i'm going to type in length so just like so so i'm going to do length and then open parentheses close parentheses and then i'm going to go to my dynamic content and i'm going to say find the length of my list of items so find the length of my list of items here so now actually it's going to be the value list of items my apologies so it's going to be the value that's the array that's returned so then i'm going to hit ok it's now in here perfect now what i need to do is put some kind of a conditional check now i want to see what is the length of that list of items returned so i'm going to come up i'm going to add in a condition a condition control and once i select condition control here i'm going to say take a look at my variable and see if it is greater than the number zero so if it's greater than the number zero i want the email to send off to me if not just stop don't do anything else i don't want to see this as this empty email being returned so once i have this in greater than zero i'm now going to take all of my actions and put them into the yes function the yes branch over here but what i want to do is i want to show you another thing if you want to do grouping of your actions we have another action that's called a scope this is also used for error handling and which i'll do a few videos on that coming up in the next few months but i'm going to do scope here and now i'm going to drag all of these actions into the scope so they're all organized as one kind of action here so now i'm going to collapse this over here so we can kind of see what our full flow is looking like so what we're doing is we're going to run this at a certain time get all of the items we're going to initialize a variable where we're going to count the length of those items returned if that length is greater than 0 we're going to run down the yes branch if it's not then it's going to go to the node branch which there is nothing there so nothing is going to get executed so at this point i'm going to hit save and then we're going to test this again and i should get all of my emails so i'll hit test i'll hit here not all just my two records that are returned for being greater than or equal today and in a second it's done the test we're going to get an email here but if we want to take a look when we do initialize variable notice the value is two because i have two items that are greater than or equal to day so we go to the condition this is now going to evaluate to true and now it goes down the yes branch and as you can see i now get that exact same email that i had just a few moments ago now if i were to go in here and i will do this just to prove to you let me come in here and pick a travel start date i'm going to change this one here so let me edit this record and i'm going to do a travel start date that is on april 6th i'll save it and then over here i'm going to change this travel start date as well and i'm going to change this over to april 3rd and then i'm going to come up and then i'm going to actually go yep i'm going to change that to over to april 3rd and come on over so 5 days to oh this did not edit out right it did not allow me to save let me do that again so april 3rd april yep they did say travel start dates are all old so now when i come back to run i'm going to come over here i'm going to come back up to the top i'll go back to edit go back to test and i'm going to test this out manually i'll test it i'm going to run the flow i'm going to hit done and in a second when we go to initialize variable look what's being returned a value of 0. when we go to the condition notice an email is no longer sent out to me so i'm not going to get just a generic random email that has nothing in it so hopefully this gives you some ideas of how you can send these automated records to you you can start to filter it down have a beautiful html table sound the way that you like uh and again hopefully you enjoyed make sure you like subscribe to the channel i have a lot more power automate videos coming out power apps power bi again i'm all doing this all over the power platform uh for this series in 22 i like to call the series we've got this because as you start to learn these things man we've got this you can do this so again hopefully i'll see you in my next video [Music]

Channel: Pragmatic Works

Views: 14,309

Keywords: power automate html table formatting, power automate html table style, style html table power automate, power automate html table, create html table using power automate, power automate how to, microsoft flow email, power automate emails, microsoft power automate, html email, power automate expressions, triggers in power automate, dynamic content in power automate, microsoft power automate demo, Send Email Notification with Power Automate, Pragmatic Works, Matt Peterson

Id: m0XLY8wqJas

Length: 19min 9sec (1149 seconds)

Published: Tue May 17 2022