(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 “Project” might have several “Edited Files” and each Edited file might have a staff (from “Staff_Edited File” table, which is an occurrence of the table “Staff”) associated with it.
- “__PKStaff ID”: an auto-enter serial number field.
- The staff’s full name (which is identical to the account name by which the person will log on) is entered in a field called “name” that is on the “staff” table (the "name" field cannot be seen on the graph- sorry! but believe me, it is on the "staff", and thus, "Staff_Edited FIle" tables!!)
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!