Confused Relationship Beginner

[jmattw24]

Hello All! I am in a mess! I have volunteered to help a school principal friend of mine develop a relational database to track school discipline. I think I have a good grasp on the fundamentals but am not sure. I have created 5 files (students,teachers,rules,punishment, and referral) Obviously the student and teacher file holds all the student/teacher demographics as well as the rules hold the rule_id, rule_name, Rule_descript. The referral table/file is what I would call the main/master file (where most of the action takes place). It is where the principal will record a "referral" from a teacher that a student has broken a rule. I have linked the tables/files by student_id, rule_id, teacher_id, Punishment_id. This is where the confusion comes in...I create a layout to enter the referral, but the only way you can get the student info to come up automatically is if you know the student id as well as with the teacher_id, punishment_id, rule_id etc. how can i set this up so that I am following db theory in keeping apples in one file and oranges in another? Also how could I set it up so that there is a layout in the referral file that allows you to enter a new student into the student table as well as with the teacher, rule, punishment without leaving that referral file. Maybe this isn't the right forum to ask but I was unsure of the subject. it is probably very simple in your minds, but only working with the product for 2 days has me a little frazzled. Let me know if anyone has any suggestions..Thanks!!!

[LiveOak]

What was your first clue that you volunteered for more than you can handle? . This is pretty ambitious for two days in.

It's hard to know where to start. You can create new records as needed by using scripting to take you to the appropriate file, allow the user to create a record, and script back to the original file and bring the ID needed to link to the new record.

You could also use a Portal showing all the say Teachers with the relationship option to "allow creation of realted records" to enter a new teacher. It's just that the portal format somewhat limits the amount of information you can enter and have a good looking presentation.

The best idea is to enter all the student, teacher, and rule information first. Then in the referral file, use value lists showing both say Student ID and Student name for student selection, Teacher ID and Teacher Name for teacher selection, etc. The ID's for Student, Teacher, Rules, and Punnishment are all you need to create a referral. The rest of the information will be filled out be displaying related fields from the other files.

If this doesn't make any sense, you are just going to have to spend a little more time reading and working with FM. The book by Coulombre and Price and Scriptology by Matt Petrowski and John Mark Osborne are both good.

I'm afraid buying the tool kit just doesn't make you a mechanic, regardless of what the marketing folks say. If you can get a little local hands-on help, it would be worthwhile.

-bd

[jmattw24]

Thanks for replying! Just to let you know. I tried the scripting with a little success but not complete success. I could not get the file to open to a certain layout. For instance you are on the Referral file, a student walks in- you don't he is not in the database of students because he is new. So I have button Enter new student. The button needs to take me to the student file (which is probably already open because when you open referral they all open)and to the student entry layout. I could get it to open the file but not to go to a certain layout. Now you have to get back to referral from the student file so you need a button to do the same thing. As far as using the value lists I am doing this with the teacher,punishment but the only way the value list will come up is if you put in a ID which no one will ever be able to remember for every item. ARGHH this is frustrating! I could send you the files so you could see the mess for yourself.

[LiveOak]

First, your script in the referral file should look like:

Allow User Abort (off)

Set Error Capture(on) <--- kind of standard practice things

Perform Script (external, Students,"<your script name for new record script> )

In the Students file:

Allow User Abort (off)

Set Error Capture(on)

Enter Browse Mode

Go to Layout (<name of entry layout> )

New Record/Request/Page

The process to get back is similar, a script in the file you're in calling a script in the file you wish to go to.

As to the value lists, you have three options for a value list: 1) specify the list, 2) use values from a field, and 3) use value list from another file. You want to use 2) and specify the Students, Teachers, Rules, etc. files. When you do this you will get a dialog allowing you to show two field. You want to use (in the case of a student) the ID field AND the Name field. You can also specify the list be sorted by Name.

-bd

[jmattw24]

Sorry, but how do I create this script. Is this AppleScript or VB or what? Using Scriptmaker inside Filemaker-I don't see how or where to do this. Do I need to just do this in a text file and Import it or what? Sorry to be dumb I have just never done this.

[LiveOak]

Scripts are not created in VB or AppleScript, but are a feature internal to FM. Scripts are created by pulling the Script menu down to Scriptmaker..., typing in a script name and clicking the "Create" button. In the ScriptMaker dialog, click "Clear All" and then insert script steps by double clicking a step in the script step list.

-bd