Manager Privileges by accountname

[Courtney]

I'm having a hard time articulating what I'm looking for, which is making it really difficult to search for a solution on the forums.

I've got a database with a Personnel table, which holds info about each staff member, including "Employee Code" and "Team".

In the same db, I have a "Time Record" table where Employee Code is an auto-entered field equal to AccountName. I have a "Team" field in the Time Record table which is a lookup from the Personnel table based on the Employee Code.

For base level employees, I have privileges set up so that they can only view and edit their own records [with the calculation: Employee code = Get ( AccountName )] I'm setting up a manager level privilege set, and I'd like managers to be able to view the records of everyone on their team (but only edit their own records).

What calculation would I use to take the currently logged-in account name, compare to the Employee Code in the Personnel table, pull the related "Team" from the personnel table, and then let them view all records in the Time Record table that have the same team?

I think I'm making this more complex than it needs to be and just confusing myself.

~Courtney

[Ender]

I think a relationship from Personnel to Time based on Team would show the records a Manager has access to. Something like:

Personnel<=>Team

Personnel::Team = Time::Team

AND Personnel::EmployeeID <> Time::EmployeeID

On second, thought, you probably only want this valid for Managers, so you should use a calc to filter it:

ManagerTeam = case(Position = Manager; Team)

Then the relationship would be:

Personnel<=>Team

Personnel::ManagerTeam = Time::Team

AND Personnel::EmployeeID <> Time::EmployeeID

To make this available from the Time table (for your record level access calcs,) I think you'll need to store the current employee's ID in a global (or an unstored calc), so you can use it in a relationship from Time to Personnel.

Time<=>Personnel

Time::gEmployeeID = Personnel::EmployeeID

For the record level access privileges, you can look through the two relationships to see the Time records that the current employee has access to, and validate that the current record is one of them. There may be simpler way, but a patterncount on a ValueListItems function is what comes to mind.

I also see a couple potential problems with using the account name as a relationship key for your employee and time records:

1. Account names could change over time, especially if they are constructed from the emplyee's name. Your relationships will be lost if they change.

2. There are times when a supervisor has to complete a time record for an employee. You should make sure you take this into account.

[Courtney]

Thanks for the response. I will dig in to the meat of your post after a meeting that I have to go to.

In our environment though, a supervisor will never alter an employee's time record. In the case something needs to change, it's done by one of the office managers who has admin rights (conveniently, that's me).

~Courtney