4 Replies Latest reply on Aug 18, 2009 9:16 AM by philmodjunk

    Check boxes and automatically select based on data from another table.

    WillGonz

      Title

      Check boxes and automatically select based on data from another table.

      Post

      I am creating a database of users on my network.  For simplicity purposes I just want their name and job duties.  So Lets say I have Microsoft Office installed.  For everyone in accounting they need Word and Excel.  For people in the Marketing they have access to PowerPoint and Outlook.  So I want to create another table that will have the job titles.  Also in that table they will have check boxes that says the application names.  I check the check boxes for the person in accounting for their required programs.  Now on my main table I pull down a list and select the job title.  Based on the preselected applications in the other table those check boxes would be filled out.

      How would I accomplish this?  

      I have created a text Field.

      I have created a Checkbox set and a value list. 

      I created another table.

      I created a text field for the job title.

      I created a text field for the checkbox set and a duplicate value list. 

       

      Thanks 

       

        • 1. Re: Check boxes and automatically select based on data from another table.
          philmodjunk
            

          Let's make sure we're looking at the same two tables first.

           

          Table1, Personnel with at least two fields:

          Name, text

          JobTitle, text

           

          Table 2, AppList with at lest two fields:

          JobTitle, text

          Applications, text, formatted as a check box field

           

          at least 1 relationship Personnel::JobTitle = Applist::JobTitle

           

          If you set it up as shown above, you can simply add the Applist::Applications field to your Personnel layout and the list of applications will appear whenever a matching Job Title is entered/selected from drop down in Personnel::JobTitle.

           

          • 2. Re: Check boxes and automatically select based on data from another table.
            WillGonz
              

            On my Dropdown list. Do I tell the managed list to use values from field table2::jobtitle?

             

            • 3. Re: Check boxes and automatically select based on data from another table.
              philmodjunk
                

              I'd prefer to create a third table (no relationship needed) with one record per application and use it as the source of values for my value list.

               

              PS. You might gain an additional benefit from this as you could add additional fields to this third table to better document each application that you are tracking. You could add a field to track the version info for each app just to name one example.

              • 4. Re: Check boxes and automatically select based on data from another table.
                philmodjunk
                  

                I looked at your file and found a few problems that were probably interfering with what you want to see happen:

                 

                1) I changed the table reference for "Setup Jobs" to "JobProgramList" and changed the top field from MyStaff::JobTitle to JobProgramList::JobTitle. Now you can create one program list here for each job title. (To change a table reference, enter layout mode, select layout setup... and pick a different table from the drop down in this dialog.)

                 

                2) On the MyStaff layout I replaced MyStaff:: Program with JobProgramList:: Program. (You can delete MyStaff:: Program from the table as you don't need it.)

                 

                3) I modified the value list definition for JobTitle to draw its values from JobProgramList::JobTitle instead of a list of custom values. Now this list will automatically update when you add/remove/edit records in the JobProgramList table.

                 

                Now, if you select a different job title for a given record, you'll see a different set of check box selections automatically appear in your Program field.

                 

                Since you asked about basing the list of applications on a table, I created a table for this and defined a new value list that refers to this field for the list of applications. This is not strictly necessary but may give you some ideas for future enhancements. The current version of your database would work just fine with custom values as you had them originally set up.