Input from one field into another field

[skula]

I'm creating a database for registration purposes at a school. Students of board members are automatically placed into their first choice classes, then teacher's children are given priority, then others. If enrollment for a class goes beyond its max limit, I want Filemaker Pro to randomly select from students whose "first choice" field contains each class name.

I currently have "first choice, hour 1", "first choice, hour 2", etc. on one layout. So here are my questions:

1) I will be creating a layout with fields for each class (ex: "hula") and want lists to be automatically generated with the names of kids whose first choice is in each of those classes. How is this done?

2) Can Filemaker Pro be programmed to tell when a class is overenrolled and randomly select the kids who get to take the class, placing the others in their "second choice" for that hour (that's another field I have already)?

As you can see, I'm in way over my head here. I've worked with Filemaker before (years ago), and I'm a fast learner, but I need to get my hands on a good manual. In the meantime, I hope you can help.

Thanks,

Stacy

[jwjay]

Your question is a little hard to follow, but I think I have an idea of what you are trying to accomplish.

I think you will need to do some of this with scripts.

I think you will need to use the Random function in a script (so it will re-evaluate a random number each time the script is run) and then I think you will have to sort by the random number generated.

I might be able to help a little more if you provide a little of your database structure. (Getting and giving help through a forum can be tough at times!)

[skula]

Thanks. I'll give it a go here, try to allow you some insight into what I'm trying to do. I'm going to tackle my first question only here, since that's step one for me.

I have a layout with fields for student information up top and down below their class preferences. There are five fields in this last section: one for each hour. These are entitled "hour 1 preference" "hour 2 preference", and so on. Each of these fields has a radio button list of the classes being offered at each hour (the lists are 4-6 items long) and I simply take the preferences the parents email me and click the radio button that corresponds to their course preference for each hour.

Now, I want to create a different layout that will generate columns of all the classes and a list under each class of student's names who have each class selected. I'm figuring I'll have to create a field for each class (for example, "Emergent Lit Math" "Spanish 1", "Cooking" and so on) but don't know how to program it to list the student names who have those particular classes selected. That is what I need help with. I don't need a count; I need a list of names.

Hopefully that's clear enough to allow you to answer my question...if not, let me know and I'll try to be more descriptive :-). Thanks in advance!

[jwjay]

I'm not sure of your skill level, so please don't take offense if some of these questions might condescending.

Do you know how to use Related Tables? Have you tried making a Student table and a Class table?

It sounds like your issue might be cleared up with properly related tables. (I don't have a whole lot of time right now but will try to revisit your issue later today or tomorrow).

[skula]

I just did... thanks for even that help. I was thinking of different layouts as different tables. Not so.

So now, I have one table called "studentinfo" and one called "classinfo". I have made the two related. In the "classinfo" table I have a text field for "class name" and I have tried to create fields for "class count" to start out with, but the calculation fields I'm trying are not working; in other words, I'm still having a problem getting a count and name list for each class.

I'm just not sure how to program the table to look up the total number of records from "studentinfo" that have selected the "class name" of the record I'm on in "classinfo". For example, if I enter the name "Emergent Lit & Math" in my first record of the "classinfo" table, I want it to look at all the records on the "studentinfo" table and tell me how many of the students are enrolled for this class. I also want it to tell me their names (that would be on a second layout in "classinfo", in a table format with columns corresponding to each class name... I don't know how to get it to do that either).

So now that I'm this far, if you have any ideas, please let me know. Thanks!

[jwjay]

I think you'll need 1 more table to connect the two (see the attached picture). This is an important concept that has helped me in the past. It's called a "one to many" relationship. A little info A little more

So you would have:

• studentinfo table
• classinfo table
• schedule table

The studentinfo table would have 1 record per student and could have your main layout where you would enter the student's choices.

The classinfo table would have 1 record for each class/period combination. If Emergent Lit & Math is offered in period 1 and 2, then you would have 2 records. You would have a period field and a class field (you could also create a 'parent' table to this class table that would summarize all of Emergent Lit & Math classes).

The additional table would include the combination of the student and the class preference. This would create the desired 1 to many relationship between the student and the schedule tables as well as the 1 to many relationship between the class and schedule tables.

The student table would be related to the Schedule table by the student's name or by a student ID, and the class table would be related to the schedule table by a class ID or by Class & Period. (when making the student to schedule relationship make sure to allow creation of records via this relationship for the schedule table)

On the student table you would then have a Portal to the Schedule table with the Class Period fields displayed. You can use a drop down list Value list) to display the choices from the Class table. This is where you would enter the student's preferences (1 portal record for each of their class preferences).

In the class table you would then have a Count ( field {; field...} ) function that would count the records in the schedule table. This would represent the number of students requesting that class. You could then use a Portal to the studentinfo table on the classinfo layout which would display which students have requested that class.

Once you have a chance to look this over and see if it makes sense we can discuss the logic behind handling an over requested class.

I hope this helps :

Edited August 24, 2009 by Guest
Additional Link for 1 to many