hmm... many portals

[Himitsu]

I am trying to put together a new sheet with some existing sheets. The sheets I have now contain student information, ie, name, phone numbers, class level, ect.. Ok.. first I own an English school in Japan and I am trying to tie everything in on one db. So I created one that contains the student information. Then from there, I created a report card sheet that creates a new report card ID# that relates it to the student id#. After, I created a billing sheet that creates an order for that student's monthly fee. Of course relating the student id#. that sheet contains a portal that pulls the "monthly package prices" from a another sheet "inventory file" very similar to selling something like the old FM templates used to use. Now... I want to create what looks like the order sheet but instead of the montly package prices and description coming up in the portal, I want the students who are in the same class. So the sheet will be the "class info" and the portal will contain all the students in that class allowing an arrow with a script "go to related records" next to each student name and ID#. Now, on the student sheet, I want a portal that will list that class. To me it seems like there are two relationships going on. One is the student ID# and the second is... "class invoice #"? or do I have to make a "line items" sheet and relate that too? and if so, HOW??? what to what??? I feel like a FM101 student working on a FM211 project...

[Fenton]

You need a file for "Classes," with ClassID and ClassName, and Description.

Then you need another file, a "join" file, with StudentID and ClassID; and probably date enrolled, date class starts/ends, etc..

Then it's fairly simple to have a portal in Classes, showing all Students enrolled in a Class (at the time anyway, hence the dates are needed if you want any kind of "history").

You can show most data in Students, 'cause the StudentID is in most files, as is the ClassID.

Showing all the other students enrolled in the same class could be handled in the Student file, but only by setting some redundant fields, and only if a student only took 1 class at a time. It would more naturally be shown in the Students_Classes join file, via a self-relationship on ClassID.

[Himitsu]

so the join file would contain it's own feilds for studentID and ClassID and not pull it from another file? so then I will set the "join" file relationship to "classes file" by ClassID and the "classes" file relationship would be set to "studentID" with the student file that already exists?

[Himitsu]

hmm.. I have tried just about everything... and I can't seem to get it to work. I have a studentlist sheet with all the student info. All I want to do is make a class sheet with a portal and through that portal, put in all the students in that class. Then on the studenlist sheet, I want a portal that will show that class. There for when the student moves to another class, all I have to do is add him/her to that new class, and it will show two class ids for that student. Sounds simple... but it is giving me headaches beyond belief...

[Ugo DI LUCA]

Class.fp5

Class_ID

Class_Name

Student.fp5

Student_ID

Student_Name

Classes_Ids

StudentsInClass.fp5 (your Line Item)

Student_ID

Class_Id

Student_Name (lookup from relationship StudentsInClass.fp5:Student_ID::Student.fp5:Student_ID)

Class_Name ( lookup from relationship StudentsInClass.fp5:Class_ID::Class.fp5:Class_ID)

Class.fp5 has a portal to StudentsInClass.fp5.

The portal will have a field Student_ID, involving a value list of all students_ids, and the relationship would allow "Creation of related records".

Student.fp5 has a relationship StudentsClassRel Student.fp5:Student_Id::StudentinClasses:Student_ID

You may have a portal to list all classes for a given student, or use the ValuelIstItems function.

Create a Value List "RelatedClassesList" using values from file StudentinClasses and relationship 'StudentsClassRel '.

Then, Classes_Ids = ValueListItems(Status(CurrentFileName),"RelatedClassesList") would give you a list of Class Ids separated by a Carriage return.

If you want this list as a one line field with commas, just use this new calc

Classs_Ids2 = Substitute(Classes_Ids,"

", ",")

HTH

[Himitsu]

Ugo... that sounds good... I will give it a try today and see what happens. I will post the results here after. Thanks a ton.

[Himitsu]

ok.. Ugo.. I did it. Worked good but now on the Student.fp5 I want a portal that lists all the classes that student has attended. I tried to refer it to the class.fp5 but that didn't work. and when I refer it to the line items, it shows the class ID so when I click on "go to related records" it takes me to the line items and not the class.fp5. How do I do this next step?