Groupby and Count

[Jojo222813]

Hi, I am new to Power Automate.

I have 2 sharepoint list. I want to create a flow that will go through the List1 and count the complete status of each email. There is another list called List2. If email is not in that list it creates the entry if it is there then just update the count. So in that list i have email and the count of status = "Completed". 

I know that sharepoint list can filter by status and groupby the email. However, I need to export those information for user, that's why I need another sharepoint list to store those data.

List1

ID	Title (Email)	Date	Status
1	abc@email.com	17/2/2022	Completed
2	abc@email.com	3/3/2022	Completed
3	abc@email.com	28/3/2022	Scheduled
4	def@email.com	1/3/2022	Completed
5	ghi@email.com	1/4/2022	Scheduled
6	jkl@email.com	3/3/2022	Completed

List2

ID	Title (Email)	CountStatus
1	abc@email.com	2
2	def@email.com	1
3	ghi@email.com	0
4	jkl@email.com	1

[v-qiaqi-msft]

Hi @Jojo222813,

Do you want to update the list2 based on the items created in list1?

Could you please share a bit more about your scenario?

Here is a flow I will configure to achieve your need.

1). You should initialize a variable to store the count number

2). Apply to each record for those returned from Get items:

Note that the first Title is from the trigger and the second Title is from the Get items

3). Update the item in list 2:

Here is the test result:

[Jojo222813]

@v-qiaqi-msft  Yes, I want to update list 2  based on the items created or update in list1. 

My scenario is when I update or create an item in list 1, then trigger a flow to update list 2 count (the status eq 'Completed'). 

Since list 1 already has some data, I am not sure how to achieve the first update in list 2.

May I know is your solution will update the count of each 'Title' everytime?

[Jojo222813]

Hi @v-qiaqi-msft ,

I tried to follow your solution, please i cannot get the correct result. Every record CountStatus = 3. 

The countstatus should be as below:

	Title	CountStatus
1	admin@M365x56770927.onmicrosoft.com	3
2	AlexW@M365x56770927.OnMicrosoft.com	3
3	IrvinS@M365x56770927.OnMicrosoft.com	1
4	MeganB@M365x56770927.OnMicrosoft.com	2

[v-qiaqi-msft]

Hi @Jojo222813,

Could you please show me you flow configure?

I have provided the screenshot of my flow configure which works great, please check it one more time.

[Jojo222813]

Hi @v-qiaqi-msft ,

Please let me know if you need more information.

[v-qiaqi-msft]

Hi @Jojo222813，

That's weird. 

I have test it again on my side and the flow runs great.

Could you please check the variable settings?

[Jojo222813]

Hi @v-qiaqi-msft 

Please find the variable setting below: