(Please see bellow a snapshot of a portion of the relationship graph of my database)
I need to define roles, in a way that a specific group of users have access only to a portion of the records on tables. The “limited” criteria should be defined based on the association of two tables (i.e. “Edited File” and “Staff-Edited File). I do not know how to do so.
Further explanations based on the following relationship graph:
Each staff is responsible for some Edited Files. I want to design the privilege set in a way that each staff member could only see the records on “Edited File” that is associated with him/her. However, I also want that staff member to be able to see the “Project” records that are associated with their records on “Edited File”.
I tried to set a custom criteria (“limited” criteria) for the view/edit privileges (under Custom Record Privileges) for the tables.
My problem is that I do not know how to use the script “Get (AccountName)” while the “__PKStaff ID” (and thus, “__FKStaff ID”) are numerical. I have entered the full name of the staff member in the “name” field on the “Staff” table , however, the tables “Edited File” and “Staff_Edited File” are not connected through the “name” field, but through “__PkStaff ID” field which is numerical.
I hope I have explained myself properly. Any ideas are much appreciated!!
Let me know if you had any questions.
Thanks a lot!
Use a script performed by the OnFirstWindowOpen trigger (specified in File Options), to use the person's account name to find their corresponding record in Staff. Set a global variable to the value of the corresponding Staff ID.
You now have a numerical value in this global variable to use in your privilege set's lock expressions in order to control access.