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

Searching filtered data

I am working on an app in which I would like to filter a data set (SharePoint list) and then search that data set based on info entered into a text box.  I have a gallery at the top with two options, My Records and All Records which should filter the next gallery accordingly.  Below is the code I currently have set up for the second gallery Items option.  The search on the All Records option works well.  I can't get the search to work when I filter to just the logged-in user's records (My Records).... (obviously it won't work with the code...that's why I'm here).

 

Also is there a better way, in general, to do this so that I can avoid the delegation issues?  My data set at the moment is under the 2000 record limit but I can imagine that in the future, it could grow over that.  

 

Thanks

Mike

 

 

SortByColumns(
    If(
        GalleryFilter.Selected.Value = "My Records",
           Filter('EMS STAT (AAR)','Created By'.Email = varUser.Email),
                       
        Filter(
        'EMS STAT (AAR)',TextSearchBox.Text in 'Call-out Location' || 
        TextSearchBox.Text in  'Substantive Case File Number' || TextSearchBox.Text in 'Bureau Name'.DisplayName
        )
    ),

"Start_x0020_Date", 
    If(
        SortDescending1,
        Ascending,
        Descending
    )
)

 

 

 

Mike_N_0-1605646352665.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

I fixed the issue.  I needed to add the StartsWith statements to the first Filter.  Complete code below.  Thanks for your help @KrishnaV 

 

SortByColumns(
    If(
        GalleryFilter.Selected.Value = "My Records",
           Filter('EMS STAT (AAR)','Created By'.Email = varUser.Email,StartsWith('Call-out Location',TextSearchBox.Text) || StartsWith('Substantive Case File Number',TextSearchBox.Text) || StartsWith('Bureau Name'.DisplayName,TextSearchBox.Text)),                       
        Filter('EMS STAT (AAR)',StartsWith('Call-out Location',TextSearchBox.Text) ||
StartsWith('Substantive Case File Number',TextSearchBox.Text) || 
StartsWith('Bureau Name'.DisplayName,TextSearchBox.Text)
        )
    ),

"Start_x0020_Date", 
    If(
        SortDescending1,
        Ascending,
        Descending
    )
)

View solution in original post

7 REPLIES 7
KrishnaV
Community Champion
Community Champion

Hi @Mike_N ,

 

unfortunately, Search and In are not a delegable operator in Power Apps. Change your query as below to make it work:

SortByColumns(
    If(
        GalleryFilter.Selected.Value = "My Records",
           Filter('EMS STAT (AAR)','Created By'.Email = varUser.Email),                       
        Filter('EMS STAT (AAR)',StartsWith('Call-out Location',TextSearchBox.Text) ||
StartsWith('Substantive Case File Number',TextSearchBox.Text) || 
StartsWith('Bureau Name'.DisplayName,TextSearchBox.Text)
        )
    ),

"Start_x0020_Date", 
    If(
        SortDescending1,
        Ascending,
        Descending
    )
)

 


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

Thanks @KrishnaV for the quick reply.  That resolved my delegation issue/warning but I still can't search on those same 3 columns when I'm on the "My Records" filtered data set. 

KrishnaV
Community Champion
Community Champion

Hi @Mike_N ,

 

Not sure what is the issue you see I see it is working seamlessly and see the attached output.

 

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

@KrishnaV , so my logic... the logic I hoped would work is this:

If "My Records" is selected in the first gallery, the second gallery is filtered to include just the records of the current user.  From there I can search the 3 columns based on user input.  

 

If "All Records" is selected in the first gallery, the second gallery shows all the records and from there I can search the 3 columns based on user input.  

 

The way it is now (based on your input) when I select "My Records" it filters correctly (shows just my records) but when I type in my search box (TextSearchBox), nothing works.  The data set doesn't change.  When I select "All Records" the search works great.  


I hope that makes sense.

KrishnaV
Community Champion
Community Champion

Sure you can do that, now that your issue is resolved would you mind marking my reply as a solution so that the new members of the community will make use of it!


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP, Microsoft Certified Trainer
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

I will most definitely mark it as resolved but your original solution doesn't work as I'm trying to make it work or as I have described I would like it to work.  If there is a way to make my logic above work, what is it?

 

Thanks

I fixed the issue.  I needed to add the StartsWith statements to the first Filter.  Complete code below.  Thanks for your help @KrishnaV 

 

SortByColumns(
    If(
        GalleryFilter.Selected.Value = "My Records",
           Filter('EMS STAT (AAR)','Created By'.Email = varUser.Email,StartsWith('Call-out Location',TextSearchBox.Text) || StartsWith('Substantive Case File Number',TextSearchBox.Text) || StartsWith('Bureau Name'.DisplayName,TextSearchBox.Text)),                       
        Filter('EMS STAT (AAR)',StartsWith('Call-out Location',TextSearchBox.Text) ||
StartsWith('Substantive Case File Number',TextSearchBox.Text) || 
StartsWith('Bureau Name'.DisplayName,TextSearchBox.Text)
        )
    ),

"Start_x0020_Date", 
    If(
        SortDescending1,
        Ascending,
        Descending
    )
)

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (4,341)