cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ScottyTarvan
Frequent Visitor

How do I filter a list with a lookup column?

Hi Guys, 

I have a lookup list of items called Funds - each item has a Code and Name. I also have another list of items that represent the Funds that have been assigned to specific Company - CompanyFunds - each item has a Name and a lookup column named Fund (ignore Company for now).

 

When a user assigns a fund to a company I need to exclude the fund from the funds list on the UI.

 

Fund             CompanyFund
A                    B

B

C

When I show the Fund list how do I exclude fund B?

I have tried: Filter(Funds, !(Name in CompanyFunds.Fund.Name)) but it does not work. Any help appreciated.


2 REPLIES 2
rubin_boer
Super User
Super User

hi @ScottyTarvan 

 

with the list being

Fund {Name, fund} and CompanyFund (Name, fund}

one would expect Filter(Fund,!(Name in CompanyFunds.Name)) assuming the relationship is referenced by name.

or Filter(Fund,!(Name in CompanyFunds.Fund) assuming the relationship is referenced Fund.name to CompanyFunds.fund.

 

Hope it helps

R

 

Thanks for your reply but both suggestions don't work.

The relationship between Fund and CompanyFund is a Lookup column Fund on Table CompanyFund that points back to table Fund. I'm assuming that Dataverse creates a primary key on table Fund and then stores that key in CompanyFund.Fund. When creating the filter expression we don't have access to the actual primary and foreign keys and so are forced to join on another property like Name. I have no idea how to get it to create this "LEFT OUTER JOIN" query.


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,282)