How to Filter LookUp Column in Power Apps

[Anonymous]

I need to filter a list with lookup column,

Brands(id, name, category(lookup column))

Category(id, name)

i'm trying to filter Brands List for given category Id, And i tried different ways,

Filter(brandsList,(drop_Category.Selected.ID) in CategoryId.Value)

Filter(brandsList, CategoryId.Value = (drop_Category.Selected.ID))

Filter(brandsList, 'Category: Id'= (drop_Category.Selected.ID))

And sometimes it shows error on Filter function '  Expected Text Value '  or Delegation warning. And none of'em seems to work as expected. 

Any help here ?

[mgreiner79]

Do you have a link to the 'one degree of separation concept'? I have not heard of it before.

[WarrenBelz]

Hi @mgreiner79 ,

It was a pun at the movie "six degrees of separation" - sorry for the aussie humour. I am simply saying do not attempt relational or "look through" queries (connecting to a third table in the same filter) as SharePoint will not accept delegation on them.

[Philipp71]

Hey Warren, 

thanks for the great tip not to create relationships in tables for PowerApps, but to set the lookup in the functions within the app. This totally helps to build simple table relationships in the app! 

[Gianluca]

I did it both ways in the past and wasn't happy with any of it.

Not using lookup columns means to recreate all the relations everywhere, not only in the app! Also in every single Power Automate Flow you build and in all Dataflows as well. Very repetitive and error prone work.

By using lookup columns you can't filter on more than 2'000 records, which i a bummer as well.

What we need is the ability to use the virtual columns derived from lookup columns for filtering in the app. Can't be so difficult, Microsoft?!

[WarrenBelz]

HI @Gianluca ,

Possibly pay for a Premium Licence and use Azure SQL . . . if you use SharePoint, you need to largely "unlearn" the concept of relationships at a database level and simply do them in Power Apps.

[darylvogan]

Can you clarify one thing for me? Do you store the ID as a number and do a lookup function, or do you just store the lookup value's text?

[PFossil]

The LookUp column is necessary because it can be indexed, so no one can remove information critical to relationships from the lists. This is not possible with purely numeric fields. That is, if your application has a lot of relations, use LookUp type column.

[WarrenBelz]

@PFossil ,

If SharePoint is your data source, sorry but I totally disagree with that - I have never used (or needed to use) Lookup columns with Power Apps.

[PFossil]

However, that doesn't mean that no one else uses them, does it? I personally use them a lot, because SharePoint gives the possibility to protect lists containing relationships, that is, no one can dispose of data that breaks relationships. Of course, they are a bit more laborious to code, but the benefits outweigh the disadvantages. And not everyone has the opportunity to use a database, in which case lists are a good alternative.

[WarrenBelz]

@PFossil ,

You are correct they are laborious to code - this is an understatement and I regularly answer posts from confused users on this. SharePoint is not a relational database, so any "relationships" created at that level other than the basic choices list (which you can easily get directly in Power Apps by simply addressing the other list) are not accessible in a Delegable manner (so why bother). I have not stuck the "disposal of data" problem and you are actually the first person who has raised this as a reason for using them.