cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Iamglt
Helper V
Helper V

Count records in a SharePoint list that have a specific value in a multi choice SharePoint field

Hi,

I have a field in my SharePoint list called Status which is a multi choice field.  It's multi choice as the workflow that submits when my form is saved runs several tasks in parallel, therefore the status field can be set to processing by our Helpdesk and Security teams at the same time.  On the gallery, I use a label to display the status using the following code (see first picture attached):

 

Concat(ThisItem.Status.Value, Value,Char(13))

 

This section works fineThis section works fine

The options for the Status field are:

Saved - Not Submitted

Security - Processing

Help Desk - Processing

Rejected

Completed

 

Now at the top of my gallery, I am trying to display a summary outstanding / completed requests.  I use the following code to get these results:

 

CountRows(
Filter('New Employee Workflow', Concat(Status, Value & ";") in Concat(Status, "Saved - Not Submitted" & ";"))
)

 

Workflow2.JPG:

 

The above code works fine if the Status field only contains one value (as you can see from the above picture with Saved, Rejected and completed), but when the field has more than one value (such us Security - Processing and Help Desk - Processing), it shows up as 0.  Can anyone advise why this is not working and the code I need to get this working?

 

Also, I am trying to add a feature so that when one of the summary numbers are selected (such as the Saved with three records), then the gallery will filter and show only those three records.  This may be tricky as well due to the multi choice field - when I select Security I only want to show all records where the status contains 'Security - Processing', even though the field may also contain Help Desk - Processing' as well.

 

Workflow2.JPG

 Any help is always greatly appreciated.

 

Cheers,

Geoff.

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Jeff_Thorpe
Super User
Super User

Try this to get to get the count for "Saved - Not Submitted" and if that works you can change the status for each of the Status Count labels.

CountRows(Filter('New Employee Workflow', "Saved - Not Submitted" in Status.Value))

 



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

You are a champion - thankyou 😀

View solution in original post

Please send your email by PM (on phone now, not PC)

View solution in original post

25 REPLIES 25
Jeff_Thorpe
Super User
Super User

Try this to get to get the count for "Saved - Not Submitted" and if that works you can change the status for each of the Status Count labels.

CountRows(Filter('New Employee Workflow', "Saved - Not Submitted" in Status.Value))

 



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

WarrenBelz
Super User
Super User

*** Update *** 

@Jeff_Thorpe answered since I opened - his solution is correct.

Hi @Iamglt ,

Try this

 

CountRows(
   Filter(
      'New Employee Workflow',
      "Saved - Not Submitted" in Status.Value
   )
)

 

 

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.

You are a champion - thankyou 😀

View solution in original post

Iamglt
Helper V
Helper V

For my second part of the question, I followed a you tube video and created an update context for each of the Saved, Security, Helpdesk, Rejected and Completed labels in the OnSelect function as follows:

 

For Saved Label:  UpdateContext({FilterBySaved:!FilterBySaved})

For Security Label: UpdateContext({FilterBySecurity:!FilterBySecurity})

For Helpdesk Label: UpdateContext({FilterByHelpdesk:!FilterByHelpdesk})

For Rejected Label:  UpdateContext({FilterByRejected:!FilterByRejected})

For Completed Label:  UpdateContext({FilterByCompleted:!FilterByCompleted})

 

Now the items in the Gallery currently shows the following:

gallery1.JPG

But If I try to add the IF statements and put a comma or semicolon after the 'New Employees Workflow', I cant get this working - all the fields on the Gallery turn red.  However, if I replace the Item property with one If statement, then it works.  Can you advise what I am doing wrong?

 

Cheers,

Geoff.

 

'New Employee Workflow',
If(FilterBySaved = true,(Filter('New Employee Workflow', "Saved - Not Submitted" in Status.Value)),'New Employee Workflow');
If(FilterBySecurity = true,(Filter('New Employee Workflow', "Security - Processing" in Status.Value)),'New Employee Workflow');
If(FilterByHelpdesk = true,(Filter('New Employee Workflow', "Help Desk - Processing" in Status.Value)),'New Employee Workflow');
If(FilterByHelpdesk = true,(Filter('New Employee Workflow', "Rejected" in Status.Value)),'New Employee Workflow');
If(FilterByHelpdesk = true,(Filter('New Employee Workflow', "Completed" in Status.Value)),'New Employee Workflow')

 

gallery2.JPG

 

 

Hi @Iamglt ,

Something like this should do the job.

Filter(
   'New Employee Workflow',
   (FilterBySaved || Status.Value = "Saved - Not Submitted") && 
   (FilterBySecurity || Status.Value = "Security - Processing") &&
   (FilterByHelpdesk || Status.Value = "Help Desk - Processing") &&
   (FilterByRejected || Status.Value = "Rejected") &&
   (FilterByCompleted || Status.Value = "Completed")
)

You had some mistypes of values in your posted code, but I think the above is what you meant.

 

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.

Hi Warren,

 

Thanks for your response with this - I tried your code and get the following screen:

 

powerapps error.JPG

Anytime I put a comma or a semi colon after the 'New Employee Workflow" that screen appears.  Im wondering if its because SharePoint list name has spaces in it?

 

Cheers,

Geoff.

Here is the description of the error:

galleryerror.JPG

 

I tried changing the semicolon to a comma, the red squiggles dissipated but now Im getting Invalid Arugument type for each line:

 

galleryerror2.JPG 

@Iamglt ,

Just a semi-colon instead of a comma - I was answering someone European just before. Now corrected.

 

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

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.

Users online (1,292)