User List to List with Conditions

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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?

Photo of GB_DirectorOfOne

GB_DirectorOfOne

  • 70 Points

Posted 3 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
How many KAM's do you have?  You might be able to do this with a a combo of role permission and formula type check, checkboxes.
Photo of GB_DirectorOfOne

GB_DirectorOfOne

  • 70 Points
Potentially hundreds globally, although per Client i'm thinking 10-20.
Can you make lookup on the KAM user field down to the cost saving table and then set up a custom rule to allow edit where the KAM user field is equal to the Current User.
Photo of GB_DirectorOfOne

GB_DirectorOfOne

  • 70 Points
I have built that and that is what I use in the KAM role to decide which records they can access. (Worth noting is that each client has multiple KAMs as we have a KAM for each country in which we work with the client.) This works great when they only want to view accounts for which they are KAM, but when they want to see records for which they are not the KAM, but it is the same client is where I'm running into issues. What I think I need is to either concatenate (list) all KAMs associated with the same client, or more ideally be able to create a KAM user summary at the Client table level. But that isn't possible.
Sorry, I did see that when I first read your post but forgot that nuance when I revisited it today.

You can make a summary count field of the number of sites where the KAM is the current user. That's a start, but you will find that you cannot limit access to parents based in that summary  field.

You can filter reports on that field, though, i.e., where [#of sites where current user is KAM] > 0

and you can hide sections of a firm based in that field.

Does that help?
Photo of GB_DirectorOfOne

GB_DirectorOfOne

  • 70 Points
Going to try it... I'll let you know. What I'm thinking is creating the summary like you suggested then relating that to other tables. My concern is reporting. Right now I have sets of reports built by Language and Currency, then they're "auto-filtered" based on the current user's role. Expanding their view on some tables will change what appears on their reports potentially. Great suggestion though! I just wish one of two things: 1st that this type of visibility to an entire client was part of the original spec and 2nd that it would be possible to create a summary field on a User List.
Photo of GB_DirectorOfOne

GB_DirectorOfOne

  • 70 Points
Great idea, but user list fields are not an option to base a matching criteria on when creating the summary field in the parent table. The quest continues...