1 Reply Latest reply on Jan 21, 2014 6:05 AM by philmodjunk

    Generating a drop down list from a table

    RhysWillis

      Title

      Generating a drop down list from a table

      Post

           Hey all,

           So I currently have two tables. Table one consists of tutors which contains around 10-15 records. This table will slowly grow and I appreciate its tiny but currently thats not the issue.

           The second table is a list of students. We currently have around 400 students but as we get a tutor this will jump up 30 or 40 at a time.

           What I would like to do is two things.

           Firstly, make the one to many relationship between the two tables. Currently - for some reason - its one to many but the wrong way around. Many tutors for one student. No idea how to resolve this.

           The second thing is to make it so that the tutor field within the students layout would be a drop down box of all the names of the instructors. This is so our sales people can book a student in with an instructor and pick that instructor from the list rather than manually typing the name in.

           The reason we want it linked is so that on the instructors layout we can make detailed reports of all the instructors pupils.

           Thanks in advance.

        • 1. Re: Generating a drop down list from a table
          philmodjunk
               

                    Firstly, make the one to many relationship between the two tables. Currently - for some reason - its one to many but the wrong way around. Many tutors for one student.

               Open Manage | Database | Fields

               Define a primary key field in Tutors. I'll refer to it s __pkTutorID make it a field of type number. Double click the field definition to open field options for that field. On the auto-enter tab, select serial number. Each new record in Tutors will now automatically enter a serial number in this field that uniquely identifies each tutor. Switch to the Students table and add a number field, _fkTutorID. Do not specify any field options for this field. Click over to the Relationships tab. If there is a line connecting students to tutors, click it and press delete to remove it. Drag from __pkTutorID to _fkTutorID and you now have a one to many relationship between students and tutors.

               Since you have existing Tutors records that need a serial number. Go to the Tutors layout and add the __pkTutorID field to your layout. Select Show All Records from the Records menu. Use Replace Field Contents from the Records menu to assign each record a serial number. (And select the option for updating auto-enter settings when you do this.)

               

                    make it so that the tutor field within the students layout would be a drop down box of all the names of the instructors.

               What I am about to describe is the "beginner level" way of getting this to work. You want to get this method working first before trying other methods as they all are based on this basic set up so this is a good starting point.

               Open Manage | Value Lists. Click New. Enter a name for your value lists and select the "use values from a field" option. This opens a dialog with two drop down lists at the top. From the first drop down, select Tutors and then click __pkTutorID to select it as the first field. Then click the "also display values from second field" check box and select a name field from Tutors. If you Tutors table has separate first and last names, define a field in Tutors that combines the first and last names and select it as the second field. I recommend a text field with an auto-enter calculation and a unique values validation rule for such a field. Select the "show values only form second field" option and then close your dialogs by clicking OK.

               Now place _fkTutorID on your Students layout and use the settings in the Inspector's Data tab to format it as a drop down list or pop up menu that uses this value list. Add some fields from Tutors to this layout and you'll see them update with the selected tutor when you select them from the list.