SOLVED

Automate reporting of text data from SharePoint list

Copper Contributor

Hello there,

I'm trying to automate the collection of text (rather than numerical) data from a column on a SharePoint list built to track migration of records from one place to another.

The column is called 'status' and contains several different options, including 'in progress', 'complete' and 'returned for rework'. Each status is selected by whomever processes the record based on whether the migration has been successful or not.

I would like to automate a weekly report to show the percentage of the total for each percentage, e.g.

-In Progress: 25%

-Complete: 50%

-Returned for rework: 25%

SP isn't part of my regular job role, so just finding my way with all this.

I managed to automate a flow to populate a list from an Excel doc, but capturing that list info (well, one column in particular) and converting each written status into a percentage on a report is proving a challenge!

Thanks for any advice!

Regards,

Ali

11 Replies
best response confirmed by al_the_ginger_leader (Copper Contributor)
Solution

@al_the_ginger_leader yes this is do-able with a flow that has quite a lot of actions but isn't particularly complicated, so I hope the details below will be clear for you.

 

In this example my SharePoint list has a choice column for the status and uses the 4 statuses you had in your post:

0-SP-List.png

I've assumed you will want to send a report out once a week, so I've added a recurrence trigger and set it to run at 9am every Friday: 

 

1-Flow.png

 

Next, we need to add 5 initialize variable actions, one to hold the number for each status and the fifth to hold the total number of items. Give each one a name (I always start mine with var), set the type to float and optionally you can set the initial value to 0. 

 

2-Flow.png

 

Next add a SharePoint get items action. As we only want to count those items where there is a status - and to overcome the annoying warning about adding a filter query when you save the flow - add a filter query of Status ne '[and expression of null}'. ne stands for not equal to. Don't forget the apostrophes. If your list has more than 100 items go to the settings for the get items action, switch the pagination toggle to on and set a number that is higher than the number of items. The reason for this is that the get items action only brings back a maximum of 100 items by default.

 

3-Flow.png

 

 

Next add an apply to each control. If you forget to do this the flow will add it automatically as it's needed as we are looping through the items in the list and looking at the status of each one. Select value from the dynamic content box.

 

Next, inside the appply to each add an increment variable action. Select the variable for the total items and set the value to 1:

4-Flow.png

 

Still inside the apply to each we want the flow to look at what the status is so add a switch control which is a type of condition. As the column in the list is a choice column type you need to select status value from the dynamic content, not Status. The switch looks at the status column  column in the SharePoint list and you have a "case" channel for each of the possible answers, i.e case status value equals Not Started then do x,y z. Case status value equals Complete then do some other actions. In each of the cases in this example all we are going to do is to add another increment variable action, select the appropriate variable and set the value to 1. You don't need anything in the default case box over on the right.

5-Flow.png

 

Next, outside the apply to each - in other words after the flow has looped through each of the items - add a compose control, one for each of the statuses. This is going to have the expression which will calaculate the percentage for each status. For the not started status the expression is:

mul(div(variables('varNotStarted'),variables('varTotalItems')),100)

 

which translates to "the variable varnotstarted divided by the variable vartotalitems, then multiply that by 100.

 

Do the same for the other 3 statuses, selecting the appropriate status variable.

6a-Flow.png

 

6b-Flow.png

 

We then want to format the resulting percentage so that it has, in this example anyway only 1 decimal place. So add a format number action, select the outputs of the calculation done above from the dynamic content and set the format to a custom value of ##.#. Do the same for the other 3 statuses.

 

7-Flow.png

 

Finally, add the send an email action (v2) or the send an email from a shared mailbox action, create your text and add in the outputs of the format number actions in the appropriate place. Don't forget (as I frequently do) to open the advanced options and set the importance otherwise it defaults to low.

8-Flow.png

 The result looks like this:

9-Email.png

 

Hope that helps but come back with questions if something isn't clear.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

 

Wow!!! Rob, this is absolutely amazing. This is my first time on this community but I can already see that it's going to be very educational for me. I'm going to take a shot at this tomorrow when I can have a clear run at it. I shall definitely respond to let you know how it's worked. Very appreciative of you having taken the time to send me a reply as comprehensive as this. Thank you very much! :)

Hi there @RobElliott, I got so far through your example only to fail at the final hurdle (still, this is progress...) I've copied the screen shot below that shows where I messed up. I tried creating all the status types within a single switch and when that didn't work, I tried individual switches which didn't work either but gave in fact gave me the same error message, very stern sounding: 

 

The execution of template action 'Switch' failed: The result of the evaluation of 'scope' action expression '@items('Apply_to_each')?['Status/Value']' is not valid. It is of type 'Null' but is expected to be a value of type 'String, Integer'.

 

Let me know if you need any more info to help us figure out a fix. 

 

Thanks again!

 

Ali

 

al_the_ginger_leader_0-1625675249819.png

 

@al_the_ginger_leader having multiple switches like that is not necvessary, isn't what I did and will slow your flow down. The error message indicates that one of the lines in your list is returning a null value for the status, nothing more serious than that. But your screenshot doesn't indicate whether you add the filter query or not so that these are not brought back by the get items action.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Thanks @RobElliott 

