My first relational database and I need help

[smith]

Educational database - I have created files. One of student information; one of a list of colleges; one of student SAT results. I need to set up a relational database which allows me to record what colleges each high school student applied to and the result of that application (accepted, denied, deferred). What would be the relationship of students to colleges - would it be many to many since one student can apply to many colleges; and one college can have many student applications....can anyone help? thanks.

FileMaker Version: 6

Platform: Windows 2000

[Ender]

Welcome smith!

Good questions. Whenever you design a database, you should make sure you have a good understanding of the relationships involved before spending too much time building the files.

For every item you may want to relate, you should ask the question, "Could there be more than one of these for a record in this table?" If yes, then it's a "Many". If you think you have got a many-to-many relationship, then you should try hard to fit in another entity between them. In your case, "Application" is an entity that seems to fit between Student and College. There are rare cases when a real many-to-many is best.

It seems to me, only the highest score for the SAT is important, so I put SAT as a field in Student, but if it were important to keep a history of all SAT attempts, then this could be done with another entity off Student.

Hope that helps.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[Fenton]

You've got Colleges, you've got Students, and you've got Applications. The Applications file functions like a "join" file (table in v.7), besides being an "entity" (thing) of its own. It always contains a StudentID, a CollegeID, a date, and the type of application; it would probably have its own serial ID also, but mostly for internal use.

Yes, there is a many-to-many relationship between Colleges and Students; but only through the Applications file. In v.7 I believe it can go directly through; but in v.6 you would need to "tunnel" data from Students, to allow it to show in a portal in Colleges, and visa versa for College data to show in a portal in Students. The latter being more useful

This is done with a simple calculation field in Applications, accessing the data via a relationship back to the parent file, using the foreign key CollegeID.

_cCollege = CollegeID::CollegeName

[stanley]

Smith:

Your Applications file is the key to your solution. Each record in that file must have the Student_ID number and the College_ID number as well. From that, you can track which student has applied to which college, and which college has been applied to by which students...

-Stanley

[smith]

Thank you all who responded...this is not my forte, so I really appreciate the help!

[smith]

I have followed your advice and created files - where I"m stumped is that I need to have the guidance counselors fill out the Application information and make it easy for them - how do I get a list of the colleges to enter in the Applications file - it's too large for a value list - would I use a look up? This is the very first relational database I've ever done, so please excuse the inaccuracies.

FileMaker Version: 6

Platform: Windows 2000

Test.zip

[smith]

I've taken your advice and created the three files, actually four and tried to pull them together. What I need is a place for the counselors to enter the application information for each student and I don't quite know where to put that - I thought in the application file - how do they enter the name of the college so it matches up with the name in the college file? I'm sorry this is so basic, but it's the first realtional database I have ever done and I'm brand new at FileMaker, so I'm stumbling. Thank you.

FileMaker Version: 6

Platform: Windows 2000

Test.zip

[Fenton]

You're right that there is a problem choosing the College when there are so many. The technique I use is to pop up a window of the College file, to a special layout only reachable via a button in Applications.

That way you can find and see which college. Than a button sets it into a global, and calls a script to set it into the current application.

In real life I don't use just a regular window. I use a "filtered" portal. And I trap the user in a Loop/Pause script, so they can't wander away. That makes it "safe as milk."

Unfortunately I don't have time to set that all up at the moment. I'm supposed to be finishing a solution (and paying my taxes, and ...). But here's the basic mechanism.

CollegesTest.zip

[smith]

THANK YOU ! I can't believe you did this!

FileMaker Version: 6

Platform: Windows 2000

[Fenton]

You're welcome. It wasn't all that much. Your files were simple and straightforward. I find this technique very useful; I've done it many times, could (and probably) do it in my sleep.

If you want a more "foolproof" method, with locking, send me a message. It's quite a bit trickier however; so if it's just you using it, what you've got may be safe enough. Just be aware that it will set whatever application is the active record; so you don't want anyone wandering into this "Choice" layout and clicking the button to see what happens.

[suchanfmnewbie]

Hi -- I am jumping on this post here, since it is also my first time creating a database and ...a relational database at that, and I have looked around at these posts, but am still stumped! Any help is greatly appreciated. I am setting up a relational database for my school for their checkout system, and have two files. I am using Filemaker 4. So I tried to make a relationship between the last names in the two databases. I could also do it with student id's, but niether seem to work. I set up my relationship (between the database student info and checkout), then drag the fields into the main database (the checkout), but what happens is that when it is a relationship for a last name, I get 'field can't be modified until I give it a valid value...,' hunh? When I make the relationship with the student id, it also says field can't be modified until I give it a valid value. I have also tried to make a portal, but I don't think I understand portals since I hit the portal button, drag the box over, then the program gives me specific options, saying 'clicking on selected objects will...'. I pick go to a related record, then define relationships, select the student id relationship....then it is just a button, and it will open my second file, but won't recognize any of the records there, so if I have 20 records, it only 'finds' 0.

Sigh. What am I doing wrong? How can I make it so that when users type in a last name or an id in the checkout file, the related info in the student file fills in the last name, first name, student id and phone number? Can I even do that on Filemaker 4?

Again, any help is appreciated...and I apologize in advance if I am posting in the wrong area.

[Fenton]

You're on the right track. When you finally see how relationships "relate," then it becomes clear. Basically a field in one file equals a field in the other file. For what you want to do the field in Students must be unique. There are different ways to do the data entry, depending on where you do it from.

Here's a little file with the basics. I had to go find version 4 on my other computer. That version 4 was a lot simpler than 7 -)

Student Checkout.zip

[suchanfmnewbie]

Fenton! Thank you so much for your examples -- so I basically tried to take your example and make my relations work, but I STILL can't make it happen. I am attaching what I has so far (pass/both = eas), because somewhere along the way I messed up the relationship between the two. I feel like you've done so much already, but again, if you wouldn't mind looking at where I am at right now, that would really help!!

Thanks so much!

SuchanFMnewbie.

Checkout and Inventory.zip

[suchanfmnewbie]

Oops, here is the second one.

Student Info.zip