cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tjtaylor1164
Helper I
Helper I

Filter gallery by multiple choice fields

I have a SharePoint list with column names FollowupLead, FollowupLead2, FollowupLead3  - text fields

In the same SharePoint list, I have column names FollowupLeadCompleted, FollowupLead2Completed, FollowupLead3Completed - choice fields. The choice field columns all have the same information for users to choose from: "Not Started" "Completed" and "In-process"

 

I would like users to filter data based on "Not Completed" "Completed" or "In-process" that would pull information from the three choice field columns. 

 

The second scenario would be for users to search for their name from the three text fields so only their name appears and all results show from the choice fields, or they could filter based on "Not Started" "Completed" or "In-process."

 

What is the best way to handle this? I think I'm confusing myself.

 

Thank you!!

 

1 ACCEPTED SOLUTION

Accepted Solutions

@tjtaylor1164 ,

Not as much as putting the leads and completions as separate items (three fields each - status, date and number) in a sub-list rather than the six you have. All your reporting issues would go away. You would need another field (I call mine IDRef) defaulted to the ID of the parent list so these can be easily displayed in a gallery and updated as required. If you want to go down this track, it is a separate subject from your post, so you need to accept this one and post a new thread.

You can tag either myself or @mdevaney - we can both help you.

View solution in original post

15 REPLIES 15
mdevaney
Super User
Super User

@tjtaylor1164 

You can perform the search for the name like this.  I suggest using StartsWith instead of the Search function because it can be delegated.  In this example, all 3 columns are filtered at the same time using a single text-input.

Filter(
  your_datasource_name,
  StartsWith(FollowupLead, TextInput1.Text)
  Or StartsWith(FollowupLead2, TextInput1.Text)
  Or StartsWith(FollowupLead3, TextInput1.Text)
)

 

I do not understand what your requirement is for the Choices columns though.  What does the interface for your app look like when it comes to searching those columns?


---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

I had combo box for users to choose one of the three options: "Not Completed" "Completed" or "In-process" but I think I did something wrong in my combo box.  The SearchFields are FollowupLeadCompleted, FollowupLead2Completed, FollowupLead3Completed.

 

I had an error in the filter on my gallery. I was trying to get these three options to pull from the three choice field columns - FollowupLeadCompleted, FollowupLead2Completed, FollowupLead3Completed so it will display all items "Not Completed" from any of these three choice field column names.

@tjtaylor1164 

I think you should send a screenshot showing your interface.  Words are not painting a picture here, lol 😉

 

I don’t understand how you plan to control the filter for the choices.  To me your explanation means you are trying to do it with one field but I can make sense of it.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Below is a screen capture of the screen. Status is a dropdown from a share point list:  Items=Choices(Notes.FollowupLead2Completed)

 

Followup screen.PNG

 

I originally had my gallery filtering by date:

  Items=Filter(Notes, FollowUpDate >= DatePicker1.SelectedDate, FollowUpDate <= DatePicker2.SelectedDate)

 

Gallery.PNG

However, it's more important for them to filter by Staff Lead and/or Status. I changed my gallery filter to:

Items=

Filter(
Notes,
StartsWith(FollowUpLead, SearchBox.Text)
Or StartsWith(FollowupLead2, SearchBox.Text)
Or StartsWith(FollowupLead3, SearchBox.Text)
)
 
How do I add a filter to the gallery when the dropdown doesn't have all my info - just the one column? Items=Choices(Notes.FollowupLead2Completed). Can I add the other columns to my items list in the dropdown to include FollowupLeadCompleted and FollowupLead3Completed? I tried the combo box but it didn't work out either. 

 

@tjtaylor1164 

Great pictures!  My question is: how does the one ComboBox search three columns?  This interface does not make sense to me... if there are three Choices columns to be searched there should be three ComboBoxes, right?

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Well that was my problem. I was hoping there was a way to do it since all the choice columns were all the same. 🙂

 

I attached another screen capture to see if there is a better way to do this. It was built for a date range, but then company wanted to add follow-up by lead and whether the action was completed.

 

As you can see, for each organization there may be three follow-up leaders with separate follow-up dates and actions and level completed. Most of the time, it will be just one lead but they wanted a scenario in case there were multiple leads. They want to see all the actions by "Not Completed" or "Completed" or "In-process." Suggestions?

 

Followup screen.PNG

@tjtaylor1164 
The interesting thing I am seeing here is there only appears to be 1 column for Follow Up Leads and 1 column for Status.  There also,  appears to be 3 rows of data.

 

Your problem statement had me believing there were 3 columns for Follow Up Leads, 3 columns for Status and 1 row of data.

 

Which is true of your SharePoint list?

There are columns for each row - one column for follow-up date, one for lead name, one for completed and one for notes.

 

So the rows,

Lead has for four fields (columns) of data: FollowupLeadDate (date), FollowupLeadName (text), FollowupLeadCompleted (choice) and FollowupLeadNotes (text)

Lead 2 has: FollowupLead2Date, FollowupLead2Name, FollowupLead2Completed and FollowupLead2Notes

Lead 3 has: FollowupLead3Date, FollowupLead3Name, FollowupLead3Completed and FollowupLead3Notes

@tjtaylor1164 
I'm so sorry for all the questions.  I really am trying but I find myself no to understanding what your data structure though.  To me: this is what you have been describing your SP list as (for rows 1 & 2).

 

FollowupLeadDate  FollowupLeadName  FollowupLeadCompleted  FollowupLeadNotes FollowupLead2Date FollowupLead2Name FollowupLead2Completed FollowupLead2Notes
Data Data Data Data        
        Data Data Data Data

 

@WarrenBelz 

If you understanding what's going on here feel free to chime in.  I am completely lost.

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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,786)