11 Replies Latest reply on Feb 21, 2014 4:18 PM by dgreensp_1

    FileMaker Relationship using FM13

    dgreensp_1

      Title

      FileMaker Relationship using FM13

      Post

           Problems with relationships - I'm hoping someone can help!

           I have a registration database setup with the following tables and relationships:

           Table 1:  Student

           Table 2:  Course

           The Student and Course Table are linked by a field called School Name

           In a layout I have a field in the Student layout called Course Name that is a drop-down based on the values from the Course Table but only shows value based on the relationship between the Student and Course tables.

           Table 3:  Course Fee

           The Course Fee and Course Table are linked by a field called Course ID

           In the Student layout I want to show the fee for the course but I keep always getting the first fee in the table and not the fee based on the choice the parents chose when registering.  This is where I'm having problems.

           I've attached screenshots to help troubleshoot.  Any help would be greatly appreciated!

      FileMaker.png

        • 1. Re: FileMaker Relationship using FM13
          philmodjunk

               Your relationships do not have a way to match to a selected course, only to a selected school.

               For a given student, do they only enroll in a single course or might they take multiple courses?

          • 2. Re: FileMaker Relationship using FM13
            dgreensp_1

                 They register for one course only.

            • 3. Re: FileMaker Relationship using FM13
              philmodjunk

                   Then your Student record can use a field to show the selected course, but you'll need to link the student table to two Tutorial: What are Table Occurrences? of your Course table--one will link by School and the other by CourseID. That way, the relationship that links by CourseID can be used to access the needed Course fee.

              • 4. Re: FileMaker Relationship using FM13
                dgreensp_1

                     I actually tried this but I can't seem to figure out how to get the CourseID into the student database.  When the parent selects the Course Name I'm still getting the Course ID from the Course Table with the first occurrence for that school.  This is where I seem to be stuck.

                • 5. Re: FileMaker Relationship using FM13
                  philmodjunk

                       You need to define a CourseID field in the student table.

                       You'd then format that field with a conditional value list of CourseID's in field one and Course Names in field 2.

                       to select a Course for a given student, select it from that value list.

                       You'd first select the school and then select the course from a value list that only lists courses from the selected school.

                  • 6. Re: FileMaker Relationship using FM13
                    dgreensp_1

                         Thank you for all of your help, but I must be missing something.

                         Under Course selection they select the course form the value list but it puts the Course ID in the Course name field and not the Course Name.  If I change the field to Course ID then it puts the Course ID as well.  It never puts the Course name.  The only thing I could do is put a field below that shows the course name.  I was trying to avoid having them see the course ID since they don't know these IDs and I don't want to confuse the parents.

                    • 7. Re: FileMaker Relationship using FM13
                      philmodjunk
                           

                                Under Course selection they select the course form the value list but it puts the Course ID in the Course name field and not the Course Name.

                           This is expected behavior for a drop down list. Though a pop up menu need not show the Course ID at all. But for a drop down list, you can place the course name field from the courses table next to or even on top of the drop down list of course ID's to show the name. (If you put the name field on top of the drop down list field, give the name field an opaque fill color and use the inspector to disable browse mode access to the name field. Also, do not select the arrow option for the drop down list field.)

                      • 8. Re: FileMaker Relationship using FM13
                        dgreensp_1

                             I thought of that, but I don't think that will work with WebDirect because I don't think you can layer in WebDirect.  I guess just adding the name field though would be the best solution otherwise in WedDirect.

                        • 9. Re: FileMaker Relationship using FM13
                          dgreensp_1

                               Actually it doesn't work because the Course Name shows up as the first name in the Course Table for that corresponding school.  Same issue all over again.

                          • 10. Re: FileMaker Relationship using FM13
                            philmodjunk

                                 Then you are using the course name field from the wrong table occurrence. be sure to select it from the Courses Table occurrence that is linked to your student table by CourseID, not the one linked by school name. (BTW, using School names is also not the best way to link records to your schools table.)

                                 I don't know if that method will work in WebDirect, you'll have to try it and see, but I also mentioned that a pop up menu does not have this issue. And there are other data selection methods besides drop down lists and pop up menus that you can use for selecting a course.

                            • 11. Re: FileMaker Relationship using FM13
                              dgreensp_1

                                   Actually a popup menu works!  I didn't see that earlier.  This is fantastic.  Thank you so much for all of your help!