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.

[Iamglt]

Yes comma has got rid of the errors but now the gallery comes up with no records - any ideas why this is happening?

Cheers,

Geoff.

[WarrenBelz]

@Iamglt ,

One issue is Delegation, which I can fix if you have less than 2,000 items in your list like this - I will also go back to your in statement as I suspect Status.Value is not a single value? That should be the only reason you would be getting an error there.

With(
   {wNew:'New Employee Workflow'},
   Filter(
      wNew,
      (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)
   )
)

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.

[WarrenBelz]

@Iamglt ,

I think our replies crossed - what is the final code you have with no errors?

[Iamglt]

Apologies I missed your last reply - I tried your latest code and there are no errors, however the Gallery now contains no records.  The list only has 5 records in it, however the Status field is a multi choice field so it can contain any of these values:

Saved - Not Submitted

Security - Processing

Help Desk - Processing

Rejected

Completed

This is the screen shot I now have with the latest code:

[WarrenBelz]

Hi @Iamglt ,

A bit of the "forest and the trees" going on - I was focusing on valid values without really looking at the logic - I think you need this

With(
   {wNew:'New Employee Workflow'},
   Filter(
      wNew,
      (!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)
   )
)

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 tried your latest code and still the same result.  I am still trying to get my head around the code and the way it works, but I want to default so that the gallery display all records when the form opens, and only applies the filters when the saved, security, helpdesk controls are selected.  In other words, if filterByxxxx are false, then display all records - really appreciate your help here its been driving me nuts 🙂

[WarrenBelz]

Hi @Iamglt ,

Before we go any further, have a look at the values of your Variables - put a label on the screen with this

"Saved - " & FilterBySaved & Char(10) & 
"Security - " & FilterBySecurity & Char(10) & 
"Help Desk - " & FilterByHelpdesk & Char(10) &
"Rejected - " & FilterByRejected & Char(10) & 
"Completed - " & FilterByCompleted

then see what value are in them as you go. As for the logic of the code - this is the long version

With(
   {wNew:'New Employee Workflow'},
   Filter(
      wNew,
      If(
         Not(FilterBySaved),
         true,    //show all records 
         "Saved - Not Submitted" in Status.Value)
      ) && 
      If(
         Not(FilterBySecurity),
         true,   //show all records 
         "Security - Processing" in Status.Value)
      ) &&
      If(
         Not(FilterByHelpdesk),
         true,   //show all records 
         "Help Desk - Processing" in Status.Value)
      ) &&
      If(
         Not(FilterByRejected),
         true,   //show all records 
         "Rejected" in Status.Value) 
      ) &&
      If(
         Not(FilterByCompleted),
         true,   //show all records 
         "Completed" 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]

Hi Warren,

The values of the variables are as follows:

Saved - true

Security - true

Helpdesk - false

Rejected - true

Completed - true

They should all be false...

[Iamglt]

Hi Warren,

I just did a test with the long version of your code, but only used the first If statement, and it worked perfectly.  As soon as I add the && and anything after that I get red squiggles everywhere.  I am starting to think the Item property can only one If statement.  Maybe we can use If,else,else...? 

[WarrenBelz]

@Iamglt ,

Well, there is some of the issue. You need to track where you set them and see what has gone wrong.

Secondly, how many records are in your list and what time zone are you in?

Lastly, (basic debugging) try these one at a time and see what you get - I have added getting the newest records.

With(
   {
      wNew:
      Sort(
         'New Employee Workflow'
         ID,
         Descending
      )
   },
   Filter(
      wNew,
      "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.