Constructing a 'Search' screen

tomburton2023

Hi

I have a PowerApp with a SharePoint list as it's data source. Our end user wants a search screen where they can search for items/records based on one or more criteria. The user interface looks like the below:

The end user will want to be able to specify one or more search criteria (this could differ each time), and then get the results in a Gallery below. I've set up a Collection for this, and have started working on the 'OnSelect' expression for the Search button, see below:

If(AuthorEditorSelection.Selected.Value=Blank() And LocationSelection.Selected.Value=Blank() And CurrentEditionSelection.Selected.Value=Blank() And FormatSelection.Selected.Value=Blank() And ClassMarkSelection.Selected.Value=Blank() And ISBNNumberSelection.Selected.Value=Blank() And SubscriptionStartDateFromSelection.SelectedDate=Blank() And SubscriptionStartDateToSelection.SelectedDate=Blank() And SubscriptionEndDateFromSelection.SelectedDate=Blank() And SubscriptionEndDateToSelection.SelectedDate=Blank() And PublicationNotesInput.Text="",ClearCollect(LibraryItems,Filter('Library System (Test)',KeywordsInput.Text in Keywords)),

If(AuthorEditorSelection.Selected.Value=Blank() And LocationSelection.Selected.Value=Blank() And CurrentEditionSelection.Selected.Value=Blank() And FormatSelection.Selected.Value=Blank() And ClassMarkSelection.Selected.Value=Blank() And ISBNNumberSelection.Selected.Value=Blank() And SubscriptionStartDateFromSelection.SelectedDate=Blank() And SubscriptionStartDateToSelection.SelectedDate=Blank() And SubscriptionEndDateFromSelection.SelectedDate=Blank() And SubscriptionEndDateToSelection.SelectedDate=Blank() And KeywordsInput.Text="",ClearCollect(LibraryItems,Filter('Library System (Test)',PublicationNotesInput.Text in Notes))))

I've quickly realised that the expression for the 'Search' button is going to get extremely messy and convoluted, because I have to allow for every single possibility where one search field/criteria is left empty.

Note that, to allow the user to select from an existing value for the drop-downs, I've set the expression to:

Distinct('Library System (Test)','Author/Editor')

To allow the user to leave a specific search criterion blank, I realised I couldn't add a 'Blank()' to the above so I've added a 'dummy' empty record to the SP list data source to allow for this - see below:

However, I'm struggling with the 'Search' expression. If I simply set the search to pick up a search criterion 'OR' another search criterion, it returns everything, because there is likely to be one empty value (in other words, it's very unlikely that the user will set all of the search criteria every time).

I've done a quick and dirty calculation in my head that, in order to take care of every permutation for the search, I'd need '12 x 12' (so 144 in total) 'If/Else' statements in the search expression.

Surely, there must be a better way of achieving this / I'm missing something obvious?

Any help/advice greatly appreciated.

Thanks, Tom

AARON_C

Hi @tomburton2023,

Are all the fields in the search page coming from the same sharepoint list?

tomburton2023

Hi @AARON_C

Yes indeed, they are.

Thanks

AARON_C

@tomburton2023

Do you need to create a collection for this? Can you not just filter the list from what is selected from each control?

Search(
    Filter(
        'Sharepoint List',
        (Column1 = Dropdown1.Selected.Value || Dropdown1.Selected.Value = Blank()),
        (Column2 = Dropdown2.Selected.Value || Dropdown2.Selected.Value = Blank()),
        (Column3 >= DateFrom.SelectedDate && Column3 <= DateTo.SelectedDate || DateFrom.SelectedDate = Blank() && DateTo.SelectedDate = Blank()),
    ), 
    TextInput.Text, Column4
)

tomburton2023

Thanks @AARON_C , I'll give this a try.

tomburton2023

Hi @AARON_C

In your sample code above, I get the column parts, I'd change those to my specific column names and add the additional ones required. However, where you've put 'TextInput.Text' and 'Column4', what are those mapping to? There is no search text per se, I just want the user to be able to have the gallery (results) filtered based on the chosen search options.

Thanks