Search function and filter function at the same time for multi-level filtering

[brandonluxe]

Hello,

I am building a photo library and require the use of a search bar to type name of project so that it spits out information. Then I need a secondary drop down menu to further filter the results from search bar for a choice column.

Data source = 'PHOTO LIBRARY - CHINA'

Gallery = galList

Form = formList

Texts fields to search by = Title, 'Item #'

Choice fields to search by = 'Visit Type'

Currently I have the typing search bar working fine and filters correctly.

Item properties for galList = Filter('PHOTO LIBRARY - CHINA', StartsWith(Title, txtSearch.Text) Or StartsWith('Item #', txtSearch.Text))

I've added a combo box to select 'Visit Type' and on Item properties I have = Distinct('PHOTO LIBRARY - CHINA', 'Visit Type'.Value) which pulls up all the different Visit Types.

After getting results from typing in the 'Title' or 'Item #', I would like to use the combo box drop down to further filter which 'Visit Type' it is. Can someone please help me take a look at this? I have spent several hours reading through forums and youtube videos and having difficulty. I am having confusion on how to add so many functions to the Items property on galList to achieve this.

[Amik · Accepted solution]

@brandonluxe ,

Try:

Filter(
    'PHOTO LIBRARY - CHINA',
    Len(txtSearch.Text) = 0 || StartsWith(
        Title,
        txtSearch.Text
    ) || StartsWith(
        'Item #',
        txtSearch.Text
    ),
    Len('Your Combo Box'.Selected.Value) = 0 || 'Visit Type'.Value = 'Your Combo Box'.Selected.Value
)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[brandonluxe]

Hi Imran,

Thank you so much for the quick reply. That has mostly worked. I can type in search bar for an item and then further filter the Visit Type by the drop down combo box. However, as it is a combo box, sometimes for example someone may want to type in search bar "WH-100" and there may be multiple of those but under different Visit Types, so the combo box currently only allows one filter at a time. I would like to be able to select multiple filter options on the combo box which would show/filter multiple Visit Types for the gallery. Is this doable?

Thank you,

Brandon

[brandonluxe]

Hi @Amik , Further elaborating on my response, I tried this formula to achieve multiple filters which works but gives me delegation error.

Filter(
    'PHOTO LIBRARY - CHINA',
    Len(txtSearch.Text) = 0 || StartsWith(
        Title,
        txtSearch.Text
    ) || StartsWith(
        'Item #',
        txtSearch.Text
    ),
    Len(ComboBox1.Selected.Value) = 0 || 'Visit Type'.Value in ComboBox1.SelectedItems
)

 If possible, could you please assist?

Thank you,

Brandon 

[Amik]

Hi @brandonluxe ,

Good to see you figured it out without any help. 

IN is not a delegable function in SharePoint and there is no way around it.

However, the Filter function is delegable with SharePoint.

If you can pre-filter your lists on a specific criteria that will return less than 2,000 rows, you can then perform the IN function on that smaller subset of data. For example we could have population data that has 5,000 rows of people per district, but if we know that each neighbourhood within those districts will return less than 2,000 rows, it is ok to use a non-delegable function within that smaller subset using delegable Filters.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

[brandonluxe]

Hi @Amik ,

Is there any way to modify the formula you provided to be able to search any part of the Item # instead of StartsWith?

For example,

If I have items WH-100 to WH-199, 

I'd like to be able to search "105" and it show the result instead of "WH-105"

Thank you,

Brandon

[Amik]

@brandonluxe use Search:

Search(
    Filter(
        'PHOTO LIBRARY - CHINA',
        Len(ComboBox1.Selected.Value) = 0 ||'Visit Type'.Value in ComboBox1.SelectedItems
    ),
    txtSearch.Text,
    "Title",        //use the logical SharePoint name
    "Another Field" //use the logical SharePoint name
)

Note, the Search function is not delegable.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan