cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roxy1
Frequent Visitor

Count multi choice selected values in a choice column

Hi,

 

A huge thank you to everyone who posts and provides solutions in this community - it has assisted me in learning how to build flows immensely.

 

I am now a little stuck so I am posting on here - crossing my fingers for some assistance!

 

This is what I am wanting the flow to do:

  • At the start of the current month, get all the previous month's data from a SharePoint list 'Incoming Offers List'. I have the dates in a data column called 'Date2'
  • From the previous month's data I want to be able to count how many times selected values were chosen in a choice column titled 'Format' (users can select multiple values in the column at the moment there are 7 options to select from but my flow is using two as I am trying to get it to work!)
  • Then update a new SharePoint List 'Incoming Offers Stats' with these results : with the previous month's name in one column, and then separate columns for each multi choice value with the corresponding number count for how many times the choice was selected. In theory, collecting stats!

 

I believe my flow so far works correctly in obtaining the last month's data using a filter query.

 

The trouble I am having is that the filter array does not correctly add the values - it seems to add every row of data returned for these dates instead of the values. I also have not found a way to display the last month name, only this months I have tried different options for flows for a few days scratching my head, using string variables instead of an array, parse json with no luck. I thought I better stop wasting time and see if anyone has any ideas!

 

My current flow is as follows:

 

Flow1.jpg

 

Flow2.jpg

The length expressions in "Compose" both link back to each separate array eg: 

length(body('Filter_array_2'))

 

Flow3.jpg

 

 

This is a screenshot for reference of the "Incoming Offers list" multi choice format column I am trying to add up the selected choices, and the next is the "Incoming Offers Stats" list I am wanting to place the counts in for the month:

IncomingOfferList.jpg

 

IncomingOffersStats.jpg

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Dual Super User
Dual Super User

Hi @roxy1,

 

I think the challenge is that you are looping through list items and from those list items you want to loop through format choice items. Nested arrays, always fun 😁

 

I would use a select within both the apply to each loops and use the format collection in the from field of that select. Only use the value field of that array in the select:

item()?['Value']

 

This way you only need to look at the item() in your filter array actions.

 

Below is a screenshot of that approach.

Btw, in that example I am also using an increment variable action to keep track of the count. That variable is using the same length function like you were using before.

 

 

incrementvariable_countoccurences.png

View solution in original post

2 REPLIES 2
Expiscornovus
Dual Super User
Dual Super User

Hi @roxy1,

 

I think the challenge is that you are looping through list items and from those list items you want to loop through format choice items. Nested arrays, always fun 😁

 

I would use a select within both the apply to each loops and use the format collection in the from field of that select. Only use the value field of that array in the select:

item()?['Value']

 

This way you only need to look at the item() in your filter array actions.

 

Below is a screenshot of that approach.

Btw, in that example I am also using an increment variable action to keep track of the count. That variable is using the same length function like you were using before.

 

 

incrementvariable_countoccurences.png

Thank you so much for your time in creating this and answering. You've saved me weeks of trying to figure it out!

 

For anyone else who reads this post for the solution, don't forget to initialise the integer variables in your flow so you can give them their value in the above solution step 🙂

 

I also used individual "compose" for each format total as they needed to be put in separate columns in the new share point list.

 

Thanks again!

 

solution.jpg

 

 

 

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

New Process Advisor Capabilities carousel.png

Read the blog for the latest news

Read the latest about new experiences and capabilities in the Power Automate product blog.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (8,362)