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

[Iamglt]

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 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" & ";"))
)

:

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.

 Any help is always greatly appreciated.

Cheers,

Geoff.

[Jeff_Thorpe · Accepted solution]

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))

[WarrenBelz]

*** 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.

[Iamglt · Accepted solution]

You are a champion - thankyou 😀

[Iamglt]

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:

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')

[WarrenBelz]

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.

[Iamglt]

Hi Warren,

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

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.

[Iamglt]

Here is the description of the error:

[Iamglt]

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

 

[WarrenBelz]

@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.