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

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 II
Helper II


Does that help?

Helper II
Helper II

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 II
Helper II

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

October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022


Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (3,875)