Answered

Search by a column having multiple values

(1) Share

Report

Posted on by BharathSena

I have a PowerApps application for which the backend source is SharePoint. I have a requirement where I need to search by the table tables. So, I used the formula -

Sort(Filter(
Table_name, inputSearch_3.Text in ColumnA
|| inputSearch_3.Text in ColumnB
)
,ColumnA,SortOrder.Ascending)

Now I got a new requirement where I need to search by another column too but that column columnC has multiple values which is the lookup for another table (Table_name1, column ColumnD) having single values.

Can you please modify the above query and provide me a query which can search by columnC also.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

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

Like
a
(1)

Report
Copy link

Link copied!
@BharathSena - you can try the non-delegable method below:

Sort( Search( AddColumns( Table_name, "MyMultiChoiceLookUp", Concat( 'Your multi-choice lookup field', Value & "," ) ), inputSearch_3.Text, "ColumnA", "ColumnB", "MyMultiChoiceLookUp" ), ColumnA, SortOrder.Ascending )

Was this reply helpful? Yes No
BharathSena 14 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks but where did you use columnC, second table Tablename2 and its column columnD?

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

Like
a
(0)

Report
Copy link

Link copied!

@BharathSena - replace "Your multi choice lookup field" in the above code with the name of Column C.

Was this reply helpful? Yes No
BharathSena 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks can you please tell what is "myMultichoiceLookup" and value

Was this reply helpful? Yes No
Verified answer

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

Like
a
(0)

Report
Copy link

Link copied!

@BharathSena - it is not possible to use the Search or In function on complex field types (such as Choice, Lookup, People).

Instead we use the AddColumns function to bring a "Virtual" column into our table. An equivalent of this is using a Vlookup in Excel - you're bringing in a new column with data that does not actually exist at source.

In this example, we have created a column called "MyMultiChoiceLookUp" which returns the data from your multi-choice lookup column in text format.

We then run a Search against this column to return the results we need.

Note that "MyMultiChoiceLookUp" is entirely made up name, and it can be anything you want.

Also note that using the AddColumns function this will modify your Gallery schema, and could impact any variables you might be running against the Gallery.

Further reading:

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-table-shaping

Was this reply helpful? Yes No
BharathSena 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you so much

Was this reply helpful? Yes No