cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamH
Post Patron
Post Patron

Search multiple columns in SP List

Hi All, 

 

I'm trying to work out how I can search multiple columns in my SP List.

 

I've tried all the usual techniques, done some research but failing to add another column.  

 

Currently my code is:

 

With(
    {WMST: 'NC MST Tracker'}, Sort(
    Filter(
        WMST,
        Or(IsBlank(TextInput1.Text),
        TextInput1.Text in 'Employee Name',
        StartsWith(
            'Employee Name',
            TextInput1.Text        
            )) && Switch(
            Gallery1_1.Selected.Value,
            "Mentor",
            Mentor,
            "AET",
            'AET Instructor',
            "MST",
            'MST Assessor',
            "All",
            All,
            true
         && Grade.Value = Gallery1_1.Selected.Value)
    ),'Employee Name',If(SortDescending1, Descending,Ascending))
)

 

I have a column called 'Cohort' - How would I add this?

1 ACCEPTED SOLUTION

Accepted Solutions
AdamH
Post Patron
Post Patron

@WarrenBelz 

Apologies, I forgot this was still open.

The code used to filter items in a gallery between a toggle switch (true or false) value and a choice field (drop down multiple options) value was:

With(
    {WMST: 'MY_SP_LIST'}, Sort(
    Filter(
        WMST,
        (IsBlank(TextInput1.Text) ||
        (TextInput1.Text in 'Employee Name' || TextInput1.Text in Cohort || TextInput1.Text in 'Payroll Number' || TextInput1.Text in iPadAsset))
                         
            && Switch(
            Gallery1_1.Selected.Value,
            "Mentor",
            Mentor,
            "AET",
            'AET Instructor',
            "MST",
            'MST Assessor',
            "All",
            All,
            true
            
         && Grade.Value = Gallery1_1.Selected.Value)
    ),'Employee Name',If(SortDescending1, Descending,Ascending))
)

 

View solution in original post

3 REPLIES 3
WarrenBelz
Super User
Super User

@AdamH ,

You have the Switch statement in there which is doing nothing (we discussed this in a previous post - the true value at the end it simply returning all records and not filtering). You also do not need StartsWith() if you already have the in Filter as this will return the string anywhere in the record - so if the gallery value match your Grade values

With(
   {WMST: 'NC MST Tracker'}, 
   Sort(
      Filter(
         WMST,
         Or(
            IsBlank(TextInput1.Text),
            TextInput1.Text in 'Employee Name'
         ) && 
         Grade.Value = Gallery1_1.Selected.Value &&
         Cohort = YourValueHere
      ),
      'Employee Name',
      If(
         SortDescending1, 
         Descending,
         Ascending
      )
   )
)

If not and you need the Switch statement to convert from your top menu buttons values, you need to add the field it is matching against (Grade)

With(
   {WMST: 'NC MST Tracker'}, 
   Sort(
      Filter(
         WMST,
         Or(
            IsBlank(TextInput1.Text),
            TextInput1.Text in 'Employee Name'
         ) &&
         Cohort = YourValueHere &&
         (
            Gallery1_1.Selected.Value = "All" ||         
            Grade.Value = 
            Switch(
               Gallery1_1.Selected.Value,
               "Mentor",
               Mentor,
               "AET",
               'AET Instructor',
               "MST",
               'MST Assessor'
            )
         )
      ),
      'Employee Name',
      If(
         SortDescending1, 
         Descending,
         Ascending
      )
   )
)

I am happy to explain all of the working to you so you can understand this going forward as I thought I had you on the right patch in the last couple of threads.

 

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.

Visit my blog Practical Power Apps

 

WarrenBelz
Super User
Super User

Hi @AdamH ,

Are you able to close this post now please - you might also include the final code.

AdamH
Post Patron
Post Patron

@WarrenBelz 

Apologies, I forgot this was still open.

The code used to filter items in a gallery between a toggle switch (true or false) value and a choice field (drop down multiple options) value was:

With(
    {WMST: 'MY_SP_LIST'}, Sort(
    Filter(
        WMST,
        (IsBlank(TextInput1.Text) ||
        (TextInput1.Text in 'Employee Name' || TextInput1.Text in Cohort || TextInput1.Text in 'Payroll Number' || TextInput1.Text in iPadAsset))
                         
            && Switch(
            Gallery1_1.Selected.Value,
            "Mentor",
            Mentor,
            "AET",
            'AET Instructor',
            "MST",
            'MST Assessor',
            "All",
            All,
            true
            
         && Grade.Value = Gallery1_1.Selected.Value)
    ),'Employee Name',If(SortDescending1, Descending,Ascending))
)

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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