2 Replies Latest reply on Oct 29, 2010 12:36 PM by KLB

    Database Structure Help: Alternatives to Conditional Value Lists



      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:

      1. 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?
      2. 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.

      Thank you.

        • 1. Re: Database Structure Help: Alternatives to Conditional Value Lists

          "I have tried making conditional value lists, but that requires 2 layouts,"
          Conditional value lists do not require two layouts. They don't actually refer to any layouts only table occurrences (the boxes in your relationship graph) and you can build a relationship for your conditional value list that uses two table occurrences for the same data source table.

          If your main table is called "Projects", you can click this box in the relationship graph and then click the button with two green plus signs to make an additional table occurrence of it. It will show up as "Projects 2" but you can rename it as needed. This makes it possible to set up this relationship for your conditional value list:

          Projects::Name = Projects 2::Name

          You can then specify your values from "projects 2" and specify use only related values starting from Project 1.

          2) I'd set up a related table for your monthly updates that is related by a project ID number to your Projects table. You can then put a portal to this new table on your layout and can log from 0 to thousands of monthly updates.

          • 2. Re: Database Structure Help: Alternatives to Conditional Value Lists

            Thank you very much PhilModJunk. These were the hints that I needed. Both problems are solved now!