I work in the field of HR. When I create an application, most of the time, I have to ensure that only the employee and their management line have access to the employee’s records.
In the example below, I would like that
Mark has access to the records of Mark, Bill, John, and Bruce.
Bill has access to the records of Bill, John, and Bruce
I would like to create a virtual column containing the email addresses of all the managers of a person in text format. In the case of Bruce, this line would look like this: “bruce@test.com, john@test.com, bill@test.com, mark@test.com”
I could then grant access using an IN or CONTAINS USEREMAIL().
Or.. if the first one is a number column and the management is as straight as you described, something like LOOKUP(USEREMAIL(),Data,Email,Number)<=[Number] and no virtual columns are needed. Though it depends on where you are using this.
You can see what POC solution I implemented in the attached video. Here you see the MGRS column being updated either individually (the inline arrow action) or in bulk by a bot (triggered by a change in the MISC table). Since changing managerial relations affects many records, I would go with the bot way to ensure all the records are updated correctly,
Screen shot
I introduced two columns to control my LOOP
column “level” to indicate the individuals’ levels in the managerial hierarchy
column “Next up” and this is the key (Edited: this can be deleted and its expression directly embedded in relevant places)
As the loop iterates, this column is updated to hold the ID of the next higher up manager to be added to the column MGRS. Within the LOOKUP expression, the minrow identifies the manager with the highest level ( the smallest number) inside MGRS and then uses it to LOOKUP its manager.
The LOOP stops when there is no manager to add in this column.
Manipulation of recursive relations is always difficult within AppSheet and I usually tend to flee to Apps Script. This is just one attempt (it was fun!) to solve your requirement inside the platform.
Since this requirement - record level security - is not at all uncommon, I would also be interested in knowing other approaches that other members may have implemented. (Apologies, I have not done much searching..)
I’ve received this email notification from @Steve , but I can’t find it here :
“It appears you want to identify the chain-of-command for each user. The complexity here is that you have layers of management and that the chain-of-command for someone at layer N requires that the chain-of command for layers N-1 up to 1 be already defined. There are several approaches, each with drawbacks. Before considering them, why do you want this? Is there a specific need you’re pursuing? This might suggest the solution.”
Yes, chain of command, that’s it. I want to be sure that only the user and his managers (chain of command) will access that user data. I suppose in a technical point of view, it means implementing a record level security strategy. Thanks !
This is something extra but probably quite helpful if your organization is large.
As the number of your employees grows large and the organization becomes complex, editing management lines could introduce unintended ‘loops’. Again You need external solutions to detect these because I believe AppSheet does not provide such capability.
Here the loops field shows a result of an Apps Script called by AppSheet. Loops cause infinite loops in my previous solution and this was necessary to prevent it.
Using the current_data, you can create a graph to visually see where the loops are. This web page was created by using this (cytoscape) if anyone is interested.
