value list dependant on another field's value how?

[m4trix]

I'm working on a database for a school. I have a student database, and now I'm making a separate database for marks. Rather then having a separate one for each grade (as was the previous method) I have decided to put them all in one.

The report cards have different courses for each student (since they can choose their courses). I want to only display the marks and headers for those subjects that each student takes. To start, I was thinking of having a bunch of checkboxes listing the courses available for each grade. That way for each student, the courses that apply to them can be checked off. But I don't know how to display different value lists depending on the grade the student is in. That is, not all courses are available in each grade. How can I have filemaker say "hey,this student is in grade 11, so I'll display the value list for that grade". There is a field called "Grade" which contains their grade.

Secondly, but less important at the moment, how can I display on the report card ONLY those courses which are checked off for that student?

Thanks

-mike

[Helpful Harry]

I'll answer the second, easier question first. On the report card layout, go into Layout Mode, click on the course field and then choose Format > Field from the menus and change the field's format back to "Standard Field". Hey presto, one field that lists all the values for the record that had the checkbox turned on.

Checkbox or Radio Button fields are just standard text fields for which FileMaker handles all the behind-the-scenes data entry for you. When a user turns on a checkbox that value is added to the end of the text field. When the user turns off a checkbox, that value is removed from the text field.

The only problem is that the standard text field will display the checkbox values in whatever order the checkboxes were turned on in, not alphabetically or in the same order as the value list. If you want the entries in a particular order then you'll need to write a your own Sort script to do that.

OK, back to the first question. This one is slightly harder.

[color:"red"]ALWAYS make a backup before playing with files!!

