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
