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 ?

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

Other than firstly suggest you maybe should consider why you are using Lookup columns (they are not the best friends of Power Apps) - this should work (I assume you are using SharePoint here - you did not specify)

Filter(
   brandsList,
   Category.Value = drop_Category.Selected.Value
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[WarrenBelz]

Hi @Anonymous ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Anonymous]

Yes..... Thanks for the help. I did removed the Lookup columns from Sharepoint list and used two lists instead. That's the easy way.

[mgreiner79]

Hi @WarrenBelz 

I agree that lookups in PowerApps is quite painful, but what is the alternative (assuming the data source is SharePoint list)? Is there some other, better way to store relationships between list items in SharePoint that plays better with PowerApps?

Cheers,

Mark

[WarrenBelz]

Hi @mgreiner79 ,

You do not need to "store" relationships in Power Apps - you can access both lists directly in Power Apps and simply do the Lookup there and store all any data in Text fields. No complications reading from or (in particular) writing to and totally maximising Delegation. I simply do not (or ever have needed to) use this field type.

[mgreiner79]

So does that mean, when you have related items, you simply store the foreign key (like the ID of the related item) in a text column?

For example, if we have a list of people, and a list of addresses, the 'people' list would have a column 'addressId' strong the string or integer value of the related 'address'.
Is this how you would do it?

[WarrenBelz]

Hi @mgreiner79 ,

You need to stop "thinking SQL" with your data structure and also practise "one degree of separation" and possibly some denormalised data. But to your question, as long as your "key" column is Single Line of Text or Numeric, then you will have Delegable queries across any two lists with this link.

[mgreiner79]

Regardless of relational or non-relational data model, I still want to store some sort of hierarchy of information. In a document DB, you could do this by nesting items (which works well for JSON). In relational DB's you could use relationships. Or for some relational DB's you could also store the JSON data type. 
But in SharePoint lists, I don't see how one could do this. There is no JSON data type in a Sharepoint list. I guess I could store JSON formatted text in a large text field, but I imagine it could get pretty ugly trying to parse that in PowerApps or MS Flow.

[WarrenBelz]

@mgreiner79 ,
As I said, you need to stop "thinking SQL" - I guess we will need to agree to disagree, but I have written some complex (up to 40) list apps in SharePoint, some with of thousands of records with nothing more than Text or numeric fields linking lists in a "one degree of separation". The main point is that all the queries can be done by Power Apps by simply addressing the connected lists, so there is no need to "store relationships" at data source level (my previous weapon of choice was Access, so it took some "unlearning" for me too).