I have 3 tables, Clients (Grandparent), Sites & BUs (Parent), and Cost Savings Initiatives (Children.) Each Client has many Sites & BUs, each Site & BU has many Cost Savings Initiatives. Currently I have a role of Key Account Manager which is defined as a user who is tagged as a KAM within each Site & BU. They are restricted to view/edit only Sites & BUs and related records for which they are listed as a KAM.
I have been asked to bump up their view ability to all Sites & BUs related to the Client, but still only edit/add records for one in which they are listed as a KAM.
My initial thought was to pull a summary field into the Clients table to make a master list of all KAMs associated with the related Sites or BUs, but it's not possible to create a summary field like this.
My next thought was create the same type of listing within the Sites & BUs table, although I'm not sure where to start on a formula that would more less conditionally create a list based on Client name. Heck, even a checkbox.
The simple thing to do would be make every KAM a KAM for every Site or BU, but that's neither clean or in line with how we need to govern add/edit access. Any ideas on a formula I could create to say If the current user is in a listing of KAMs with the same Client?