Microsoft 365 Community SharePoint, 2 Sep 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: SharePoint Approval Workflow Status Tracking with Column Formatting

Description

In this 19-minute developer/citizen developer-focused demo, Reza Dorrani makes easy the creation of a dynamic multi-step expense approval workflow in SharePoint leveraging a Power Automate flow. Very deliberate step-by-step explanation of list creation and of flow/process. Create 2 SharePoint lists – Expense Types and Expense Reports (with column and view formatting + list form configuration), Add the approval process flow (a sample) to Power Automate, done! Clear visual status in UI to track output and status of workflows. This PnP Community demo is taken from the General Microsoft 365 Development Special Interest Group (SIG) - Bi-weekly sync call recorded on September 2, 2021.

Demo Presenter: Reza Dorrani (Catapult) | @rezadorrani

Supporting materials:
• Repo – rdorrani/SharePoint | https://github.com/rdorrani/SharePoint
• Playlist - SharePoint List Formatting | https://bit.ly/spformatting

Learn more:
• Learn more about the Microsoft 365 Patterns and Practices community at: http://aka.ms/m365pnp
• Visit the Microsoft 365 Patterns and Practices community YouTube channel: https://aka.ms/m365pnp-videos

A

So the topic at hand today is sharepoint approval, workflow, with status tracking a little bit about me. My name is reza durrani, I'm a microsoft, business applications, mvp and a microsoft certified trainer. I work at catapult systems, along with my friend, david warner. I work as a power platform practice technical lead, that's the link to my youtube channel and that's my twitter handle, so the topic for today is purely approval, workflows in sharepoint and how we can track the output and the status of those workflows.

A

So my scenario here is expense reporting, in which I have a simple sharepoint list, wherein users can add their expense details once the expense information is recorded by the users, I'm leveraging power automate to add an approval process to the incoming expense requests.

A

These approvals are dynamic in nature. They are serial approvals and using the power of column and view formatting. I was able to track the entire approval process from start to finish who the approvers are where we are in the approval status?

A

How long did the approvers take to respond and so and so forth the key components of this solution? The first one, of course, is sharepoint, I'm leveraging a standard sharepoint list and I've applied some column and view formatting to enhance the experience.

A

I've also leveraged a little bit of the sharepoint form configuration features to create an experience around the sharepoint form, wherein we have sections a header section right here, as we can see and finally power automate. That is backing up the entire approval process.

A

So, let's get straight into the demo here I have a sharepoint list of expense reports and I'll first head over to the list view. So we can look at the columns of this list standard details being tracked.

A

The title of the expense start date end date, the amount, a description, a choice, column that tracks the status of the approval, a multi-line text, column that tracks the entire history of the approval, and then I have something known as expense type, which is the type of the expense, and this is a lookup column, to a separate list, of course, which is a list of expenses.

A

I have my types defined and, more importantly, for each of these types I have my approvers defined of this definition of my approvers. Here is dynamic in nature, so if the user adds an expense request related to training. In that scenario, my approvals would be james, followed by reza, followed by sarah.

A

So right here I can define the order in which my approvers need to approve this incoming request.

A

If I had additional expense types, I can simply add them in here and if I need to add additional approvers or maneuver the approval order around. I can simply just go to my sharepoint list edit this and make a change to my approvers person type column.

A

So that's the setup related to the sharepoint list. Now, on top of this, I needed an approval process, so I've leveraged power automate- and this is my power- automate approval workflow this workflow triggers or kicks off the moment. The user creates an item in my expense reports list try and zoom in here.

A

Once I have the context of the item right here on the trigger action. My next step is to go and grab the approvers related to the expense that the user has created and to grab that information.

A

I'm using a get items, query against the expense types list, and here I have an odata query that goes and grabs the approver information from my expense types list based on the expense type the user has selected for the request that they've put in once. I get those details, I add them to a variable of type array and that's the list of my approvals.

A

That's coming based upon the expense type selected once I have that my first step is to go and update the status of the current item and the way I do that is, I just change the choice column approval status to pending because the approval process will be starting now and in my approval history column, which is of type multi-line of text. I just go ahead here and enter simple text that says that this expense request approval process has been started by the person who created this request, and they started this at today's date and time.

A

So I'm just maintaining a history of the entire process of approval by updating the values inside my multi-line text column.

A

I also have an approvers column of type person, so I'm plugging in who the approvers are, as the expense request comes in, based upon the expense type.

A

