5 Replies Latest reply on Jan 27, 2011 11:17 AM by pademo57

    Having Drop-down boxes over-ride each other

    pademo57

      Summary

      Having Drop-down boxes over-ride each other

      Product

      FileMaker Pro

      Version

      11.0 v2

      Operating system version

      Windows Vista

      Description of the issue

      Yes, I am a newbie to Filemaker and I am transitioning from MS Access and can't figure out how to do this in FMP 11.

      I have 2 tables Student and Classes.  Because they are Many-to-Many relationship I also have a Stud_Class table as shown below:

      Students >--Stud_Cour --< Courses

      Our Administrator wants us to have a form which displays the Student information and a portal which shows the Courses the student wants to take using a Drop-Down box.  The Courses have a Course Number, ex. MAT001 and a Course Name, ex. MATH 101.

      What she wants to be able to do is click on the Course Number drop-down box and select a Course Number and it automatically fills in the Course Name.  She also wants to be able to click on the Course Name drop-down box and have it update the Course Number box.  I know this overrides the other drop-down box but that is what she wants.

      Can somebody help with this please?

        • 1. Re: Having Drop-down boxes over-ride each other
          philmodjunk

          You're almost there

          I'm assuming these relationship details:

          Students----<Stud_Cour>-----Courses (>---- means many to one)

          Students::StudentID = Stud_Cour::StudentID
          Courses::CourseID = Stud_Cour::CourseID

          With "allow creation of records via this relaationship" enabled for Stud_Cour in the Students to Stud_Cour relationship.

          Put a portal (think Access sub form or sub report ) to Stud_Cour on a layout based on Students.
          Put Stud_Course::CourseID in this portal and format it with a drop down list of couse ID numbers from Courses. (A Course Name field can be included as a second column of info in this value list.)

          Add the Course Name field (and any others you need) from the Course table to this portal row and you'll see this info appear when a CourseID is selected in the drop down.

           

          • 2. Re: Having Drop-down boxes over-ride each other
            pademo57

            Thank you for your answer. But I'm still not able to get the other fields to populate in the portal when I select form the drop-down box.

            I think my problem is more with the mechanics of setting this up.  I'm used to writing everything out in code and going through the menus is, I think, where I'm having a hard time.

            I'm sorry I seem to be so dense when it comes to this but if you could clarify my steps that would be fantastic, so here goes:

            (I've tried to color code this to show relationships)

            Students Table Stud_Cour Table Course Table

            Stud_ID Stud_Cour_ID          Cour_ID

            Stud_fname fk_StudID Cour_Name

            Stud_lname fk_CourID Cour_Cost

            1) File > Manage > Database > Relationships (double-click on the "=" between Students and Stud_Cour)

            2) On the left side it says Students and on the right side it says Stud_Cour

            3) Click on the right side under Stud_Cour "Allow creation of records via this relationship". Click OK and OK again to go back to Layout.

            4) On the Students Layout, go into Edit Layout and click and drag a Portal on to the Students Layout.

            5) Click Show Related records from: (in this case) Stud_Cour and click OK

            6) Click from available fields from table Stud_Cour so Included Fields says: Stud_Cour::Stud_Cour_ID

            7) Edit field Stud_Cour::Stud_Cour_ID to a Control Style - Drop-Down Box

            8) Click on Pencil beside Values List

            9) Click New for Values List Name, type a name "Course List"

            10) Click on radio button "Use values from field:"

            11) Under "Use values from field:" click "Courses"  and for field click "Cour_ID"

            12) Under "Also display values from second field" click "Cour_Name". Click OK, OK, OK to return to Layout

            13) Click and drag Field onto Portal beside the drop down box and specify field from Courses Table and select Cour_Name 

            14) Save Layout

            If I have followed your steps correctly when I now click in the portal on the drop-down box, although it lets me choose different course ID's after leaving the field nothing else happens, it doesn't fill in the rest of the portal line course information.

            What have I done wrong?

            • 3. Re: Having Drop-down boxes over-ride each other
              pademo57

              Argh! Sorry about the way the tables displayed it looked so good when I first made it,  but here is the way it should look:

              Students Table: Stud_ID, Stud_fname, Stud_lname

              Stud_Cour Table: Stud_Cour_ID, fk_StudId (connected to Stud_ID in Students), fk_CourID (connected to Cour_ID in Course)

              Course Table: Cour_ID, Cour_Name, Cour_Cost

              • 4. Re: Having Drop-down boxes over-ride each other
                philmodjunk

                Here's the problem: 

                6) Click from available fields from table Stud_Cour so Included Fields says: Stud_Cour::Stud_Cour_ID

                7) Edit field Stud_Cour::Stud_Cour_ID to a Control Style - Drop-Down Box

                Make that fk_CourID instead of Sud_Cour_ID and this will work.

                While a primary key in Stud_Cour could be useful in the future, it is not needed for anything that you have set up so far.

                Here's a demo file that matches contracts to companies in the same fashion that you are matching students to courses that you may find useful:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                • 5. Re: Having Drop-down boxes over-ride each other
                  pademo57

                  THANK YOU!!! I have to laugh at my attempts and this shows the difference between Filemaker and MS Access.  It was easier to write the code than to try to figure out how to do this in Access.  Which is just the opposite in Filemaker.  Thanks again.