Ok, this should be an easy one for you experts but it's driving me crazy-

I have 3 tables linking many to many relationships as follows:

1. "Employee Roles" - Out of all the employees in our company, each can preform one or more roles (eg. estimator, engineer, senior engineer, etc.)

2. "Project Roles" - Out of all the projects running at our company, each 'role' is defined with a charge out rate. ie- the rate for an engineer may change from project to project.

3. "Project Employees" - Each emploee must be 'authorised' to work on a project.

Simple so far.

Now we create a new table called "Timesheet LineItems" on which an employee books their hours worked for any one week. On each record in this table, the user selects a project they are working on (noting that an employee may be working on many projects), then selects a value that enters a "Project Roles" key ID, which will be used to draw the charge-out rate to calculate the client cost for that line item.

So here's my question:

How can I create this value list so that it filters out

a) the projects that employee is allowed to book to;

the roles that employee is authorised to work;

c) the authorised roles for the project?

Any help on this would be greatly, stupendously appreciated.

Cheers,

Andrew.

If I understand this correctly, you need TWO value lists:

1. A value list of ProjectIDs that the employee is authorized to work on - this should be easy enough, since this is given by the relationship to Projects (through Employees and ProjectEmployees), or directly to ProjectEmployees, by matching EmployeeID.

2. A value list of RoleIDs that this employee can legitimately undertake for the selected project. This one is a bit more tricky:

First, define a calculation field that lists all the RoleIDs that the employee is qualified for (via a relationship to Employee Roles). Now define a relationship to a new occurence of ProjectRoles, matching ProjectID to ProjectID, AND the calculation field to RoleID. The related records in ProjectRoles form the basis for the value list.

Thanks.

All of this makes sense to me except for the implementation of the 'EmployeeRole' calculation field. Here's how I've set it up:

1. Create a table occurance called 'timesheetlineitem_EMPLOYEEROLE' linking the employeeID to the employeeID in the 'TIMESHEETLINEITEM' table occurance.

2. Use the List() function

"List(timesheetlineitem_EMPLOYEEROLE::_RoleID)"

to generate a list of all roles an employee is authorised to work. This calculation resides in the 'timesheetlineitem' table, named cEmpRoleID.

3. Create a new table occurance based on the ProjectRoles table called 'timesheetlineitem_PROJECTROLE_vl'

4. Link the projectID from 'TIMESHEETLINEITEM' to ProjectID in 'timesheetlineitem_PROJECTROLES_vl'

AND

the calculated 'cEmpRoleID' field from 'TIMESHEETLINEITEM' to RoleID in 'timesheetlineitem_PROJECTROLES_vl'

5. Define a new value list 'Authorised Project Roles' drawing the ProjectRoleID from 'timesheetlineitem_PROJECTROLES_vl'

that includes only related values starting from 'TIMESHEETLINEITEM'

I must be missing a step or don't understand how the calculation field can be used to define a complex relationship, because I get no values in the pop-up menu.

Any ideas?

My mistake- one of the foreign key fields was defined as a number field when it should have been text. The VL is working! Thanks heaps.