The easiest way is to use a second file to store all the course names. This new file (eg. CourseList) only needs two text fields: Grades and CourseName. In this new file create records for each course and in the ListGrade field enter in all the grades that can have that course (making sure that they are each separated by a Return so they're on their own line). This way "Mathematics" may be available to four different grades, but only needs one entry in the file.

Hopefully your Grade field in the original file is defined as text fields since this will make it MUCH easier to operate (if not, it's probably best to change it).

In the original file, create a relationship link to the new CourseList file which links records in the original file to the new file when Grade = CourseList::Grades.

Then change the current Course field's value list to "Use values from field" and "Only related values" via the new relationship link and using the CourseName.

All done!!

Now when the user crates a new record, the checkbox field will be empty until they enter a Grade. Once they type in a Grade (and move out of that field), the checkboxes will appear with only the Courses available to that Grade. If they make a mistake, then changing the Grade will also change the list of checkboxes.

Unfortunately FileMaker has some peculiar display bugs that sometimes mean the checkbox field won't update properly. There's not much you can do about this except move to another record and then back again.

[m4trix]

Thanks a lot for the reply, however, I couldn't get it to work. I think that it has to do with the fact that I'm using such an older version. It doesn't have the "only related fields" checkbox for example. Sorry about the length of the rest of this:

I tried some other stuff, which I'll get to, but if you're willing to read this, this is exactly what I want:

There are grades 7 through 12. Grades 7 - 10 have fixed courses, and grades 11 and 12 have some optional ones. This becomes important when it comes to report cards. the courses are listed horizontally, with various marks shown below. This is fine for grades 7 - 10. If I made a different layout for each, that would be an easy solution. However, for grades 11 and 12, as the system is right now, all the courses available are listed, and marks only appear for those courses that that student took. This means there are large white gaps appearing between the course "columns" for courses not taken by that student. What I would like to do is only list those courses that the student is taking.

This would make the reports look much neater. In addition, there are about 15 fields for each course. That works out to an awful lot of fields per student, most of which aren't used. So, What I was hoping to do, is have say 9 generic sets of course fields, label them course 1 through course 9 for example. I figured the easiest way to do it would be to select the courses each student took on their record, and using that list, distinguish each of those 9 generic sets of fields from eachother... hard to explain, do you see what I'm saying?

So here is an example of how I envision it working, though the only important things are entering grades and the final reports:

A new record is made for "bobby". His grade is entered, and that allows for the selection of x number of courses from a list of courses available to him (based on his grade). Let's say he takes eng, math & science to simplify. Now 3 sets of fields take marks for each of the three courses. So all the "course 1" fields pertain to eng, and I suppose would be linked by name (ie "course 1 name" = eng) or something like that, and all the "course 2" fields link to math, and so on. So once all the marks are entered, the user switches to the report card layout, and it displays the courses horizontally with the corresponding marks below. I guess it would be like

"Course 1 Name"---"Course 2 Name"---"Course 3 Name"---"Course 4 Name" ... etc.

"Course 1 Mark"-----"Course 2 Mark"-----"Course 3 Mark"-----"Course 4 Mark" ... etc.

so the previous example would appear something like:

English---Math---Science---(blank since it doesnt exist) ... etc..

--100--------99---------98-----------------(blank) ... etc..

as for the other stuff I tried, so far I tried creating a global field list for all the subjects available to each course. Then I created a "course" field which was a calculation that used the case command to return the global field for grade 11 if grade=11, return global field for grade 12 if grade=12, etc. It works fine, but I don't know how to store whatever values are checked for each record. I tried creating a field that got it's values from the "course" field (which got it's values from a calculation), but all I get is "[] <index missing>" so it seems like a dead end at the moment.

I'm stumped

[Helpful Harry]

The method above works in FileMaker 5.5 and should work in 6.0. What version of FileMaker Pro are you using, 4.x??

[-Queue-]

If you need your checkbox values sorted, try this.

[m4trix]

harry, I'm not quite that lucky! :

I'm stuck with FileMaker Pro 3.0!

[enzo01]

Hi,

FM 3.0 is very limitative. You can get a value from a different file, but surely not a related value.

It's hard to say, surely tedious, but a workaroud would be to have 12 additional fields in each record of the Course File.

Obviously, each of these fields would be populated or not with the Course Name according to the Grade.

Call them t_MatchGrade1 to t_MatchGrade12.

There may be an easier way, but you could therefore have 12 value lists (each pointing to one of the 12 t_MatchGrade fields), attached to 12 temporary global fields, and a script that would go to a specific global according to the Grade, and then Paste (SetField in FM 3.0 to the Course field selected.

[Vaughan]

FMP 3 was the first version to offer relational capabilities. Version 4 added a few more and that's the minimum I'd ever use.

Make sure you're using FMP 3.0v6, where 6 is the patch level. Patch updates are free from the FM Inc web site. Any version before 3.0v6 is not Y2K compliant -- that's now old version 3 is!

[Helpful Harry]

I haven't got FileMaker 3 any more (only 4.1 and 5.5), so I can't double-check what is and isn't able to be done with that version, but ...

From just the above description I can't see any need for a related file at all - unless you're wanting to keep records for the same students over different years.

Either way, you seem to be on the right track. The easiest method looks like it would be to have the nine separate Course fields (Course_1 ... Course_9) and the nine separate Marks fields (Mark_1 ... Mark_2). The Course fields could all use the same simple value list (just a typed in one, not a "from a field" one) of all the courses. Instead of Checkboxes, set-up the Course fields as pop-up menus/lists for the user to choose an option from - it'll use MUCH less screen room. These could also be auto-entered with data based on the Grade for those grades that have standardised courses. They could also be validated so that a user can't choose a course that isn't available for the particular grade.

If you are wanting to keep historical data, then the above would need to be in a separate Marks file related to the Students file by a unique ID, preferably an auto-entered serial number, but at least unique. You'd need to print the reports from the Marks file using another relationship link back to the Students file to obtain the student's name, etc.

Something along these lines:

• Students file
  ID
  First Name(s)
  Surame
  Address
  Phone
  Birth Date
  Parent/Guardian Name
  Parent/Guardian Phone
  ...
  (Portal displaying related yearly data from Marks file)
  (Layout for printing mailing labels?)
  
  Marks file
  ID
  Year
  Term
  Grade
  Class Number
  Teacher_1 ... Teacher_9
  Course_1 ... Course_9
  Marks_1 ... Marks_9
  ...
  (Layout for printing reports)
  (Layout for printing mailing labels?)

Each file can have a relationship link to the other using ID (an auto-entered serial number in the Students file).

This way the report layout becomes your simple:

• Course_1....Course_2....Course_3....etc.
  .Mark_1......Mark_2......Mark_3.....etc.

set out however you want.

Data entry of the Marks can be done in the Marks file - find Course="Maths" and Year="20xx" (and perhaps Term="y") and then sort the entries by the related Students surname and flick through a list view entering marks. When all the marks have been entered from the various courses, you find Year="20xx" (and perhaps Term="y") and simply print the whole lot in one go from the Marks file.

[m4trix]

wow! thanks for all the replies. you guys have been great.

I've basically solved all my problems except for the courses depending on grade thing.

What about grades 7 to 10.

I have fields course1 name, course2 name, course3 name. etc.c

can I make it so that when the user enter's a student ID #, it pulls up the student's grade # from the student db, and then fills a name for each of those fields from the values in a value list named "GradeX" where X is the grade #? I KNOW I can do this simply by placing a button and running a script, but Is there a way to make it automatically run a script as soon as the user enters data into a certain field? I wish there was the equivalent of the javascript "onMouseOut" event or something, so as soon as the data is entered, it triggers a script.

and I wish there was a way that you could change a value list for a field depending on another field. I don't think you can do that even through scripting. This is one of those things that bugs me about filemaker

That and the fact that if I have a 2 page report, and someone is typing in a textbox on the first page, and types enough that they fill up that text box, there is no way to link the bottom of that box with the top of another one on page 2... Or if there is a way to do this, I havn't found it. Many text editors I've seen allow this.

[Helpful Harry]

You can't run a script on exiting a field in FileMaker, unless you count (expensive) plugins or awful, kludgy workarounds.

If the grade is already stored in the Student file, then there's no need to have it in the Course file at all - simply reference the field by choosing your ID relationship link from the pop-up menu in the choose a field window / calculation area.

Since you can't use "related-only value lists", you'll probably need to use seaparate value lists. Because you're using such an old version, some of your "problems" would disappear in a newer version - after all, FileMaker 3 was the first ever relational version and things have improved since then (FileMaker 6 is the current version, so you've missed 4, 4.1, 5, 5.5 and 6, plus a number of bug-fix upgrades).

Anything that's stretched over a page break (text, fields, lines) will simply get cropped to fit on each page - often cutting a line of text in half - unless you're very lucky with your positioning. I can't see how you can have a 2-page report (all my school reports were about the size of a cheque book, so you'd get three/four to an A4 page). The easiest way around this is to use two fields on the data entry layout that gives you the correct size for the printout.

[m4trix]

alright, I'll work it out. As for getting a newer version of filemaker, this is the version I was given My employer has 4.1, but he only has one license for it (and he's very strict about sticking to software licenses), while he has an unlimited license (or something) for 3.0. As for the newer ones, I dunno. He thinks that for our use, 3.0/4.1 should be enough. Plus, both he and I believe that most of the updates (not all of course) up to 6.0 are mostly for the web aspect of filemaker - which I personally see no use for, as I'm quite handy with php/perl/mysql which can be customized to fit almost any application.

Anyway, thanks a lot for the help. Really appreciate it

[Helpful Harry]

Not quite true. While there are many "web" additions to newer versions of FileMaker, there are equally as many (probably way more) additions to the rest of FileMaker as well - including new functions in the ScriptMaker / Calculation areas. If you're a education facility you will probably qualify for Education discounts on lots of software and hardware purchases, which would bring the prices down - although not by a huge amount for FileMaker.

[-Queue-]

From my experience just over the past month discovering the uses of GetField(), I would say that function in itself is an excellent reason to upgrade to at least 5.5! Of course, there are quite a few other nice, newer features. But this one takes the cake for me.