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
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

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

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,746)