11 Replies Latest reply on May 20, 2010 2:11 PM by philmodjunk

    Trouble with Value Lists and Relationships

    beckles88

      Title

      Trouble with Value Lists and Relationships

      Post

      Hi All,

       

      I am building a student system and when a student enrols they must be assigned a tutor depending on the course, the course type, whether the tutor is active and also if the tutor has spaces available on that course.

       

      A tutor is active if 'Tutor Status' = "Active" and 'Tutor Allocation' = "Allocate" these fields are in a Tutor table.

      I used a calculation If(Tutor Status = "Active" and Tutor Allocation = "Allocate"; "Yes"; "No") to calculate an 'Active Tutor' field.

       

      There is a Course Tutorial table which features as a portal on the Tutor table.

      It has 'Course Name'

      'Course Type'

      'Maximum Students'

      'Enrolled Students' (ValueCount ( FilterValues ( List ( Enrolled Courses::Course Status ); "On Programme" ) and Case ( Enrolled Courses::Course Name; Course Name )and Case ( Enrolled Courses::Course Type; Course Type ) )

      'Student Spaces' (=Maximum Students-Enrolled Students))

      'Tutor Name'

      'Tutor Record Number'

       

      The Course Tutorial works well to show how many spaces are left on courses with a particular tutor.

       

      I tried using an 'Available Tutor' field on the Course Tutorial table to find tutors who were active and had spaces. (If(Enrolled Students>0 and Tutors::Active Tutor = "Yes"; Tutor Name; "") This field works but it is not possible to index the field.

       

      I have got myself confused as to what I need to do to get only the relevant tutors appearing as a drop down list when enrolling a student.

      Any help would be appreciated.

        • 1. Re: Trouble with Value Lists and Relationships
          philmodjunk

          It is confusing and I don't have your file to look at so that makes it more confusing. Can you expand on your post by listing the tables involved and how they are related? That'll help make sure suggested solutions will work with your tables/relationships and we may spot ways to clean things up for you in the process.

          • 2. Re: Trouble with Value Lists and Relationships
            beckles88

            I know its quite hard to explain, the system is a student system.

            So the basics are a Tutor table a student table and a course table. There is then an enrolled courses table which makes the relationships one to many.

             

            The Student Table and Enrolled Course table are linked by 'Student Record Number' the Tutor table is linked by the 'Tutor Name' and the course table by the 'Course Code'

             

            Also the Tutor Table links to the Course Tutorial table by the 'Tutor Record Number' field.

             

            Any more information which would be useful?

            • 3. Re: Trouble with Value Lists and Relationships
              philmodjunk

              Let's see if I understand correctly with this recasting of your information:

               

              Student::Student Record Number = Enrolled Course::Student Record Number

               

              Course::Course Code = Enrolled Course::Course Code

               

              Tutor::Tutor Record Number = Course Tutorial::Tutor Record Number

               

              and " Tutor table is linked by the 'Tutor Name' " --- to which of the above tables?

              • 4. Re: Trouble with Value Lists and Relationships
                beckles88

                Thank you for your help, that is correct and the Tutor is linked by 'Tutor Name' to the Enrolled Courses table.

                • 5. Re: Trouble with Value Lists and Relationships
                  philmodjunk

                  Sorry I haven't suggested a fix, but cleaning up your table structure first may simplify your final setup.

                   

                  Your Course Tutorial table lists the students assigned to a given tutor, correct?

                   

                  I'd simplify it by:

                  'Course Name' --Remove, and replace with Course Code

                  'Course Type'--Remove, this info should already be in the Course table

                  'Maximum Students'

                  'Student Spaces' (=Maximum Students-Enrolled Students))

                  'Tutor Name'--Remove this is already in your tutor table

                  'Tutor Record Number'--use this field as your link instead.

                  'Enrolled Students' (ValueCount ( FilterValues ( List ( Enrolled Courses::Course Status ); "On Programme" ) and Case ( Enrolled Courses::Course Name; Course 

                  Name )and Case ( Enrolled Courses::Course Type; Course Type ) ) ---This is a real puzzler. This appears to be a attempt to count the number of students assigned to this tutor. If so, Count ( Enrolled Courses::Course Status ) should return the same count. The and operator is a logical (boolean) operator used in comparison expressions such as ( A > 5 ) AND ( B < 6 ) As written, I don't see that you'll get any result except either 1 (true) or 0 (false).

                   

                  OK, now for your value list. This is a classic problem encountered with filemaker databases. You need a calculation's field value to be a stored, indexed value, but it references data in related tables and thus cannot be stored and indexed.

                   

                  I suggest you put a number field in the Tutor table called Student Spaces.

                   

                  Your calculation for your value list: If ( ( Active Tutor = "yes" )  and ( Student Spaces > 0 ) ; Tutor Record Number ; "" )

                  Make your value list a two column value list with this calculation field as the first column and the tutor's name as the second.

                   

                  To put the correct value in Tutor::Student Spaces, You set up scripts and script triggers so that when a student is assigned to a tutor or removed, the script copies the Student Spaces value from your course tutorial table to your Tutor table.

                  • 6. Re: Trouble with Value Lists and Relationships
                    beckles88

                    I need the 'Course Name' and 'Course Type' as these are used in a portal on the Tutor record to show the courses tutors teach what type of courses these are (a music course can be distance or face to face).

                    The 'Enrolled Students' actually calculates the Students enrolled on each course individually depending on the type as well.

                    I agree with removing the Tutor Name I entered this to try and calculate Available Tutors, uing the Tutor Record Number also sounds good.

                     

                    Thank you for your help so far :) How would I get the tutor spaces to calculate the students enrolled on any type of course with that tutor?

                    • 7. Re: Trouble with Value Lists and Relationships
                      philmodjunk

                      You can simply add the course name and course type fields from the course table to your Course Tutorial portal. There's no need to duplicate the fields.

                       

                      From the tutor table, Maximum Spaces - Count ( Course Tutorial::Tutor Record Number )

                       

                      I'd move maximum spaces to the Tutor table as I believe you need this number to represent the total number a tutor can accept.

                      • 8. Re: Trouble with Value Lists and Relationships
                        beckles88

                        I could add a sum Maximum Students to the Tutor Table but I need a Maximum Students on the Course Tutorial to see the Maximum number of students that a tutor will accept per course.

                        • 9. Re: Trouble with Value Lists and Relationships
                          philmodjunk

                          Yeah but you won't see this unless you copy the data from somewhere else with each new record in this table. Just like Course name and type fields, you can add this field to the portal on your layout from the tutor table. Then you have this number in only one record for each tutor.

                           

                          Here's the detailed description of how you do this:

                           

                          Define Maximum Spaces in the tutor table.

                          On your layout, enter layout mode.

                          Double click the Course Tutorial::Maximum Spaces field to bring up the Field/Contorl | Setup.. or Specify Field dialog (Which you see depends on the version of filemaker you use.) Select the Maximum Spaces field from the Tutor table and click OK.

                          When you select or enter the tutor record number, the maximum spaces number as stored in the Tutor table for this tutor will automatically appear.

                          • 10. Re: Trouble with Value Lists and Relationships
                            beckles88

                            The trouble is that I need the information to be per course and type and I can't really evaluate all this information on the tutor screen.

                            • 11. Re: Trouble with Value Lists and Relationships
                              philmodjunk

                              That shouldn't be a problem. It will look exactly the same, but you won't have to do anything to copy this data over from the parent record to keep it correctly updated. This is a basic built in feature of relational databases. You put such data in one and only one place and then link to it from everywhere where you need to see the data.

                               

                              Have you tried doing this just to see what happens?