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

Counting Lookup Multi-select values

I'm having trouble counting values in a Lookup field from a SharePoint list that supports multiple values for an app I've created (my first app!).  Is there a way to count the values to see how many instances each of these has had?  The fields are semicolon delimited when viewed in quick edit.

 

Example data:

AgentNameIssues
BobSpelling;Documentation
JoePolicy Violation;Spelling
SamGrammar

 

From this, I would like either a chart or table to get count of the number of Issues in my datasource to have out output like this:

 

IssueCount
Spelling2
Documentation1
Policy Violation1
Grammar1

 

I've managed to count the total number of items, but not a count for each type of issue using the formula found here:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Get-Count-of-values-used-in-Multi-Lookup/m-p...

 

I've tried it with the field as a Choice field with multiple-selection as well with no success either.  Any help or tips you could provide would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Eelman
Super User
Super User

@WarrenBelz thanks for the tag, this was a tough one. Either that or I've got Sunday brain ...

 

@WJake 

Try this on a button then use colIssuesCount as the Items of a gallery to see your count:

ClearCollect(
    colMultiIssues,
    yourListName.Issues
);
Clear(colIssues);
ForAll(
    colMultiIssues,
    Collect(
        colIssues,
        Filter(
            Choices(yourListName.Issues),
            Value in Issues.Value
        ).Value
    )
);
ClearCollect(
    colIssuesCount,
    AddColumns(
        Distinct(
            colIssues,
            Value
        ),
        "Count",
        CountRows(
            Filter(
                colIssues,
                Value = Result
            )
        )
    )
)

You can always move this code to the App OnStart or the OnVisible of a screen, the design choice is yours. I tried refactoring this code but couldn't reduce it down but you may be able to do that?

 

Let me know how you get on.

 

Note: depending on the size of your SP list you may also need to consider delegation eg I'm not sure if the first ClearCollect above will only look at the first 2000 records?

View solution in original post

7 REPLIES 7
WarrenBelz
Super User
Super User

HI @WJake ,

The CountRows function can be used on galleries or other data filtered sets.

If you tell me exactly what you are trying to do, I can assist.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

WJake
Frequent Visitor

Thanks! I am looking to count the instances of each possible value for my Issues field and then place the result into a gallery. I know how to collect data and display it in gallery, but can’t figure out how to get this to count for multi selection fields.

Hi @WJake ,

I have just realised what you are after and this is not easy - I assume you want to count inside every field for a total number selected?

I will admit defeat on this after about 30 minutes of trying many and various things - I will tag my code guru colleague @Eelman who quite enjoys these challenges.

 

I will share where I got to if this helps - you can get a Collection with the values in separate fields in a table contained in CollectionName.Value with the items inside this in a field called Result, but to collectively count them is another matter.

ClearCollect(
   YourCollectionName,
   ForAll(
      YourTableName.YourFieldName,
      Split(
         YourFieldName,
         ","
      )
   )
)

 

Eelman
Super User
Super User

@WarrenBelz thanks for the tag, this was a tough one. Either that or I've got Sunday brain ...

 

@WJake 

Try this on a button then use colIssuesCount as the Items of a gallery to see your count:

ClearCollect(
    colMultiIssues,
    yourListName.Issues
);
Clear(colIssues);
ForAll(
    colMultiIssues,
    Collect(
        colIssues,
        Filter(
            Choices(yourListName.Issues),
            Value in Issues.Value
        ).Value
    )
);
ClearCollect(
    colIssuesCount,
    AddColumns(
        Distinct(
            colIssues,
            Value
        ),
        "Count",
        CountRows(
            Filter(
                colIssues,
                Value = Result
            )
        )
    )
)

You can always move this code to the App OnStart or the OnVisible of a screen, the design choice is yours. I tried refactoring this code but couldn't reduce it down but you may be able to do that?

 

Let me know how you get on.

 

Note: depending on the size of your SP list you may also need to consider delegation eg I'm not sure if the first ClearCollect above will only look at the first 2000 records?

View solution in original post

@Eelman - you have risen to the challenge again. My brain on any day of the week would not have got that one.

@WJake , you have the privilege of a real code expert helping you here.

WJake
Frequent Visitor

@WarrenBelz @Eelman 

 

Thanks!  I'm new to powerapps and self taught myself over the past week and couldn't get this one at all.  I had to convert the field from Lookup type to Choices in the Sharepoint list and then this worked without any issues.  I thought out the delegation issues by planning on archiving old data every year when it is no longer needed live which should keep it below the 2000 records.  I'm also creating each department their own list to further prevent going over 2000 records in a given year.

@WJake ,

That is the best move you could have made - if you have a few minutes, please read this post that I just finished a few minutes ago.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,966)