PowerApps lookup filter based on multiple choice column in another list

[Anonymous]

Hello! Please help me with syntax. I have ListA with lookup field Company_name and List B with fields Title (text), Type(multiple choice)

Column Type could have different variants: Type1, Type2, Type3.

I need to filter lookup field in ListA so to show only such companies from ListB that include Type=Type1.

Is it possible to do?

[yashag2255]

Hi @Anonymous 

Can you try out the below expression:

Filter(ListA, LookUp(ListB, ID = Company_Name.Id).Type.Value = "Type1")

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Hi @yashag2255 

Thank you for helping.

I did try this expression

Filter(ListA, LookUp(ListB, ID = Company_Name.Id).Type.Value = "Type1")

Originally it looks like:

Filter('InOut documents'; LookUp(Counterparties; ID = 'Company Name'.Id).Type.Value = "ADF LLC")

Column Type in the list Counterparties might has not only one record, but multiple.

For example: ADF LLC, buyer, suppplier

I need to filter it according to the name 'ADF LLC', so i think i need somehow to check Type that it includes value 'ADF LLC'

and there are the following problems:

1) It says I can't use ',' only ';' to separate so I tried like this:

Filter('InOut documents'; LookUp(Counterparties; ID = 'Company Name'.Id).Type.Value = "ADF LLC")

2) When I tried using ';' now it says "Delegation warning: the highlighted part of Filter of this formula might not work correctly on large data sets "

3) What is the syntax to use space in the name of list. For example: Company Name. Should I write it as 'Company Nme' or ['Company Name']?

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

Do you want to filter listA based on type value in listB?

1)If so, try this formula:

Filter('InOut documents';  'Company Name'.Id in Filter(Counterparties,"ADF LLC" in Type.Value).ID)

"Filter(Counterparties,"ADF LLC" in Type.Value)" represents the records in list B that have type of "ADF LLC".

Filter(Counterparties,"ADF LLC" in Type.Value).ID represents these records' ID in list B.

'Company Name'.Id are some value of ID in listB.

So you could compare "'Company Name'.Id" with "Filter(Counterparties,"ADF LLC" in Type.Value).ID".

2)The reason why you need to use ";" instead of "," is because of region.

In some regions, ";" represents ",", ";;" represents ";"

3)Delegation is powerapps limit.

The reason why you have this warning is that you use delegate function but not delegate functions.

Could you tell me whether your data in these two lists larger than 2000?

If not, you just need to change this limit to 2000. Then this warning will not make your app perform bad.

 If yes, since the feather that you want need "in", but "in" is non-delegate, so I suggest you try collection.

Save these two lists data to collection. In collection, there's no delegation limit.

For example:

ClearCollect(collection1; listA);

ClearCollect(collection2;listB)

Then use these two collections in your app.

Here's a doc about delegation for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

4)If you have spaces between fieldname, you need to use fieldname like this:

'Company Nme'

Best regards,