Searching filtered data

[Mike_N]

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
    )
)

[KrishnaV]

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
    )
)

[Mike_N]

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]

Hi @Mike_N ,

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

[Mike_N]

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

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!

[Mike_N]

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

[Mike_N · Accepted solution]

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
    )
)