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.

 

 

25 REPLIES 25

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

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. 

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.

 

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

 

Cheers,

Geoff.

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

View solution in original post

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 🙂

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (68,883)