Filter with multiple words on multiple columns

(1) Share

Report

Posted on by Desbrina

I'm struggling to get my head round how to setup a filter on multiple columns, that can check for multiple words.

I have a text input for a search box, and a gallery for the items.

The items is currently set using

SortByColumns(
 Filter(
 _ClubInfo,
 SearchBox.Text = Blank() || SearchBox.Text in Name || SearchBox.Text in Prefix || SearchBox.Text in Site
 ),
 "Name",
 SortOrder.Ascending
)

This works fine for if I'm searching for exact matches, but I want to be able to search by shorter versions of the site names.

So if I enter "man" I will get back both Manchester North, and Manchester Trafford, but if I enter "man n" I should only get back Manchester Trafford

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Sort by

poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@Desbrina

You can try to modify your formula to use the Search function instead of using the in operator, since the Search function allows partial text matching. Here is an updated version of your formula:

SortByColumns( Filter( _ClubInfo, IsBlank(SearchBox.Text) || Search(SearchBox.Text, Name) || Search(SearchBox.Text, Prefix) || Search(SearchBox.Text, Site) ), "Name", SortOrder.Ascending )

The Search function should return all the items where SearchBox.Text is a substring of Name, Prefix, or Site which should give you results when you type in a partial name.

If you prefer to see what something starts with, use StartsWith function to check if the Name or Prefix or Site starts with the string entered in the SearchBox.Text. Here's an example of how you can implement this:

SortByColumns( Filter( _ClubInfo, IsBlank(SearchBox.Text) || StartsWith(Name, SearchBox.Text) || StartsWith(Prefix, SearchBox.Text) || StartsWith(Site, SearchBox.Text) ), "Name", SortOrder.Ascending )

This formula should return all items where the Name, Prefix, or Site starts with the text entered in the search box, which would allow you to search by shorter versions of the site names.

Also, partial matching of strings within columns like "in", even using Search or StartsWith, etc. are not delegable if using SharePoint List and not Dataverse, which means it would only process the first X items, X being the data row limit. The default data row limit is 500. If you want, you may raise it to the max of 2000.

I want to make a note of the following.

Even with a delegable data source like dataverse, suppose you raise it to 2,000 and you have a data source with 20,000 records. You don't suddenly get full reign to return unlimited records. Without delegation, only the first 2,000 records are processed and filtered for your criteria. With delegation, the data source tries to process all the 20,000 records if it's delegable, however, if there are more than 2,000 records matching your criteria (suppose the 2,000th record that matches is your 17,025th record for instance), you won't get any further than 2,000. So if record # 17085 also matched the criteria and would have been the 2001th matching record, this record won't be included in your canvas app . This is because even if you have delegation, each Power Fx function does not return more than that data row limit.

So for example, if you use a Filter function, that's limited to 2,000 even if it's fully delegable. ClearCollect is limited to 2,000. And so forth.
You may be able to have a collection that's more than 2,000 records by calling Collect on it in batches. However for app performance considerations, I don't recommend keeping more than 2,000 records in memory, with even 2,000 being on the quite high side or even being too high sometimes depending on the kind of data.

For Canvas Apps in particular it's important to design your app in such a way that you use multiple Filters and intelligent design to bring your initial fetch of records below 2,000 records if and where possible.

See if it helps @Desbrina

Was this reply helpful? Yes No
Desbrina 12 on at

Like
a
(1)

Report
Copy link

Link copied!

Thasnks, I'll give that a try out shortly

Would that solution work with any number of words or is it just two?

I don't need to worry much about rows currently, its less that 140 at the moment, and can't see it going above 500 any time soon

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Desbrina

Go ahead and try using Search to use any partial matching of a string. See if it works. Try it with one word, then try it with a match of two words, then three, etc. See if it works first with one and work from there. If it does not work with more than one word I may see if I could give you another solution for it.

Was this reply helpful? Yes No
CeriT 4 on at

Like
a
(1)

Report
Copy link

Link copied!

..

Was this reply helpful? Yes No
Desbrina 12 on at

Like
a
(1)

Report
Copy link

Link copied!

Its only working with a single match, so "man" brings up both results, but "man n" brings nothing

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@CeriT @Desbrina
Yes that's what I thought, please note that the Search function won't work as expected when there are multiple words in the search input. It treats the space as an "And" operator. So if you type "man n", it would look for records where both "man" and "n" are found in the columns. This is not your desired behavior.

Try this:

Create a collection that contains each word from the search input. This can be done by splitting the search text from SearchBox.Text:

ClearCollect( SearchWords, Split(SearchBox.Text, " ") )

Next, use ForAll to go through each word in the SearchWords collection and filter the _ClubInfo.

Since ForAll returns a Table, the results of ForAll can be collected directly using one single ClearCollect (rather than a common but inefficient approach of putting Collect inside the ForAll),
and we can use AddColumns to append the SharePoint list item's ID to each record:

ClearCollect( FilteredClubInfo, ForAll( SearchWords, AddColumns( Filter( _ClubInfo, Result in Name || Result in Prefix || Result in Site ), "ItemId", ID ) ) )

Note that "ItemId" is a new column we're adding that contains the SharePoint list item's ID.

Now we can have a collection FilteredClubInfo that could contain duplicate entries.

We can use Distinct to remove duplicates (i.e. matches that were returned more than once, since multiple words may be contained in on or more of the same records) based on the SharePoint list item's ID assuming your data source is a SharePoint List:

ClearCollect( FinalClubInfo, Distinct(FilteredClubInfo, ItemId) )

Now, FinalClubInfo contains the filtered list without duplicates, which you can use to populate your Gallery:
In the Items property of your Gallery use the following formula:

FinalClubInfo

Remember, this solution might face delegation issues if _ClubInfo is too large, because of the in operator.

You mentioned you're not worried about delegation or data row limit at this time as you are operating on a total of well below 500 records right now, so you should be fine.

If you can raise the data row limit to max of 2,000 later, you'll be fine even with the delegation issue if you have 2000 total records or less. And remember even with delegation, you should make sure your records returned by a matching a single function call, even a fully delegable Filter function for example, is still below 2,000 records even if the Power Fx function (and operators you are using) is delegable to the data source!

See if it helps @CeriT @Desbrina

Was this reply helpful? Yes No