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.

[WarrenBelz]

@Iamglt ,

OK, I will attack it another way - danger of trying to visualise an outcome rather than being able to test it.

Bear in mind that you will need to have either none or one variable true here

With(
   {
      New:
      Sort(
         'New Employee Workflow',
         ID,
         Descending
      )
   },
   Filter(
      wNew,
      If(
         FilterBySaved,
         "Saved - Not Submitted" in Status.Value,
         FilterBySecurity,
         "Security - Processing" in Status.Value,
         FilterByHelpdesk,
         "Help Desk - Processing" in Status.Value,
         FilterByRejected,
         "Rejected" in Status.Value,
         FilterByCompleted,
         "Completed" in Status.Value),
         true
      )
   )
)

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,

I am in Sydney Australia time and have 8 test records in my SharePoint list.  I also tried the code above to debug with each of the options, and they all worked perfectly. 

[WarrenBelz]

Hi @Iamglt ,

I am in Rockhampton Queensland, so we can do Teams if necessary, but I think now this is all about your Variables if the other Filters work. I will be offline shortly, but please have a good look how you set them and watch the Label for them to change. Also if you try the latest code as this should work on the first true variable it finds.

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]

Ok im going to finish as well, been at this all week.  Can we do teams tomorrow?

Cheers,

Geoff.

[WarrenBelz · Accepted solution]

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

[Iamglt]

Big thank you Warren for helping me out with this - indeed it was my variables that were incorrect.

Changed the UpdateContext Varibles for the OnSelect property for each of the dashboard numbers to:

For FilterbySaved:

UpdateContext(
{
FilterBySaved: !FilterBySaved,
FilterBySecurity: false,
FilterByHelpdesk: false,
FilterByRejected: false,
FilterByCompleted: false
}
)

For the FilterBySecurity:

UpdateContext(
{
FilterBySecurity: !FilterBySecurity,
FilterBySaved: false,
FilterByHelpdesk: false,
FilterByCompleted: false,
FilterByRejected: false
}
)

For Filter by Helpdesk:

UpdateContext(
{
FilterByHelpdesk: !FilterByHelpdesk,
FilterBySaved: false,
FilterByCompleted: false,
FilterByRejected: false,
FilterBySecurity: false
}
)

For FilterbyRejected:

UpdateContext(
{
FilterByRejected: !FilterByRejected,
FilterBySaved: false,
FilterBySecurity: false,
FilterByHelpdesk: false,
FilterByCompleted: false
}
)

For FilterbyCompleted:

UpdateContext(
{
FilterByCompleted: !FilterByCompleted,
FilterBySaved: false,
FilterBySecurity: false,
FilterByHelpdesk: false,
FilterByRejected: false
}
)

Then the code for the Gallery Items is as follows:

ith(
{
wNew:
Sort(
'New Employee Workflow',
ID,
Descending
)
},
Filter(
wNew,
If(
FilterBySaved,
"Saved - Not Submitted" in Status.Value,
FilterBySecurity,
"Security - Processing" in Status.Value,
FilterByHelpdesk,
"Help Desk - Processing" in Status.Value,
FilterByRejected,
"Rejected" in Status.Value,
FilterByCompleted,
"Completed" in Status.Value,
true
)
)
)

Works beautifully - Thank you Warren 🙂