Restricted user access/related record question
I am currently in the process of merging approximately 30 individual databases into a single database. The organization that hired me had originally assigned an individual database to each of their clients in order to protect their sensitive information. They have, however, discovered the many drawbacks to this approach and now want to consolidate them and protect/restrict the data through privilege sets.
I have the new database largely ready to go and have started doing test imports, but I’m running into a problem that I’m having trouble solving. The database keeps track of art collectors (the clients) and their collections (inventory). I created a field in both the Client table and the Inventory table called Record Owner that corresponds to the client’s account name, and I have set up a script that runs when they log in to search for those records. This is working perfectly. But we have three other tables - all related to the inventory table - called Artist, Gallery, and Service Provider. Each artwork was (obviously) made by an artist, purchased from a gallery or auction house, and will be subjected to various services (crating, transportation, installing, etc). We want to also limit the records that each client sees to those that are related to their particular collection. So while some clients may have artworks by the same artist, other clients may have the only instance of an artist in the database. We don’t want clients to see artists that aren’t in their collection. The initial Artist layout is a list, so we want the list to display only artists in the given client’s collection. The same goes for the Gallery and Service provider tables/list layouts.
I created join tables between Inventory and Artist, Inventory and Gallery, and Inventory and Service Provider that ties together the artwork to the other table and adds a Record Owner field. As of yet I have been unable to figure out how to display the related Artist, Gallery, and Service Provider related records via the list. I’m wondering if I’ve added unnecessary complications through these joins - if there is some way to limit the records without them. Or maybe I'm on the right track but need to get my scripts working, because they're not right now. I am including some screenshots to help illustrate my question. Any help would be much appreciated. I’m in over my head!