Database Structure Help: Alternatives to Conditional Value Lists
I want to build a database that holds details about all the projects that my department works on. Each project should be associated with the name of the person that is responsible for that project. The layout will have at least 20 fields on the layout for data entry. In less than a year, we expect that the list of projects will be at least 200.
I need help on developing a database structure that will allow us to do 2 things:
- We need to have 1 layout for everyone to use to add new projects and update existing ones. I would like to have it set up so that once a person enters their name, then the next field will only show the projects that are tied to their name in a drop down menu. Most importantly, they also need to be able to add new project titles to this "sorted" list in the same layout. I've tried making conditional value lists, but that requires 2 layouts, which makes it impossible to add new project titles without having to switch between layouts (either manually or with scripts). Is there another way to accomplish this sorting so that only the projects the person is resonsible for will show from a drop down menu and allow them to edit? I've gotten a suggestion from the phone technical support to use a drop down list that is based on records, instead of a value list. Can anyone help with this or suggest a different solution?
- In addition to associating each project with a person, we also need a field to type in monthly updates on each project. Not all projects will have a monthly update from now to forever. Some will end and not require anymore updates. Also, new projects will start and only have monthly updates from their start date for a period of time. I am planning a long term solution, and would rather not make a new field for each month/year. There are probably many ways to do this, but I need a solution that will work well considering issue #1 (i.e. if I have to make multiple layouts for people to switch back and forth from). One idea is to have a month/year field where once the month/year is selected, then a separate adjacent linked field will automatically show the update details for this project that are associated with that month/year... and allow them to add/edit the information there especially when the entry is new.