Help with many-to-many reference and bridge table

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

6 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

3 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

7 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

Hi There
This Covid-19 crisis and schools closure, has impelled us to rapidly develop a teachers App for sending dayly tasks to pre-school and elementary students parents. (Secondary School students already use Google Classroom with a G Suite Account per student and teacher). I followed your models and (using our existing data models) teachers can now compose an assigment, add files to it, and easily send it to each class (parent) they are in charge. All assigments are not deletable so principals may supervised them.
Although, it was necessary to make up a Google sheet with a trigger in order to get each file url to be albe to view it, since the different file types uploaded. Appsheet only shows image files, and prompts you to download doc, docx, pdf etc.
Principals can track all teachers online assigments in their own App.
There are many challenges left still. For example, I would like teachers to create a check list for every assigment they create. This would imply to add multiple records to a table “Students-Assigments”. Let say we have a class with 10 students, each time a teacher create an assigment we need an “Action” to add ten more records to the “Students-Assigments” Table. This way, the teacher may keep track of each student and his/her result, also recording grades or comments on each assigment.
To what I have read until now this is only possible via API. This exceeds way more my present skills.
I would share my project with me but tables are full of real data.
I am writing all this just to repeat my thanks to your time and sharing your projects.
Claudio
Instituto San Pedro

1 Like

Some other issues I forgot to quote in my previous post:

-Some tables (due to size and other dbms exports) are in csv format. I am having trouble with special characters (Spanish ñ, á, é, etc.) of which Spanish Names are full.
-Same when I perform an export csv action, special characters are not well translated.

I will take another look this week. Glad to hear about your progress!

@Instituto_San_Pedro can we arbitrarily enforce a rule like “all assignments will have 8 (or 12) check list items” - if yes, we could simply create 8 or 12 hidden actions. If on the other hand we need a variable NN amount of checklist items and this varies per assignment, that is harder.

From a human/business point of view, standardizing on “8” or “12” or whatever, creates a reliable and consistent scholarly experience. :slight_smile:

1 Like

Hi @TyAlevizos,

I’m coming from AppMaker myself, and have been struggling a bit working with many-to-many relationships in AppSheet. I have checked out your sample app and was wondering about something. In the Parent Detail there is an inline table of the parentsstudents bridge table. When clicking on ‘Add’ it first goes to the bridge table where the parent is preselected and you can either choose an existing student or enter a new student. When choosing a new student it will go to the student form. In this case you would create an entry in the student table and one in the parentsstudent table, but this happens by navigating over two different forms and having to click Save under each form.

I was hoping there might be a way in AppSheet where you jump straight to the student form and the parent is already assigned to the student and Save only needs to be clicked once. Is this possible in AppSheet with an action. I have tried this in my own application by essentially introducing an action under the parent detail that navigates to a different view via LINKTOFORM("Student_Form", "Related parentsstudent", [_THISROW]) but this does not appear to work. If you have any suggestions I would really appreciate it.

For anyone interested: just dropping in a link to a summary post I made, which contains a sample app, about how to accomplish this without the need for a bridge table.

3 Likes

Thank you so much.
Claudio

1 Like