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.
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?
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?
Thank you for your help, that is correct and the Tutor is linked by 'Tutor Name' to the Enrolled Courses table.
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
'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.
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?
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.
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.
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.
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.
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?