Find total of "Under Review" records within SharePoint, and of that total, how many belong to Approver 1, 2, 3, etc.

[mpistil]

I'm hoping someone has dealt with this before 🙂

I have a SharePoint list (we'll call this list InvoiceParent) of records for an Invoice Processing app I am building. Within the record line, I track the Approver's name (which would be one of the 20 possible Approvers that can be selected from the app), their Approvals app response, overall Status, etc. I have the Status field set up as Choice, with New/Under Review/Approved/Rejected as options.

I need a flow that will find all of the records with "Under Review" as the Status as well as find out how many of that total belong to each of the individual approvers (for example, 20 records are Under Review, 5 of which belong to Case Fox, 6 of which belong to James Smith, 2 of which belong to Jane Door, etc. totaling up to the total 20 records that are Under Review).

Then, I need those numbers to fill out a second SharePoint list (we'll call this list Approvers). Essentially, it's just a SharePoint list of all the approvers. I'd like to have a second column that gets populated with the data pulled from the InvoiceParent list. So, for example, within the Approvers list, for Case Fox, the second column would populate with the number 5, for James Smith, the second column would populate with 6, for Jane Door, the second column would populate with 2, and so on.

Is this possible? Thank you!!!

[wskinnermctc]

How many items are in the list or expected to be in the list? 1000's 10,000's?

Do the Approvers have a unique Identifier that can be linked to the Invoice list like an employeeId? Or will it just be their names and you are certain they will match exactly between lists?

Yes it is possible:

• Get Items - Invoice List
  • (Use the Filter Query to filter by column name and status to only pull the rows you want)
• Get Items - Approver List
• Apply to Each - values of Approver List
  • Filter Array - the Get Items - Invoice List by equals to Approver Name
  • Get length() of the Filter Query Outputs - this is a count of items
  • Update Item - in Approvers List with count of items

[mpistil]

@wskinnermctc 

This will be in the 1000s. Although, we are working through a strategy to archive data once it hits six months old, to keep the # of records down. And yes, the approver names will be identical in both lists as the columns are both Person/Group and fed from Active Directory (for background information, long story short, I have a flow that duplicates the individual approvers' names into a Single Line of Text column in both lists).

I've included screenshots from the lists to help describe what I'm trying to accomplish. Within the InvoiceParent SharePoint list, there are 4 records "Under Review" - 1 for Case Fox, 1 for Jane Door, and 2 for Tyler Brown. I'd like a flow to find those individual totals and then populate the Approvers SharePoint list column "#ofUnderReviewInv" with 1 for Case Fox, 1 for Jane Door, and 2 for Tyler Brown:

InvoiceParent:

And second, Approvers:

I've started to update my flow according to the steps you spelled out, but I don't think I'm following exactly once it hits the Apply to Each section. Would you mind looking this over?:

Thank you!!!

[wskinnermctc · Accepted solution]

Yes, you are very close. Here is an example I just made. I used a SP List we have of HR Business Partners who approve requests from another list I'm calling Invoices.

In my HRBP Approver List - the name of the HRBP is in the Title column. In my Invoices List, the name of the HR approver is in the HRFristLastName column.

[wskinnermctc]

@mpistil what I previously posted about updating the counts is the exact answer.

Below I'm going to show something that you should think about or consider. I'm not going to make an entire detailed flow, you will have to figure the rest of this out on your own or create a new forum post asking for assistance.

What prompted me to think about this is the 30 minute recurrence you have set. I think that is going to be a lot of unnecessary refreshes and updates.

You should think of a way to update the Approver count only when required. So when an item is created or a status has changed, then you update the count for that approver.

I'm going to show you how to Add to the count of a single approver. You will have to think about the condition and logic that would cause you to subtract from the count. Like when the status goes from Under Review to completed or whatever.

Also, I think your Approver list should have a column for each status so you can count overall invoices and status for each person. Maybe you are doing that, anyways.

Below is a flow that is triggered when an item is modified or created. After the trigger the "Get Changes for an Item or File" action will tell you if the values in a column have changed true or false. So like the name column of an invoice will most likely always be false since it stays the same. However, your status column will change when it goes from Pending Review to something else so that will be true.

I use a condition check to verify if my Status column has changed AND the Status column equals Under Review.

If Yes, then Get Items - Approvers is filtered by the approver name, so the Get Items should only return 1 item.

The compose is a way to extract the ID from the Get Items and avoid using an Apply to Each. This isn't necessary, but I prefer to avoid an Apply to Each when possible.

Then use the extracted ID to Get Item - Approver, a single item using an list item Id.

Then you update the item by adding 1 to the previous count.

Sample of flow updating a single item based on change conditionTrigger and get item changes to check which column has changed true or falseThese steps get the Approver id and then updates the count by adding 1 to it's current count.

I think I would spend more time building something like this so it is more about updating individual items instead of blanket stamping the entire list with an update.

Furthermore, you could have a flow that when an Invoice Item is Created to go ahead and get the Approver row Id and then put it into and extra column in the Invoices list. That way you can easily link the Invoice item to an Approver using the row Id. 

This is just something to think about, and you will have to go over the logic and consider check to determine when to add, when to subtract, or when to do nothing.

Good luck

[mpistil]

Your first scenario did work, so thank you!!

And I read through your secondary post -- that is a good call out. Running unnecessary flows to gather information vs. only running them when there has been an update to the data.

Again thank you for your help on this!!!! Made my day (and weekend) haha!