Two Value List problems that seem like they should have easy solutions

[skula]

First, the background. I'm the registrar for a "Monday School" for homeschooled children. Our organization offers about 6 classes every hour for 5 hours. I have 5 value lists, one for each hour, that lists the classes offered that hour. On my first layout, I have general student information (first, last, address, phone, emergency contact) AND the classes they've signed up for (I just set up radio button lists in fields called "Hour 1 signup", "Hour 2 signup", and so on, each of which contain the respective value list, so I can click on the classes they want).

NOW: In my second layout, I have a field called "Class Name Hour 1" and a second called "Class Count Hour 1", which is supposed to be a calculation field that matches the contents of "Class Name Hour 1" with the "Hour 1 signup" field from the previous layout, and gives me a total count of how many students are signed up for that one. That is, I want to have a number automatically generated, reflecting for how many records that class is selected in the "Hour 1 signup" field on layout 1. I have tried several calculations, but none of them have worked. So I need your help:

How do I do this?

ALSO: In my third layout, I simply want a table that has a column for each of the class names and then returns a list of student first & last names who are signed up for each one of those classes.

How do I do this?

Thanks in advance!

[Søren Dyhr]

Your description doesn't honestly give an impression of an extremely structured solution ... but as I see it should all the assignments go into a separate table instead of being valuelist in individual fields. Otherwise will the counting get pretty spreadsheet'ish.

Couldn't you create a small template showing where and why your fields are chosen the way they are?

With your brief description does it seem like you strucurally shouldn't be too far from this:

http://fmforums.com/forum/showpost.php?post/297553/

...where it deals with doctors and midlevels registration of time used each day. Could yours be somewhat shorter one line fore each monday school hour ... btw the way the template works is to put in a ID number for the employee and select the month and make the required registrations.

The reason for this approach is that all things land in the same table for sub summary purposes, and still in the layout provide a grid where spaces can be left empty ... since they even in the medical trade not works 24/7 ... similar could it in your situation give room for limited participation, as well as full.

What you see in the template is only provisions for putting in hours worked, but this doesn't make the overall structure wrong for your purpuse. You could here put in the ID for the classes or even the course name. But structurally should it be the ID to prevent redundant storage, the subsummary reporting vill still work.

--sd

[skula]

I'm sorry to say this, but I can't follow your answer. Here's some extra info, though, that may help you and others guide me in a way that I can "get":

1) I have the "hour 1 preference" (and so on) fields set up as value lists for ease of data entry when students turn in sign-ups for classes. I can just click on the buttons corresponding to the classes they want, and go on. I suppose I could set up a separate field for each class, but I'd ONLY want a check box in that case (checked would equal 1, unchecked 0). That way it would be easy to create a summary field that totals up the number of records that are checked for each class. Is it possible to have a field that only consists of a checkbox, and if so, how do I set that up?

2) My second layout is actually part of a second, related table. In that table & layout, records are created for each CLASS, not each STUDENT like the first layout/table. I want my info organized by class for this one.

2) I have succeeded in a kind of halfway step to doing what I really want. In my first table, I created a calculation field called "Emerging Lit Math Count", for one of the classes offered in hour 1:

If (Hour 1 preferences = "Emerging Lit & Math"; 1; 0).

Then I created a summary field that was the total of "Emerging Lit Math Count". So through this method I can get a count of enrollees on a specified class.

However, when I tried to apply and broaden this calculation to my second table/layout, it didn't work like I wanted it to. In this table, I have a "Class Name" field (text), and I tried to have the calculation field "Class Count" automatically search for the right entry in the other table rather than me having to separately create a "count" and "summary" field for each class like I did above. So here's the calculation I tried:

If ( Class Name = studentinfo::Hour 1 preferences or studentinfo::Hour 2 preferences or studentinfo::Hour 3 preferences or studentinfo::Hour 4 preferences or studentinfo::Hour 5 preferences, 1; 0). Then I created a "Class Total" summary field which was just the total of "Class Count". So the idea was

Now I just get a 0 all the time. How can I get an accurate count? This seems like it should be so easy... look up how many records have this value and give me that total.

Again, thanks for the help.

~Stacy

[Søren Dyhr]

I created a calculation field called "Emerging Lit Math Count", for one of the classes offered in hour 1:

If (Hour 1 preferences = "Emerging Lit & Math"; 1; 0).

Why not use a spreadsheet for this way of reasoning?

Why do you relate files the way you do? Educated guesses or divine inspiration?

--sd

[skula]

Educated guesses. That's all I got at this point. I really don't know the program that well. The last time I worked on Filemaker Pro was about 15 years ago... version 3, I think. As is the case now, I didn't have a manual or anything, and just went on basic trial-and-error.

I went to Filemaker Pro rather than a spreadsheet program like Excel for many reasons; the registration & class lists is only one application of the program to our general record-keeping efforts at the school and basic spreadsheets are not going to cut it. I know my computing logic is very basic, but it just seems to me that there should be a solution to what I want to do that wouldn't require tons of advanced expertise. It seems like there should be an easy way to create class counts based on the values on student enrollment records.

I appreciate your help very much.

[Søren Dyhr]

It seems like there should be an easy way to create class counts based on the values on student enrollment records.

Indeed, but trying to let an arbitrary chosen structure do it for you is perhaps a bit far fetched?

I have asked if you could provide us with a tiny template showing what you think is the structure ... what you have brought in until now is kind of paint by numbers. I know how i would approach an enrolment solution, but the clues you have come up with until now ... seems to me inadequate and opposite heading. Couldn't you at least make a screen dump of your relational structure to eliminate the worst guesswork.

--sd