Help with many-to-many reference and bridge table

Hi
I have two tables.
“Students” and “Parents” which I need to use to build a detailed wiew on each other.
Each student may have more than one parent (Father, Mother, other), and each parent more than one student (brothers or Sisters).
So I set up a Bridge table “Families” containing each relationship.

Fristly I would like to have a Students view in which I can see all realted Parents:
so fields may be:
StudentId
StudentName
Parents-> ParentName
ParentRelation
ParentId

My Families Table Has two columns type ref
StudentId
ParentId

According to what I read, I create a Virtual Column In Students Table, type List with the formula
SELECT(Families[ParentId], [StudentId]=[_THISROW].[StudentId]).
Only get a list with the ParentIds.
How can I get a detailed view of all Parent Data for each student?
Or the only way is to add all Sudent Data and Parent data to the bridge table Families?
thanks
Claudio

Please explain what you mean by this.

Hi Steve
I am new to AppSheet coming from AppMaker.
What a mean is an Alum “View” like this:

StudentId
11223

StudentName
Tom Doe

Parents (list)

Parent id ParentName ParentRelation

  995      John Doe     Father ->
  996      Mary Smith   Mather -> (may when I click here I can get another more detailed view as follows,)

Parents View:
ParentId
990
ParentName
Mary Smith
ParentRelation
Mother
Address

PhoneNumer

IdNumber

Following this one… Many-to-many
Is what we’re taking about here I presume…

Yes that’s it, according to what I already read: many-to-many relations/references

To my knowledge in Appsheet the only “native” (reads easy) reference is a one-to-many. There have been various feature requests over the years for one-to-one, and many-to-many. However, since we can develop these into existence it’s never been a priority. (Hopefully someone chimes in and tells me I’m off base and we have an easy button now…)

Here is a prototype app to continue this conversation. (I’ve been meaning to take a stab at the school situation for awhile now). It is not meant to be a complete app but rather a starting point to continue this conversation! :slight_smile:

https://www.appsheet.com/samples/many-parents-to-many-students-to-many-classes-to-many-teachers?appGuidString=30f1b61c-6ba6-4d4d-b35e-97341c937970

Armed with the above data model and app, someone could now create:

  1. actions to help ease the navigation and creation of records
  2. workflows to observe the creation of records, and auto-create additional data as needed
  3. security filters to enforce things like
    a. a teacher should only see their own classes and students (in america this falls under FERPA laws)
    b. same as “a” but for parents and students.
7 Likes

Thank you all
I am going to try TyAlevizos sample.
Coming from AppMaker, wher we had another kind of control over data and objects, it is turning a bit hard to accoplish a full transition in some apps.
Claudio

1 Like

I took a look at your app (or prototype) and IT IS RIGHT WHAT WE NEED.
In fact there is a great job ahead, normalizing data structures etc.
We even might extend functionalities adding grades, timetables, class attendance, etc
I ve already copy your sample, thank your for sharing and contributing to our school domian.
Claudio

5 Likes

great, that’s awesome. Yeah, “grades” was next on my list… :slight_smile:

1 Like

Dude, all over it!

2 Likes

Hi, @Instituto_San_Pedro, I went ahead and added basic support for “Grades” as follows:

  1. Some additions:
    added “semester” to the class table. In this example, “classes” should be unique per semester, so you would want one row for “German Spring 2020” and a second row for “German Fall 2020” and so on (this is similar to real life and existing school data systems, btw)

  2. added images and icons to everything (that is not pertinent to the below info, just FYI)

  3. added a new sheet called “Grades” with references to students and classes.

Interesting trick:
when you create a new “grade record” you first select the class. Then, the system should be smart enough to only show students in that class. To do this in AppSheet, for table “grades” and column “StudentID” we use a valid if statement as follows:

`select(studentsclasses[StudentID], [ClassID] = [_THISROW].[ClassID])`

(you will note in my example app that only two students are taking the german Spring 2020 class, the above statement returns only those two students)

Because we started this experiment with a proper data model using bridge tables, we can now start to reap the benefits of our original efforts by putting in clever calculations like the one above with a minimum of effort. :slight_smile:

Best, -Ty

2 Likes

Great Ty
I will reinstall the updated sample version to take a look, I assume I have to do this whenever something is updated in your sample.
I even inspected Ref expresions and applied them to the prototype I am working on with real school data, and surprise, It is beginning to work!
My overall work was not so wrong, I had misused some expression due to lack of practice (first week on AppSheet)
Thank you Again
Regards
Claudio

1 Like

