Search function for Lookup Column

[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?

[CNT]

@calvinck00 Try this,

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

[calvinck00]

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"
)

[CNT · Accepted solution]

@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'
)

[calvinck00]

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".

[CNT]

@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. 

[calvinck00]

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).

[CNT]

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

[calvinck00]

Ok thanks mate! You help me again..

[CNT]

@calvinck00 Glad to help!