How can I limit access data based on a supervisor-user relationsheep (N-N) ?

[DroidTeo]

Hello all!

here my needs: 
the user fill some data creating a report. This report has a state "toBeFinalized". 
Once the state is equal "toBeFinalized" , the supervisor of the user that created that report need to receive an email. The supervisor must be able to see and modify all rows created by all users assigned to him.

How can I structure something similar? 

I was thinking to: 

1. add a new table users
2. add another table supervisor and to add here the ref towards the users table 

what do you think? (ps how? ) 

Then, the supervisor should receive an email with the report to be checked, but step by step 🙂 

Thank you very much! 

[1minManager · Accepted solution]

So some quick notes on this

• You're right that you are going to need a Users table.  The two main columns you'll need in here is [Login Email] to record what they login with and [Type] which could be as simple as a ENUM of "User" or "Supervisor"
• How I would solve the [State] = "toBeFinalized" option is to have "toBeFinalized" as the initial value with an editableIF formula of

OR(
   ISBLANK([State]),
   ANY(Select(User[Type],[Login Email]=UserEmail()))="Supervisor"
)

•  To trigger an email when a report is created your going to need to create a bot where the trigger is "Adds" on whatever table name the report is created on.  Checkout https://support.google.com/appsheet/answer/11432969?hl=en for more info

Simon@1minManager.com

[DroidTeo]

 @1minManager hello!
Thank you for your answer!

For the moment, I've solved point 1 using 2 different tables: one as users and 1 for the supervisor. 
In this way, I can easily linked the users to their supervisor and implement a kind of logic like "who should check what and who".

But, I think that your solution it is better in term of DB structure.. I will try to implement it a little bit later (for the moment I'm working on a Proof of Concept)