Search function for Lookup Column

(0) Share

Report

Posted on by calvinck00

I have created 3 tables in Dataverse.

First table is "Location". (columns: IDLocation, Name)

Second table is "Fixed Assets" that has lookup column to the first table (Location Table). (columns: FixedAssetsNumber, Location)

Third table is "Opname" that has lookup column to the second table (Fixed Assets Table). (columns: IDOpname, Fixed Assets)

How can i use search function if i want to filter a gallery that use Opname Table as the datasource by value of TextInput1 that contains Location Name?

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@calvinck00 Try this,

Filter(Opnames, 'Fixed Assets'.'Fixed Assets' in Filter('Fixed Assets', Location.Name=TextInput1.Text).'Fixed Assets')

Was this reply helpful? Yes No
calvinck00 43 on at

Like (0)

Report
Copy link

Link copied!

Sorry @CNT but it still not working 😞
Maybe can you use the Search and AddColumn Function? This multilevel lookup problem is very hard to be solved for me. I'm trying to use this code but it gives me an error "Multiple Level of Many to One Relationship Expansion Aren't Supported".

SortByColumns(
Search(
AddColumns(
Opname,
"AssetNumber",
'Fixed Assets'.'Fixed Assets Number',
"AssetLocationName",
'Fixed Assets'.Location.Name -> ERROR
),
TextSearchBox3.Text,
"AssetNumber",
"AssetLocationName"
),
"AssetNumber"
)

Was this reply helpful? Yes No
Verified answer

CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@calvinck00 It is 'Fixed Assets' and not 'Fixed Assets Number'

Filter(Opnames, 'Fixed Assets'.'Fixed Assets' in
Filter('Fixed Assets', Location.Name=TextInput1.Text).'Fixed Assets'
)

Was this reply helpful? Yes No
calvinck00 43 on at

Like (0)

Report
Copy link

Link copied!

Hi, i have tried, but it still not working

I'm trying using addColumn and Search function like this:
AddColumns(
Opname,
"AssetNumber",
'Fixed Assets'.'Fixed Assets Number',
"AssetLocationName",
'Fixed Assets'.Location.Name -> ERROR
)
ERROR: "Multiple Level of Many to One Relationship Expansion Aren't Supported".

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@calvinck00 your formula will not work as "Multiple Level of Many to One Relationship Expansion Aren't Supported".

What's the error you are getting? It looks like something is wrong with the Name column.

Was this reply helpful? Yes No
calvinck00 43 on at

Like (0)

Report
Copy link

Link copied!

Hi, i think its name problem.
Example in my case:
Table Name Column
Fixed Assets Fixed Assets Number, Location (lookup)
Opname Opname ID, Fixed Assets (lookup)

Name that i type in red color must be different? Because i feel like power apps do not know which "Fixed Assets" (is it the table or the lookup column).

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@calvinck00 You can refer the table as [@'Fixed Assets'] to be specific.

Was this reply helpful? Yes No
calvinck00 43 on at

Like (0)

Report
Copy link

Link copied!

Ok thanks mate! You help me again..

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@calvinck00 Glad to help!

Was this reply helpful? Yes No
carlilelance 353 on at

Like (0)

Report
Copy link

Link copied!

"in" is non-delegable 😞

Was this reply helpful? Yes No