Pulldown with subset of value list

[scott93933]

Hi all,

I'm creating a timesheet database that I can use to track the hours for employees of my consulting business. I've enclosed a copy of my database to illustrate what I'm trying to accomplish. An employee can select which project they're working on (from a pull-down menu) and then enter their hours for that project. The problem is that as we do more and more projects, that "Projects" pull-down menu is going to grow very large and an employee will have to spend 10-15 seconds scrolling through hundreds of projects in order to select the correct project and enter the hours for their work session. Not only will this be inconvenient for them, it will no doubt lead to errors as incorrect projects will occasionally be chosen.

Each project has a Project Status, which can be set to "In Progress," "Waiting," or "Completed." The solution I'd like to implement is to only show projects in the Projects pull-down menu that are not "Completed." That way, employees will only have 4-5 projects to choose from in the menu; the 4 or 5 projects that are currently active (not completed).

The enclosed database has two tables and two layouts, so that an employee can change the status of a project and have the change applied to all work sessions for that project. In other words, if "Project 1" has three work sessions, an employee can set the Project Status of Project 1 to "Completed" and the Project Status for Project 1 will be changed to "Completed" for all three work sessions.

What I'd like to add (and haven't been able to make work) is that once a project's status is set to "Complete," the project should no longer appear in the "Project" pulldown menu in the "Management View" layout. It should still be accessible from the "Project" pulldown in the "Project Status" layout, however.

Any help from you experts would be greatly appreciated.

The enclosed database can be opened with Guest access, by the way.

Thanks!

Scott

Timesheets.fp7.zip

[dbruggmann]

Hello Scott

I changed your file slightly according your wish and attach it here. Have a look at the Value lists Dialog and the Relationship Graph, then you can probably figure out my changes.

Timesheets_v2.fp7.zip