Answered

Filter by multiple words in addition to drop down filters

(0) Share

Report

Posted on by DaleZ

This is how I have my gallery set up right now:

If(CollateralTypeDrop.Selected.Value="All",
Filter(ContentIndex,ContentDesSearch.Text in 'Content Description'),
Filter(ContentIndex,And(ContentDesSearch.Text in 'Content Description', CollateralTypeDrop.Selected.Value in 'Collateral type'.Value)))

Screen Shot 2022-02-02 at 1.43.04 PM.png

(ignore the dropdown input on the right for now, its not live yet)

So far I can filter nicely using one word in the text input (ContentDesSearch.Text), and combine that with a filter in the dropdown (CollateralTypeDrop)

Need to be able to use multiple words in the text input though. Haven't been able to figure this out using other answers found here.

Any tailored help would be massively appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
@DaleZ

Please consider changing your Formula to the following:

Filter(ContentIndex, (CollateralTypeDrop.Selected.Value="All" || CollateralTypeDrop.Selected.Value in 'Collateral type'.Value) && (Sum(ForAll(Split(ContentDesSearch.Text, " "), {Value: If(Result in 'Content Description', 1, 0)}), Value)>0) )

The Value column naming is redundant, but it is there to emphasize that ForAll is a function that returns a table and not some sort of ForLoop function.

This will split your input and evaluate each word against the 'Content Description'.

Avoid specifying multiple filter function with If statements in your formulas to make them easier to maintain.

I hope this is helpful for you.

Was this reply helpful? Yes No
DaleZ 34 on at

Like
a
(0)

Report
Copy link

Link copied!

It's not quite doing what I'd hoped actually. It's now filtering by both words, but if a result contains either of the words it is being shown. Really needs to be both words need to appear in every result. So for example:

if i type in 'jet video', it's returning anything with either 'jet' or 'video'. I need it to show only results that contain both (not necessarily consecutively though like 'jet video'. It should still return any items that contains those words anywhere - such as something like 'jet has a video')

Is that an easy tweak?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@DaleZ

That is much different than what you originally posted.

If you need to be able to find all words though, then just adjust the formula to account for it.

With({_splits: Split(ContentDesSearch.Text, " ")}, Filter(ContentIndex, (CollateralTypeDrop.Selected.Value="All" || CollateralTypeDrop.Selected.Value in 'Collateral type'.Value) && (Sum(ForAll(_splits, {Value: If(Result in 'Content Description', 1, 0)}), Value) = CountRows(_splits)) ) )

This will return results where the count of entered words equals the count of found words.

Was this reply helpful? Yes No
DaleZ 34 on at

Like
a
(0)

Report
Copy link

Link copied!

"That is much different than what you originally posted."
Yeh you're right, sorry. Thank you for the solution. I'm learning a lot from breaking it down. Hopefully others can find this useful too.

Is there a way to make the gallery filter across the collateral type column too using the same text input? i.e. if someone types in 'video', but the word video isn't in the description, the gallery still returns everything that's a video collateral type.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@DaleZ

No problem!

Yes, you can alter your filter however you like. The trickier you get, the trickier the formula gets!

Example:

With({_splits: Split(ContentDesSearch.Text, " ")}, Filter(ContentIndex, ( (CollateralTypeDrop.Selected.Value="All" || CollateralTypeDrop.Selected.Value in 'Collateral type'.Value) && (Sum(ForAll(_splits, {Value: If(Result in 'Content Description', 1, 0)}), Value) = CountRows(_splits)) ) || ("Video" in _splits.Result && 'Collateral type'.Value = "Video") ) )

That would return all videos if they type "video" in the search.

Was this reply helpful? Yes No
DaleZ 34 on at

Like
a
(0)

Report
Copy link

Link copied!

hmm... I can't get this one to work. I probably described it poorly again, sorry!

I don't think the word video should be specifically mentioned in the formula. I just meant that as an example. I need it to check if any of the words entered into the text input appear in the Content Description column (as it currently does successfully) OR the Collateral type dropdown. If one or more of the entered words appears in either of those, then show it.

Hope that makes sense!

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@DaleZ

It was just an example anyway...

So based on what you are stating, then this should get you close to it:

With({_splits: Split(ContentDesSearch.Text, " ")}, Filter(ContentIndex, (CollateralTypeDrop.Selected.Value="All" || CollateralTypeDrop.Selected.Value in 'Collateral type'.Value) && ( (Sum(ForAll(_splits, {Value: If(Result in 'Content Description', 1, 0)}), Value) = CountRows(_splits)) || Sum(ForAll(_splits, {Value: If(Result in 'Collateral type', 1, 0)}), Value) > 0 ) ) )

Was this reply helpful? Yes No
DaleZ 34 on at

Like
a
(0)

Report
Copy link

Link copied!
With({_splits: Split(ContentDesSearch.Text, " ")}, Filter(ContentIndex, (CollateralTypeDrop.Selected.Value="–" || CollateralTypeDrop.Selected.Value in 'Collateral type'.Value) && (TechDrop.Selected.Value="–" || TechDrop.Selected.Value in Tech.Value) && ( (Sum(ForAll(_splits, {Value: If(Result in 'Content Description', 1, 0)}), Value) = CountRows(_splits)) || Sum(ForAll(_splits, {Value: If(Result in 'Collateral type', 1, 0)}), Value) > 0 || Sum(ForAll(_splits, {Value: If(Result in 'Tech', 1, 0)}), Value) > 0 ) ) )

I have it like this (added a filter of the Tech column and "All" is now "–")

but trying it out with the word 'video', it's not returning all with the Collateral type Video and I cant understand why?

Was this reply helpful? Yes No