Value List from related table problem

[benmort81]

Hello,

I have a Employee_Table that holds records of employees including their Employee_Code.  I have a Employee_Codes value list being generated from the Employee_Code field.

Employees are users of the system and log into the system with a limited privileges set to view and edit their own record, no other records.

I have a Meetings_Table related to the Employee_Table in which employees record meetings.  One of the fields to be filled in is a Meetings record that uses the Employee_Code value list - from which I want the user to be able to select from any current employee's Employee_Code.

The problem is, that they only see their own Employee_Code in the value list.  This is obviously because they only have privileges to view their own record - but how do I get around this problem so that they can see a list with all Employee codes?

Thanks for any help

Ben

Edited May 14, 2015 by benmort81

[comment]

Well, you have conflicting requirements here: on the one hand, you don't want a user to see any of the other users' records. On the other hand, you do want him/her to see all of them, when they look at the value list.

I believe you have two options here;

1. Allow the access to other users' records and disallow field access to all fields, except the fields participating in the value list. However, this will also prevent users from editing their own records.
    
2. Split the users table into two, with a one-to-one relationship between them. Let the "main" table hold the fields participating in the value list, and place all the other fields in the "sub" table with limited access.

 

 

[benmort81]

Thank you comment.

I was actually trying out something similar to option 2 when you posted.  Although I created a sub table with just one field with a copy of the Employee_Code and made this have all access.

It seems to work - is there any downside in having the access to table/sub-table this way around?

Thanks,

Ben

[comment]

is there any downside in having the access to table/sub-table this way around?

​There is the downside of added complexity. And there's more layout work for you to do. I cannot think of any functionality that will be affected by this, if that's what you mean.