Jul 06 2021 02:38 AM
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
Jul 06 2021 06:09 AM - edited Jul 06 2021 06:11 AM
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:
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:
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.
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.
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:
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.
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.
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.
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.
The result looks like this:
Hope that helps but come back with questions if something isn't clear.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Jul 06 2021 07:11 AM
Jul 07 2021 09:30 AM
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
Jul 07 2021 10:02 AM
@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
Jul 08 2021 01:45 AM
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!
Jul 08 2021 07:37 AM
@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..
Jul 08 2021 07:50 AM
Jul 09 2021 01:34 AM
@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
Jul 09 2021 02:12 AM
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.
Jul 12 2021 01:55 AM
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 :)
Jul 12 2021 04:02 AM - edited Jul 12 2021 04:03 AM
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
Jul 06 2021 06:09 AM - edited Jul 06 2021 06:11 AM
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:
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:
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.
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.
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:
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.
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.
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.
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.
The result looks like this:
Hope that helps but come back with questions if something isn't clear.
Rob
Los Gallardos
Microsoft Power Automate Community Super User