cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gjayne84
Level: Powered On

Pupil & Class app (related SharePoint lists)

Hi everyone

 

I have a requirement where an app must be created so pupil details can be kept as well as class details.  I have setup 2 lists in SharePoint, one called Pupil and one called Class.  However, one thing that's required is that when a pupil is added to a class the teacher can add notes to that pupil either directly in the pupil record, or using the class form.

 

I'm not sure of the best way of achieving this?  I was thinking of creating a third list called Class Pupil which would have three fields - lookup to Pupil, loookup to Class and a notes field.  This could then be added as a repeating table on the class form so the teacher could add pupils to the class, and then any notes on each of those records could be viewed from the pupil form due to the pupil lookup.

 

I'd be grateful if anyone has any thoughts of how this can best be achieved.  If you need anything clarifying please let me know.


Accepted Solutions
Super User Rank
Super User

Re: Pupil & Class app (related SharePoint lists)

Hi @gjayne84,

One of the reasons you haven't gotten any help is this post is not in the right part of the forum.  It should be in either the Creating apps or in the General section.  In any event, you are on the right track.  What are seeing is a classical Many-to-Many relationship between tables in a data base.  One Class can have many students and one student can have many classes.  You have come upon the correct solution, that is to create a Junction table between the two.  You could refer to it as in the example below, Enrollments table. 

Capture.PNG

Later, when you want reports, you select a record from the one side and have the junction table as a gallery that shows the Many side with a lookup to the third table.  For example you might have on one screen a Dropdown1 control with the Items property of the Students list.  Below it would be a gallery with its Items property as Filter(Enrollments,StudentID=Dropdown1.Selected.ID). In the Gallery would be a label with the text property Lookup(Classes,ID=ThisItem.ClassID, Title).  The gallery would show all the classes that a particular student took.  Likewise, it could be reversed and the Dropdown could be Classes, the gallery would be Filter(Entrollments, ClassID=Dropdown.Selected.ID and the label lookup would be Lookup(Students,ID=ThisItem.StudentID, 'Last Name').  This set up would give the list of students enrolled in a particular class.  As you surmised, comments on each student would be added to the Junction table.  So would the students grade.   An internet search on Many-to-Many relationships would give you more examples.  You pretty much arrived at the answer on your own, but if this post helps you achieve your goal, please mark it solved. 


All Replies
Super User Rank
Super User

Re: Pupil & Class app (related SharePoint lists)

Hi @gjayne84,

One of the reasons you haven't gotten any help is this post is not in the right part of the forum.  It should be in either the Creating apps or in the General section.  In any event, you are on the right track.  What are seeing is a classical Many-to-Many relationship between tables in a data base.  One Class can have many students and one student can have many classes.  You have come upon the correct solution, that is to create a Junction table between the two.  You could refer to it as in the example below, Enrollments table. 

Capture.PNG

Later, when you want reports, you select a record from the one side and have the junction table as a gallery that shows the Many side with a lookup to the third table.  For example you might have on one screen a Dropdown1 control with the Items property of the Students list.  Below it would be a gallery with its Items property as Filter(Enrollments,StudentID=Dropdown1.Selected.ID). In the Gallery would be a label with the text property Lookup(Classes,ID=ThisItem.ClassID, Title).  The gallery would show all the classes that a particular student took.  Likewise, it could be reversed and the Dropdown could be Classes, the gallery would be Filter(Entrollments, ClassID=Dropdown.Selected.ID and the label lookup would be Lookup(Students,ID=ThisItem.StudentID, 'Last Name').  This set up would give the list of students enrolled in a particular class.  As you surmised, comments on each student would be added to the Junction table.  So would the students grade.   An internet search on Many-to-Many relationships would give you more examples.  You pretty much arrived at the answer on your own, but if this post helps you achieve your goal, please mark it solved. 

gjayne84
Level: Powered On

Re: Pupil & Class app (related SharePoint lists)

@Drrickryp

 

Apologies, I've just seen where I posted it and not sure how I managed that :-)

 

Thank you for your help and I appreciate you confirming that I'm on the right track with this.