How to maintain a Many to many relationship?

[rodrigopfraga]

Hi,

I have a common many-to-many relationship:

One Class day has many Students, and one Student participates in many class days. 

The tables are: Class, Student, and StudentClass (which I need to track the frequency).

The question is: How to auto-generate a list of StudentClass while the User sees a Class day detail to flag if the Students are present or not?

I know, by default, the user could be adding Students one per one and flagging it, but this is not useful/UX fail.

I tried to create an action using the option: "add a new row to another table using values from this row"
but I have the Student Id only or the Class Id only.

Any helps?

[Suvrutt_Gurjar]

Yes it is possible in AppSheet with two approaches - a bridge table or enumlist with base as reference.

Please search the "Q&A" section as well as Tips and Tricks section. There are many relevant posts.

Search - Google Cloud Community

[rodrigopfraga]

Hi, 

I can't see how these approaches solve my need: to auto-generate a list of StudentClass while the User sees a Class detail to flag if the Students are present or not.

Consider this screenshot:

 

This is the desired UI, which the User can flag with a custom action, if present or not, in the table StudentClass.

To show the custom actions, I needed to list all the available students, in a Class day detail.

The problem is, with a "add a new row to another table using values from this row" action, I have the Student Id, but I don't have the Class day Id, despite being in the Class day detail.

How to get the "row id" in a detail view? 

[Suvrutt_Gurjar]

---

@rodrigopfraga wrote:

I can't see how these approaches solve my need: to auto-generate a list of StudentClass while the User sees a Class detail to flag if the Students are present or not.

---

Sorry that your shared screenshot is not helping much. If you could elaborate above statement we could see if there is a solution to your requirement.

Also if you could share the relevant columns that are especially used in referencing in the three tables and any other major columns we could possibly provide a solution.

[rodrigopfraga]

OK, I will share in the details all the community need.

DATA STRUCTURE:

Class

Id	CourseId	Date
1	1	02/01/2023
2	1	02/02/2023

Student

Id	Name	--
1	John
2	Mary

 StudentClass

Id	ClassId	StudentId	Present
1	1	1	True
2	1	2	False

Very common many-to-many relationship, right?

UI REQUIREMENTS: 
When the user opens the details of class, he can also maintaing the Frequency of all Students Present in the Class day, checking if Present or not (average of 50 people).

THE SOLUTION I TRIED:
1) I created a virtual column in the Class to list all Students related by courseId;
2) I created two action buttons (Presence true/false) which is visible for the table Student.
When the user clicks on it, it will "add a new row to another table using values from this row"  in the table StudentClass. 

The problem with this solution is that I can get the StudentId, mark as Present=true/false, but I don't know how to get the ClassId, even I being the Class detail view.

EnumList would work if I maitain a list of Students in the Class table. But I have problems:
#1 I need to store the present=false too; and
#2 it's not user friendly to show 50+ options in a dropdown like.

Thanks for any kind of help.

[Suvrutt_Gurjar]

Thank you for the details. I am sorry that I am still not totally clear what you are trying to achieve, especially with those actions. Maybe someone else can help.

However , if it helps, you could get the number of present, absent and total students by the following expressions in the Class Table in three  virtual columns

Present student numbers: 

COUNT(SELECT ([Related StudentClasss][Id], [Present]))

Absent student numbers: 

COUNT(SELECT ([Related StudentClasss][Id], NOT([Present])))

Total Students:

COUNT([Related StudentClasss]) 

Where [Related StudentClasss] is the reverse reference column in the Class table.

[Present] is the Y/N type column in the  StudentClass table.

{Id] is the key of the  StudentClass table.

[rodrigopfraga]

Thanks for sharing the counts, but before that, I still need to generate StudentClass records, and that's my need.

[Suvrutt_Gurjar]

Maybe I am still missing some point. If the tables are set up with proper reference relationships, the Class table detail view will show the associated Students for that class.  The tick and cross icons are for present and absent actions. The ClassId is the label of the Class table ( Concatenation of ClassID and Date. In the backend the ClassID gets stored,

The image below shows the present and absent marked for a particular class. in the StudentClass table.