Counting items in SharePoint list based on text condition and updating Secondary SharePoint list

[AgnieszkaPoland]

Hi Team,

Seems that again I need some help..

I’m looking for a solution that helps me to count the items in 1 SharePoint List and build a summary in a 2nd SharePoint List.

Seems that Power Automate is the best option (I do not have any knowledge in Power App).

These are my lists:

And this is the flow that I built till now, but I know that need to add some formulas 'somewhere'?

I will be grateful for any ideas and suggestions.

Thank you!

[Nived_Nambiar]

Hi @AgnieszkaPoland 

one question, does the column approved and rejected should count total number of approved and rejected values for each row or only approved ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[AgnieszkaPoland]

Hi @Nived_Nambiar 

My bad, as I calculated the cells manually - only for the example purpose. 

I'm caunting approved & rejected as this meand for me that any feedback was given by Approver 🙂 

[AgnieszkaPoland]

Hello  @Nived_Nambiar 

Do you have any suggestions ? 🙂 

I modified the flow a bit based on some suggestions find on youtube / MS community forum / chatgpt / copilot but my flow still doesn’t bring the effect.

3 main challenges that I can see:

1. I do not have number values, only text. Therefore, simple formulas don’t work.
2. Multiply conditions (each column separately) to be met.
3. If condition is met, final result must be divided into specific rows, dependent on PDT.

And the solutions that I found:

1. It can be counted eg with length expression.
2. Each condition can be considered in separately as all of them are independent.
3. This is something that I cannot figure out yet.

Nevertheless, taking everything into accounts, I started with below actions:

** Rows from 1st list are counted and saved in 2nd List correctly.

However, values are unreal + aren’t divided per PDT.

** Same here, rows from 1st list are counted and saved in 2nd List correctly.

However, values are unreal + aren’t divided per PDT.

Funny fact is that the same logic doesn’t work at all for columns with Approvers’ feedback.

Thank you in Advance! 

[Nived_Nambiar]

Hi @AgnieszkaPoland 

Sorry for late response !

I was busy for few days so could not be able to respond to few questions.

I have created a simple workflow simmilar to ur usecase and it seems to be working fine. Let me share the flow design.

For demo purposes, i have created 2 demo list like below

The below list takes count 

Note:- I have taken data for approver 1 only. Logic to be used for Approver 2 and Approver 3 count would be same once you go through flow logic.

Flow steps

1. Use trigger when item is modfied or created when item is modified/Created in PDT List

2. Get list of item corresponding to that PDT in list PDT List_2

3. Use get items again to get list of items corresponding to PDT in PDT List

4. Select the Business Process value from PDT List Get items using select action

5. Filter the list items from PDT List Get Items with Playbook status equals to Completed

6. Filter the list items from PDT List Get Items with Approver 1 status equals to Approved or Rejected.

Expression used -  or(equals(item()?['Approver1'], 'Approved'), equals(item()?['Approver1'], 'Rejected'))

5. Check whether there is any list item corresponding to the PDT in PDT List 2 using below condition

Expression -  length(outputs('Get_items_-_PDT_List_2')?['body/value'])

if condition is True : It means PDT item is already created, we have to just update values, for that follow the below steps .

a. Add update item to update item in PDT List 2 with updated count

Expression -  first(outputs('Get_items_-_PDT_List_2')?['body/value'])?['ID']

This expression helps to calculate the ID of item from PDT List 2.

Expression -  length(union(body('Select-_Business_Process'),body('Select-_Business_Process')))

This expression compute how many Business Process are for given PDT distinct values and take count of it.

Expression - 

length(body('Filter_array-Completed_Playbooks'))

This expression computes the total count of Completed Playbooks. if the item created/Modfiied in PDT list has Completed 

Expression - 

length(body('Filter_array-Approved_Rejected'))

This expression calculate the count of items for that PDT which has Approver 1 Rejected or Approved.

If the condition is false- then we have to create item in PDT List 2.

Expression - 

length(union(body('Select-_Business_Process'),body('Select-_Business_Process')))

This expression computes how many Business Process for that PDT.

Expression - 

length(body('Filter_array-Completed_Playbooks'))

Expression - 

length(body('Filter_array-Approved_Rejected'))

This should the way how the flow to be designed.

Results 

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[AgnieszkaPoland]

Oh WOW! I don't know what to say - thank you so so so much! I highly appreciate all your effort! Your Reply is so detailed and complex - Thank you once again !

Can I buy you a coffee? 🙂

If it comes to my flow - I have been working on it all day but I'm still getting some errors:

I tried to replace this function somehow but I know that it doesn't make sense to choose only 'ID' as 'Apply to each' section appears automatically ...

I also asked Chat GPT / Copilot and reviewed Community forum again looking for the answer but I failed again. 

Do you have any idea what might be wrong?

The 'funny' thing is that my PDT List 2 is completely empty now (I removed all content as I wanted to check 'No' way) but flow still fails in 'Yes' path:

Once again, thank you so much for your time and engagement!

[Nived_Nambiar]

Hi @AgnieszkaPoland 

Could you show the condition which you have written ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[AgnieszkaPoland]

Hi @Nived_Nambiar ,

Sure, I followed each and single step of your wonderful instruction : 

[Nived_Nambiar]

Hi @AgnieszkaPoland 

expression is correct, but see my condition below

I have used not equals to  while you have used equals to 

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[AgnieszkaPoland]

Hello @Nived_Nambiar 

Thank you once again for your will to support me. 

Omg.. so silly mistake. You’re fully right! 

I did a double very detailed comparison of both flows: yours and mine.

Now the flow ran successfully, I mean : List 2 is empty, so flow went ‘NO’ path.

Nevertheless, seems that it doesn’t work as it should…

Do you have any idea what might be the reason of this?

While at the same time:

FLOW DETAILS:

(here I know that I have ‘contains’ instead of ‘equal to’ but it’s because of the fact that in real life I have 2 statuses: ‘Completed’ or ‘Completed pending development’)

[Nived_Nambiar]

Hi @AgnieszkaPoland 

Few doubts 

1. What type of column is PDT ?

2. If i look at expression where counting of approval status is done, i could see you have placed a space after approved and rejected 

I think it was not needed there.

Also for the first question i have asked-  just try with PDT value dynamic content instead of PDT, see whether that helps ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