Next I loop through the array of the approvers that is obtained dynamically and for each of those approvers that I obtained, I started the approval process now I begin the approval process only if the status is not equal to rejected, because my process is a serial approval, a multi-step approval. If any approver rejects it, the entire process stands rejected if the first approver approves it, it moves to the second and the third and so and so forth, depending upon how many approvers were defined for that specific expense type.

A

So as long as the status is not equal to rejected, we will begin an approval process and the approval process simply goes ahead and creates an approval task for the current approver. That's running in this for loop of my array of approvers.

A

Once the task is assigned to the approver, the flow will wait for their response. Once the approver responds. I am once again tracking the details of the approval response and in this case, I'm tracking a lot of information about the approvers decision.

A

Their decision who the approver was at what date and time did they respond? How long did they take to respond and any comments that they plugged in while they were responding to the approval process once this is done? I am loading it into my array variable once again and finally, I check to see the outcome of the approval. What's the final decision, if the decision of that approver is approved.

A

In that case, I will go ahead and change the status of the item only if this is the last approver in the thread of approvers and how do I track if this is the last approver? I have an index variable that I maintain inside this for loop right here that I'm incrementing each time the loop runs. So I have the counter of the approver. That's currently running this action and at the same time I also know how many approvers I have, because I can check the length of my array variable.

A

So I check to see: is this the last approver? If, yes, then, the status will change to approved. If no, that means there are more approvers who need to approve this. I keep the status as pending in case the approver rejects it. I straight away, go and set the status as rejected, and then this variable that I'm maintaining in my flow, I change its status to rejected if this stands as rejected the next time the loop runs, because I have added this condition that checks to see the status is not equal to rejected.

A

It will never go into the is into the yes condition and the flow will not move ahead and not assign any further approval tasks. So that's the concept behind this approval process.

A

Now, let's go ahead and try and create an expense id so here in my list, reza is logged in and reza creates a new item, very simple standard sharepoint list with a little bit of form formatting here so I'll create an expense item.

A

Let's say I need some power, automated training, or maybe I took some power automate training for 600 or 500 training for flows, and this one I will relate it to training, and maybe I took this yesterday for a day I can even attach receipts basically attachments to prove my expense details and once I'm done with this I'll click save now the moment I do this. The first step here is that the information gets logged in sharepoint.

A

Next, the status is set to new by default. I'm setting that column to new and because I just created the item, power automate now will trigger and begin the approval process, because the trigger for my flow was when a new item is created in the sharepoint list.

A

Once the process begins, we will have the status change from new to pending and because I want to show two cases I'll go and create another item right here once again, so I'll go and say new, and this time I will say I need some- let's try a different expense type I'll pick marketing. So maybe I have a event promotion. Maybe we are being involved in an event. We need to promote something so thousand dollars. Let's put this as test and I'll just put the start, end date and click save.

A

So now I have created two expense requests in my list in sharepoint now, once the float triggers, we will have the status move ahead from new to pending. This can take up to a minute or so until the floor triggers.

A

So while the flow is triggering, let's try and look at some of the column formatting samples that I have put in place. The first key column, formatting sample for me- was this little timeline that you see right here and what this does is.

A

If I select this, it gives me the details of the approval process so, for example, the customer lunch meet this request that james put in as the creator for a hundred dollars was approved, and this was approved by sarah and reza, and these are the full details of that approval history when it started who started it date and time and how long did they take to respond along with their commands?

A

Now all of this, if I head over to the list view, this is my column formatting sample that I have leveraged right here. So if I go to approval history, column, settings and format this column, this is the json code that I will just go and plug in in vs code, and the key thing here is one I am no json expert. So how did I get this sample?

A

Well, I looked at the pnp column, formatting samples and one of the samples that's been put out by tetsuya kuahara and I'm a big fan of his work. He has had put out this nice looking timeline sample and what I did is literally. I just took this and transformed it from a vertical looking timeline to a horizontal, looking timeline, and when you select this, it opens this custom hover card in there. I just show my description column, my history column, that's all I did and the way I did.

A

That is simply when the user selects this custom hover card right here and we can see the code in action here. I all I do is just go ahead and plug in the approval history and regarding the position of the and regarding the position of this shape. If you notice the shape moves from left to right, depending upon the current status, the way I handle that is by using the justify content attribute.

A

If I zoom in here, we can see that an action right here if the status is pending. I position it in the center. If it's new, I position it on the left. If it's approved or rejected, I just move that shape onto the right hand, side and the color of the shape also is dependent upon the status. So that's why, when you see here, depending upon the status, the color of this circular shape changes as well, and when the user selects this, it opens up the entire history.