It looks great, good job!
I am starting to figure out how to customize our high school grade system:
we have (possible tables):
Students
Courses (1st year A, 1st year C, 2nd year A, etc)
Subjects (fixed yearly classes: Math 1st Year A, history 2nd Year A, English 3rd year B, etc)
Teachers
Year Grades (with some categories: job assigments grades (partial), Quarter grades, year grades (average of three quarters), complementary exams grades, etc. some of the need math calculations.
Historic Grades (those definitive grades stored each year the sudents spent at school)

Also in our other AppMaker app we had Students Emails, and Parents Emails, same as in this sample, but we also could select an entire course, compose and send an email to all students of this course, or all parents of this course, using a dropdown to select the course. I do not think I coud set up a table based dropdown and a Rich Text box to send a multiple recipient email with AppSheet, perhaps we will accomplish that using an AppScrip web app.

As you see a lot of work to be done, but we will do it step by step

Regards
Claudio

1 Like

“I will reinstall the updated sample version to take a look, I assume I have to do this whenever something is updated in your sample.”

Yes this is true.

“Also in our other AppMaker app we had Students Emails, and Parents Emails, same as in this sample, but we also could select an entire course, compose and send an email to all students of this course, or all parents of this course, using a dropdown to select the course”

Although it will look very different from a web UX in AppMaker, from a functional perspective, you can do the above in AppSheet. We also have ICS calendar attachment support. These are more advance items but not impossible.

1 Like

thanks
yes the multiple emails sending is something we would have to investigate how to do it in AppSheet.

1 Like

@Instituto_San_Pedro

Hello again,

Please copy the app one more time (same link above). There are changes, including:
*data model changes to existing tables
*new table “emails”, new view on the upper left menu for “emails”
*actions created, new buttons created
*three example workflows created
*workflow google doc templates created
*Some expressions and calculations created

All of these changes highlight the various declarative syntax of the AppSheet platform, for example, to get started with expressions take a look at


or

for workflows:

for actions:

And so on and so forth. Please note that this is just one way to create functionality like this in AppSheet. There could be other design patterns that make more sense, or, someone else could chime in here with alternative ideas.

At a high level, this email solution is based upon a) creating new email records in an email table using AppSheet “actions”, and then b) observing changes to this email data table using AppSheet “workflows”.

Instead of providing you with a detailed lengthy document or write-up, I would like you to study what I have done and see if you can independently understand it.

In an American college, the professor might say that this work “is left as an exercise for the reader” :slight_smile: But please do let us know, here, if you have questions or something does not make sense to your team.

Best, -Ty

4 Likes

Great Job Ty!
I promise you to study all functionalities and try them.
Now I am solving some isues with references and bridge tables in our own data models.
Simultaneously I will take a look all the new features you added and tech sheets.
Finally, of course, we will let you know our feedback
Thanks a lot.
Claudio.

1 Like

Hello @Instituto_San_Pedro, one more item on this general subject.

I have created a second app which is for the parents/students. This is not necessarily for your specific needs, but rather, for the generalized use case whereby a school wants to communicate with parents and teachers in a secure, scalable manner. This second app is located here:

https://www.appsheet.com/samples/read-only-interface-to-your-schools-class-ERP-system?appGuidString=1e06ab70-3b8a-487d-8566-8ee7ba875010

(again, just an example and you are meant to copy it).

Some interesting features of this second app include:

  1. it is a copy of the first app, however
  2. it points at the same google sheet as the first app
  3. all data is marked as “read only”. There are no edits for any record in this app
  4. All data is secured to the Parent. Each parent that logs into this app will only see the data for their students, their students’ classes, teachers, grades and assessments.

This last point makes use of AppSheet Security Filters (easily searchable phrase, btw)

Finally, for both apps, if you click the “about” menu choice, and then “more information”, you will land on the following google doc which is a brief writeup of this entire example, also located here:

Hope this helps! -Ty

2 Likes

Great Work Ty
It seems this app has empty tables, I suppose it is meant to be so.
I think I have to study a lot of filter expressions from what I saw in the app.

In the previous one (class bulk emails, etc), it turned a bit hard to grasp the workflow for email sending (speaking as user not dev), your School Model Documentation made it a bit clearer to me that emails should be composed, stored in a cue to be sent, changed or not, then marked as ready to send, and finally sent to recipients. Testing the app (previuosly entering some real emails) I unintentionally sent emails and transscripts (reports whit grades) and when I intended to do it I could not.

I will continue testing these, my own with real data, and figure out a slow transition.

Thanks a lot
Claudio