Filtering a value list based on 3 'link' tables

[noodleman]

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.

[comment]

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.

[noodleman]

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?

[noodleman]

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.