A

So now, if I go back to my tiles view here here, I've leveraged view formatting, in which I show that same column, formatting experience in here I'm tracking my approval process. So these are the two requests I put in for the training request. I have three approvers for the marketing request. I have two and these are being dynamically picked from this specific list.

A

So right now the task is assigned to james and you can see that with this little status indicator that I'm showcasing right here. So as a user, I know who my approvers are, and I also know who the current task is assigned to by just looking at this visual.

A

So right now the task is assigned to james for my power automate training.

A

If I sign in as james and head over to the approval, email james can see all the details of the approval, along with the link to the item and james, can approve a request or reject the request. So let's say james approves this and says: okay, so james has taken his approval decision once james's approval decision is logged.

A

The flow now will move ahead to the second approver, because james has approved it and while the flow moves ahead, the flow is smart enough to know that the status is still pending, because reza or james is not the last approver. There are more approvers on the thread and if you observed it, it moved ahead. Live it converted the james james's approval to green, because james took his decision, and now it's jumped ahead to reza, and if I click on this approval timeline, I can see the entire process right here.

A

Reza started the approval james took the approval decision. James took three minutes to respond. That was pretty quick, and this is the comments that james put in now. The task is assigned to reza. So now, if I sign in as reza raza should get the approval task created, and in this case let's say reza, goes ahead and says: no, I'm not going to prove this reza rejects this outright.

A

Now, since reza has rejected this, the status now changes to rejected and when it changes to rejected no other approval task would be assigned. That means the process will stop at this point and we will see the status now turn red goes to rejected and this light will turn red and no task will be created for sarah.

A

So let me try and refresh this and there we can see that live here in action and then for event. Promotion. It's basically the same experience. I have two more minutes or three more minutes. So let me just make this quick. Let me show you the formatting uh experience for this. The key to show the status here was two things one. If I head over to my list view again, I have a column called approvers where and I track who my approvers are. Of course I also have something known as approver index.

A

This tells me where we are in the approval process, so if index is one that means it's, the task is what the first approval, if it's two it's the second approver and so and so forth. So the index lets me know where we are in the approval process, and I already have my list of approvals with this information.

A

All I needed to do was just apply some very simple column, formatting and once again all I did is I stole a sample from pnp. I believe this was from chris kent wherein he shows how to put a uh like an apply to each loop and basically a for loop to append your multi-person type column.

A

So I just leveraged that formatting sample and here I'll just go and copy this paste this in visual studio code, and let's look at the main crux of this, so this was the pnp sample that chris chris put out and in there you know he looks through the person type column and he just places the uh images of the person one next to each other.

A

So I thought about reusing that concept and what I did is I actually reapplied to that concept, but the only thing different I did is I placed certain emojis, which basically tell me the status of the approval. So, instead of putting images I used emojis and that emoji is the status and to drive the status here, we try once again and zoom in to drive the status here. All I did was this: if my status is approved, that means every approver has definitely approved it.

A

So for each person in that approval thread, put the green icon if the status is pending and the current item index is less than the approver index, which is the special index column that I'm maintaining, which tells me where we are in the approval process.

A

If the current user's index is less than that, that means they've definitely approved it. So it's green else. It's yellow else. It's green else. It's red, that's the basic logic that I applied right here, so same concept as what chris put out, but I just changed it with a few emojis and just played around with status, and this is what I ended up with.

A

So you can build these experiences for your users inside sharepoint by applying column, formatting view formatting, and then, if you apply on top of that, the power of approvals leveraging power automate, you can create such amazing experiences I'll, just wrap it up quickly with a few learning resources. So you can get started with what I demoed today. uh I have a full playlist on on column, formatting approvals, sharepoint, of course, and power automate. So if you want to check that out, that's the link bit dot ly slash sp formatting.

A

uh I have my own github repo, wherein I maintain my list. Formatting samples, luis, was sweet enough to reach out to me, and let me know that I can apply in the pnp samples as well so I'll plan to do that as well.

A

Very soon, and finally, thank you for for listening and a big thank you to vesa for giving me the opportunity to be on this call today I have been a fan of vesa ever since the pnp initiative started, and uh I've always been on the other side of the call, and I'm extremely excited to be here today. So thank you.

B

Awesome thanks reza excellent stuff. There are warrior horses celebrating around the globe with all that list. Formatting goodness.