I've removed the excess switches. I'm still doing something wrong around the case box though. One variable has an 'equals' box attached to it but the rest don't. I can see this is wrong. I need to find a way to create multiple cases I think. This is interesting work I have to say. I may not have succeeded yet, but feel like I am learning a lot!

 

al_the_ginger_leader_0-1625733873684.pngal_the_ginger_leader_1-1625733923436.png

 

@RobElliott Ha-ha, okay, so I have figured out how to add individual cases. Still getting the same error message though...

 

Here's the image of the filter query, if that helps..

 

al_the_ginger_leader_0-1625754979926.png

 

Making progress down the flow though after messing about with the list itself. Got new error message now..

Unable to process template language expressions in action 'Compose_5' inputs at line '1' and column '14506': 'Attempt to divide an integral or decimal value by zero in function 'div'.'.

@al_the_ginger_leader sorry for the delay in getting back to you, I was travelling from the north of Scotland back to our home in Spain. I'll look through your most recent posts and get back to you asap.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@RobElliott 

Hey, thanks Rob, no rush. As this is 'development' for me, and not (currently) a core deliverable, I just appreciate your help in my endeavours! I've got the automation to send me emails now, but the figures just don't seem to be adding up..

 

Hope you enjoyed your time in Scotland. It's a beautiful landscape. I'm especially fond of Glencoe, although Glasgow is where my Dad's side of the family are from.

 

al_the_ginger_leader_0-1625821909570.png

 

 

@RobElliott 

Success! I deleted my statuses on the list, then re-added, ran the automation again and bingo! (see images below). Not sure why this worked, but it did, and that's the main thing.

 

The only slightly weird thing is that it sends me 4 emails as it counts the responses. The last one shows the total count. Anyway, that's not a huge problem. I'm just happy we got there. Thanks again for all your help :)

 

al_the_ginger_leader_0-1626080054559.png

al_the_ginger_leader_1-1626080076453.png

 

Glad you got it working. If it's sending you 4 emails it means you've probably got the send an email still inside an apply to each rather than outside it.

Scotland was good but most of the time spent building decking which we've wanted for years. At least the weather was good .

 

Rob
Los Gallardos

1 best response

Accepted Solutions
best response confirmed by al_the_ginger_leader (Copper Contributor)
Solution

@al_the_ginger_leader yes this is do-able with a flow that has quite a lot of actions but isn't particularly complicated, so I hope the details below will be clear for you.

 

In this example my SharePoint list has a choice column for the status and uses the 4 statuses you had in your post:

0-SP-List.png

I've assumed you will want to send a report out once a week, so I've added a recurrence trigger and set it to run at 9am every Friday: 

 

1-Flow.png

 

Next, we need to add 5 initialize variable actions, one to hold the number for each status and the fifth to hold the total number of items. Give each one a name (I always start mine with var), set the type to float and optionally you can set the initial value to 0. 

 

2-Flow.png

 

Next add a SharePoint get items action. As we only want to count those items where there is a status - and to overcome the annoying warning about adding a filter query when you save the flow - add a filter query of Status ne '[and expression of null}'. ne stands for not equal to. Don't forget the apostrophes. If your list has more than 100 items go to the settings for the get items action, switch the pagination toggle to on and set a number that is higher than the number of items. The reason for this is that the get items action only brings back a maximum of 100 items by default.

 

3-Flow.png

 

 

Next add an apply to each control. If you forget to do this the flow will add it automatically as it's needed as we are looping through the items in the list and looking at the status of each one. Select value from the dynamic content box.

 

Next, inside the appply to each add an increment variable action. Select the variable for the total items and set the value to 1:

4-Flow.png

 

Still inside the apply to each we want the flow to look at what the status is so add a switch control which is a type of condition. As the column in the list is a choice column type you need to select status value from the dynamic content, not Status. The switch looks at the status column  column in the SharePoint list and you have a "case" channel for each of the possible answers, i.e case status value equals Not Started then do x,y z. Case status value equals Complete then do some other actions. In each of the cases in this example all we are going to do is to add another increment variable action, select the appropriate variable and set the value to 1. You don't need anything in the default case box over on the right.

5-Flow.png

 

Next, outside the apply to each - in other words after the flow has looped through each of the items - add a compose control, one for each of the statuses. This is going to have the expression which will calaculate the percentage for each status. For the not started status the expression is:

mul(div(variables('varNotStarted'),variables('varTotalItems')),100)

 

which translates to "the variable varnotstarted divided by the variable vartotalitems, then multiply that by 100.

 

Do the same for the other 3 statuses, selecting the appropriate status variable.

6a-Flow.png

 

6b-Flow.png

 

We then want to format the resulting percentage so that it has, in this example anyway only 1 decimal place. So add a format number action, select the outputs of the calculation done above from the dynamic content and set the format to a custom value of ##.#. Do the same for the other 3 statuses.

 

7-Flow.png

 

Finally, add the send an email action (v2) or the send an email from a shared mailbox action, create your text and add in the outputs of the format number actions in the appropriate place. Don't forget (as I frequently do) to open the advanced options and set the importance otherwise it defaults to low.

8-Flow.png

 The result looks like this:

9-Email.png

 

Hope that helps but come back with questions if something isn't clear.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

 

View solution in original post