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...
Solved! Go to Solution.
@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.
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.
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.