Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Compounded filter in Dataverse



In my app, I have a Dataverse table with client info. Because one client can own more than one site, there is another table of Sites, which has a column specified as the owner which is a lookup from the first table.


Each site has a number of items of inventory, and each item of inventory is in a third table, with a lookup column to the site. That made sense to me when I designed the app!


Now here's the issue....


The Inventory table has inventory from all clients in it. Obviously I need clients to only be able to see their own inventory not anyone else's. Supposing that my whole model isn't wrong.....!


I've created a User table also, this assigns an Access type (Admin, Owner etc) alongside each email address which has access, as well as a lookup to the client data to give me the client associated with the user


To filter the inventory list, I need to first return all the sites that the client owns, and then return all the inventory belonging to that client. The issue is that I don't think I can do what appears to be a compound lookup/filter.


Does anyone have any ideas? I could always add another column in the Inventory table for the Client name as well as the site? I was hoping to be able to do it more elegantly than that...






Accepted Solutions


Filter(tableInventory, 'Inventory Site'.SiteName in (Filter(tableSites, tableClient.'Corporate Name' = varUserClient).SiteName))

View solution in original post

Super User
Super User

@Martin_W It doesn't sound impossible. If you could you share some screeshots of the data definition of these tables, I could then give you the formula to get the data. 

Helper III
Helper III


Does that help?

Helper III
Helper III

So think of the Client table as storing Supermarket companies; Head Office details.


The Site table would contain the address of each actual physical supermarket. The Site record contains a field which is a LookUp (in Dataverse) to the Head Office record.


The Inventory table contains everything in stock at each site. Each product (e.g. a record in the inventory table) has a field which is a Dataverse Lookup to the Site.


Each user account (at the App level) has a field indicating which client they are working for. This is also a Dataverse lookup to the Client table.


So, when I display the Inventory table, I need to be able to work back from the site that owns the item to the Client that owns the site, compare that to the User's Client field, and only display the inventory that is owned by that client, irrespective of site.

Helper III
Helper III

Essentially, what I am trying to do is this?


Filter(tableInventory, 'Inventory Site'.SiteName = (Filter(tableSites, tableClient.'Corporate Name' = varUserClient).SiteName))


I can't use the Search function as that needs text whereas I'm looking in a column that contains lookup values.


but this fails because I'm trying to compare a string with a table.




Filter(tableInventory, 'Inventory Site'.SiteName in (Filter(tableSites, tableClient.'Corporate Name' = varUserClient).SiteName))

Helpful resources

Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,330)