cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Martin_W
Helper II
Helper II

Compounded filter in Dataverse

Hi,

 

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...

 

thanks,

 

Martin

1 ACCEPTED SOLUTION

Accepted Solutions

@Martin_W 

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

View solution in original post

5 REPLIES 5
CNT
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. 

Martin_W
Helper II
Helper II

Martin_W_0-1657477487795.png

Does that help?

Martin_W
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.

Martin_W
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.

 

 

@Martin_W 

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

Helpful resources

Announcements
October Events

Mark Your Calendars

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

Ignite 2022

WHAT’S NEXT AT MICROSOFT 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)