SharePoint Approval Timeline | Power Automate Approvals + Column Formatting + Approval History

hello everyone reza here in today's video i will show you an integration between sharepoint and power automate approvals we will build an approval flow to track the entire approval process and in sharepoint we will leverage column formatting to enhance that approval process experience by showcasing an approval timeline which highlights the status of the approval along with the approval history how long the approvers took to respond to their respective approval actions and a lot more so let's check it out in action the use case here is a help desk application the data for which is stored in a sharepoint list here i have three tickets with different statuses and i have an approval timeline that shows where we are in the approval process plus if i was to click on it it will showcase the entire approval history from the person who started the approval to the approvers in order in which they have responded it also includes the time each of those approvers took to respond as well as their commands for the tickets that are in pending status we can look at who the current task is assigned to so right now the task is assigned to reza and james i have a link here that allows me to chat directly with the approvers so if i was to select this launch microsoft teams and deep link into a chat conversation with those users at the same time we can also look at how long the approval is spending since the task has been assigned to these users and this is a live timer right now it's showcasing 45 minutes and we can see how this lifetimer keeps moving ahead now all of this is possible through a combination of two things sharepoint column formatting and power automate approvals let's create a ticket and look at the process in action this will launch the sharepoint form in this case i have customized it using powerapps i'm going to put in the details of my ticket i'll select my ticket category as i t define my ticket priority and save now the moment i add my ticket the status of the ticket is new and the approval timeline showcases that if i select this there is no current history that is available once the approval process begins the approval timeline will automatically move from the new status to the pending status and we can see this in action live in sharepoint once the flow triggers so the ticket status is now impending the approval timeline highlights that if i was to select this it gives us the name of the person who started the approval process and the date and time at which the approval process was started the approvers here are being picked dynamically based upon the category of the ticket that the user selects and if we look at my departments list this is where the categories are being loaded from for category it my approvers are reza and james the approval process has just started so the timer here that is doing a live calculation of the time since the approval of spending showcases that information the reza gets an email from power automate in which reza can see all the details of the approval and reza can either approve or reject the request so in this case let's say i approve this request reza puts in the commands and submits the approval response james also receives the same approval request the approval pattern is everyone must approve so both reza and james have to say approved in order for the ticket to be approved let's say james says approved puts in commands submits now since both the approval decisions have been logged the flow would go ahead and update the status of the ticket and we can see that live in action here and if i was to select this we can see the complete history of this entire approval process in the timeline so let's try and build some of the key components of this solution from scratch so i'll create a list create a blank list i'll call this my ticket data list and click create i'll add a description column i'll add a choice column for the status of my ticket provide my statuses and click save add a multi line of text column i'll call this my approval timeline i also need a column to track the date at which the approval process has started so i'll add a column date and time and i will include the time aspect here and click save and i would also like to show who the current task is assigned to who are the approvers so i'll go to add a column in this case i'll keep it extremely simple i'll use the single line of text column and i'm calling this assigned to i'll click save for the status column when a new ticket gets created i want to set a default value here to new in power automate i'll select an automated cloud flow since i want the flow to trigger when an item is created directly inside my sharepoint list so that's my trigger i'll click create i'll pick my sharepoint site i'll pick my sharepoint list i would like to first update that item with specific details the id will come from my trigger action dynamic content so i'll select this any mandatory fields i would have to refill so for title i'll select the title from my trigger the status is what i would like to change now since the approval process will start i will change the status in this case to pending approval timeline i would like to first put in a message that says that the approval process has started now i'm a big fan of emojis so right here i'll press the windows key along with dot i'll select the rocket emoji and i can say that the approval has been started by now i need the name of the person who's created the ticket so i can get that directly from dynamic content if i search for created by i can pick the display name put the clock emoji the approval is started at i can use the created dynamic content column now this create a date will come back in utc format in power automate so let's try and add some expressions to enhance that experience so under expressions here i can use the convert from utc expression the first parameter is a timestamp so i can go to dynamic content and pick the created column the second parameter is the destination time zone so i've put in pacific standard time and the third parameter here you can add formatting to your dates so i've just put in the format and i'll click ok now in this case i'm going to keep the approval process extremely simple i'm just going to assign this to one user in my case my approval will always be reza i'm just going to hard code my name and approval pending since is the date and time at which the approval process will be created which is right after this action which is the current date and time at which this flow is running so once again i can go to expression and put the expression utc now next i'll add a new step search for approval and pick the start and wait for an approval action i only have one approver here so i'll pick first to respond my title would be the title of the ticket assigned to in this case i'm hardcoding this to reza i can put in details of the ticket here like title description and other details i can provide a link to the item all of these are dynamic properties next i'll add a condition to check the outcome of the approval there's a dynamic property called outcome if this is equal to approve in that case i would once again like to update the item now instead of adding that action again i'll just go right here and copy go to my clipboard and paste it so if it's approved in that case the status will change to approved the approval pending since this now needs to change to null because i want to clear that date time field and the assigned to i will set this as empty now for the approval timeline this is where i need to put in certain details about the approval so i'm going to remove this so here i can say approved by and pick the name of the approver so there's a dynamic content property called responses approver name so i'll select this now the moment i do that it will apply this for loop because responses is an array in my case i only have one approver so this loop is only going to run once when did this approval respond so if i search for respond one of the properties here is the response date so if i select this it will put the response date for the commands we have the responses commands and finally to check how long this approver took to respond this i can calculate based upon the request date that's the date at which the approval request was sent to the user and then the response date that includes the date at which the approver has responded to that request so all i need to do here is find out the difference between these two dates i wrote a blog post on the community forums around how we can get the difference between two dates by using a function called ticks so this expression you can grab it from the video description so i'll just paste the expression right here and click ok because we are trying to create an approval timeline here i also have to put in the original approval commands that we put in place when we updated the item for the first time i'm just going to add a demarcation and right here from dynamic content i'll search for approval timeline that's the name of my column and from the update item action because that's where i updated the timeline with that information i will pick the dynamic content next i want to repeat this for the no branch so i will copy this paste it change the status here to rejected give my flow a name and save the flow so let's create a record creating my request i'll click save the status here is new the moment the flow triggers the first step is we go ahead and we update the item and we can see that in action right here the status is spending i have my approval timeline and i have the approval pending since which right now just shows the date at which the approval process actually started along with the person to whom the task is assigned to now in order for me to show the timeline experience something like this here i've gone ahead and used column formatting and i've picked up most of these samples from the pnp column formatting samples i'll paste the link in the description of this video i'll also paste a link to my github repo which includes all of these column formatting samples that i'm showcasing here so in this case i'll just go and copy this i'll go and format this column go to advanced mode remove all the code here so i've just pasted my code clicked preview and i have my approval timeline and action here let's save this this time it will load up this dialog experience that will show the data within it now to calculate the time once again i'll go and grab my column formatting and paste it right here click save and i can see that it's been three minutes since this approval task has been assigned to reza let's say reza approves this says ok and submit once the flow records that response we can see the status changing to approved the timeline has moved ahead if i select this it will launch the approval timeline experience for me now coming back to the demo help desk solution let's look at some of the key aspects in here my flow triggers when a new item is created in my help desk list then i have certain variables that i'm creating here in order to store information of my approvers who are going to be loaded dynamically plus i have a variable to store the approval commands as an array next i go to that departments list and query that list to get the data based upon the category selected for the ticket in action and based on that response i go and grab all the information for my approvers that's how i get the dynamic approvers i've done a video on this the link is in this video's description then comes my main update item step wherein i just go and update certain properties associated with my item and here is that approval commands next i go ahead and create an approval in this scenario i'm using everyone must approve and here i'm assigning it to my list of approvers coming dynamically i'm using create an approval and i'm using wait for an approval that means i'm starting the approval and then i'm waiting for the response later and in the middle here i'm taking some additional steps i'm also posting that approval action to my approvers as an adaptive card in teams so they can not only respond to the approval action in the email or in the flow approval dashboard they can also go to teams and take a response now once we get the response now comes the key aspect of looping through each of the responses and then comes the key part of me getting all the response data i also had the original commands here in that array variable i'm using the approval commands dynamic property from my update item action and once this array variable has all the approval commands loaded inside it next step for me was just to go ahead and join them all and create a single string that i can use to update my approval timeline and here i've used a data operation action in power automate called join i'm providing the array variable of commands and i'm joining it with the string then i check the final response of my approval in this case i'm checking to see if the outcome does not contain reject that means definitely everyone have approved it i'm going ahead and changing the status of my ticket to completed the approval start time column i'm setting it to null the approval commands is coming from the output of my join approval commands action and in case of rejection same steps the ticket status though in this case is rejected for the timeline the position of the circle i'm defining it here using the justify content property if the status is pending then i position this at the center if it's new then it's positioned at the left else it's positioned at the end that is when it is approved or rejected and for the color property i'm checking the status of my ticket and based on the status i'm setting the color when i select this circle it launches the approval history for me and that is done through a property called custom card props and right here posted the data of my approval commands column to calculate the time a little bit of mathematical calculations gets the current date and time using the now function and then subtracting it from that date time column which i'm storing the date and time at which the approval started and that's the life timer that we see here if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 11,285

Rating: 5 out of 5

Keywords: sharepoint approval workflow, sharepoint approval workflow multiple approvers, sharepoint approval workflow power automate, sharepoint approval history, flow approval history, flow approval timeline, power atuomate, sharepoint lists, SharePoint, SharePoint column formatting, sharepoint list formatting json, sharepoint list column formatting, reza dorrani, flow dynamic approvers, flow multiple approvers, power automate history retention, power automate history, column formatting

Id: lYE7NXTDPJY

Length: 17min 35sec (1055 seconds)

Published: Tue Aug 17 2021

Reddit Comments

[u/Golden_Cheese_750]

Great. Sure gonna use that in my next approval flow.

[u/kyleb350]

Great idea. Showing users history without needing to go into the flow log!