Answered

Search on lookup multiple selection column for gallery

(2) Share

Report

Posted on by MicaBassi

Hello!
I'm opening this post to see if someone could help me with the challenge I'm facing in PowerApps.
I have a Canvas app with a horizontal gallery, that's connected to a Sharepoint list. On the items property of that gallery, I have the following code:

Search(

Filter(
myDataSource,
Category.Value = ThisItem.CategoryOption
),

SEARCHBOX.Text,
"field_21", "field_22")

So far, this works and I can use the searchbox (text input control) to filter my gallery, based on searching what I write on the searchbox on field_21 and field_22.

I also need to search on field_23, but field_23 is a lookup column in sharepoint that allows multiple values. If I simply add "field_23", I get the following error: "The function Search has invalid arguments. Wrong column type. Expects text type". I assume this is because this multiple selection column returns an array.

Do you have any ideas on how I could achieve this, in order to search for text within the different values of field_23?

Let me know if you need more details.
Thank you in advance 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Ami K 15,687 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!
@MicaBassi -

It is not clear why you have "ThisItem.CategoryOption" inside the Filter (is this a nested Gallery?), but nevertheless, you can use the AddColumns function to create a "virtual" text column of the LookUp value.

Search( AddColumns( Filter( myDataSource, Category.Value = ThisItem.CategoryOption ), "_text_lookUp", Concat( field_23, Value, ";" ) ), SEARCHBOX.Text, "field_21", "field_22", "_text_lookUp" )

However, it is worth noting that the AddColumns and Search functions are not delegable. You can mitigate this somewhat using the With function:

With( { _prefiltered_data: Filter( myDataSource, Category.Value = ThisItem.CategoryOption ) }, Search( AddColumns( _prefiltered_data, "_text_lookUp", Concat( field_23, Value, ";" ) ), SEARCHBOX.Text, "field_21", "field_22", "_text_lookUp" ) )

If you can be certain that filtering on the Category field will return less than 2000 rows, you can then run the non-delegable functions on that smaller subset of data.

Was this reply helpful? Yes No
jpullins1 on at

Like (0)

Report
Copy link

Link copied!

Hi Mica, you should be able to concatenate the field and use that text to search?

Search( AddColumns("Your Data Source", "Searchfield_23", Concat( ThisRecord.field_23, Name, ", " ) ), TextInputCanvas1.Value, "Searchfield_23" )

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

Let me know if my answer helped solving your issue.
If it did please accept as solution and give it a thumbs up so we can help others in the community.

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@jpullins1 you're missing a data source in your formula....

Was this reply helpful? Yes No
jpullins1 on at

Like (1)

Report
Copy link

Link copied!

Thank you Amik, pretty sure your solution is exactly what I was saying yet much more eloquently.

Was this reply helpful? Yes No
MicaBassi on at

Like (0)

Report
Copy link

Link copied!

Hi @Amik thanks for the solution, this worked! It is indeed a nested gallery. If possible I would like to ask you another question. For this same gallery, I have a dropdown control to filter the inner gallery by a column that is also a lookup column that allows multiple selection. So, if on the dropdown I selected Option1, I want to filter the gallery items by those that have Option1 within the array/lookup column named Vertical. The issue is that if I simply do "Dropdown.Selected.Value in Vertical", I get delegation error and the gallery "breaks". Any insights for these type of cases?

Once again, thanks!!

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

@MicaBassi - sorry I would need more detail because I cannot visualise what you're trying to do.

I would suggest raising a new post with that question in the main forum.

Was this reply helpful? Yes No