School Communication app - 2

[peronis]

My first post got marked as spam... Here is is again.  I really appreciate any help.

I am a high school teacher, and there is a need in my school for a communication app that will keep communication records of students to keep all the staff on the same page.  For example...any contact with: parents (phone calls, email), the student, or about the student from teachers/admin/guidance will be logged, so when an issue arises the staff member will be able to pull up a history of logged communication/comments.  In its basic function it needs to do the following:

• select students from our AD
• Allow the staff member to view log of all entries for selected student
• Allow to create/edit an entry

I already made my first powerapp that achieves this functionality, and everything is working.  Well working good enough to distribute.

Since this is my first app, I used the excel option to create the canvas app.  I have a table on an excel online spreadsheet with  6 columns.  I am using the table to log every entry that is created. It logs the student name, person who made the entry, entry date/time, role of person logging, the purpose/type of entry, and the actual message/entry/communication.

The way I have it now, on the main screen the user types in the student name and once they select the student it sets the displayname from o365 users as a global variable i named 'student'. Then on a button press it takes the user to the 'Contact log' and there I have a gallery that displays the entries from the excel table.

Since it displays entries from all students and i only wanted the student who the user typed in on the main page, I put

SortByColumns(Search([@Table1],student, 

in for the data items.  Not sure this is the best way, but it functions as desired, and only displays the correct students entries.

However I have a concern and a few questions.

• My concern is if every student in our school (around 800 students), had an average of  8 communications over the year, then that is already 3600 rows on the excel sheet, and if we want this to continue in future years, then its going to get really big.  I read that excel can only search the first 2000 rows of a table.  I'd really want all the entries to show up when the student is being searched.  I read sharepoint lists might be a better option. I also read I might need an actual database like sql to handle the search.

• My questions:
  1.  Would a sharepoint list or another solution/way of doing what I'm trying to do solve my potential issue?
  2. Thinking out loud, Considering each student would be well under 2000 rows for themselves, is there a way when the student is selected in the app, there could be a separate table with columns created for each student if the student doesn't have a table already? That way I'd only have to run the search on the table for that specific student.  Would that be a better way, and keep excel?  How would that work: Could I create a new sheet for each student on the same excel document? Can I reference a particular sheet in the file and the table in it in a gallery? Or would I need a separate excel doc with the table set up for each student?! 800 excel docs!?  And 800 data sources!?
  3. Any other options or thoughts? Am I doing this all wrong? Hope this all made sense.

The administration likes the app and is considering it, however I don't want the whole school coming after me if it breaks after 2000 entries. I have a million other things going on with actually teaching during covid, so I'd prefer no potential headaches down the road.

Thank all of you in the community in advance for any and all help you could give me.  

[timl]

Yes, a CDS database is hosted in an Environment. Each environment can host 1 database. According to your screenshot, there isn't the capacity for you to create another database because the 1GB space for your tenant is used up.

As I mentioned in my previous post, I don't believe the M365 A1 plan that you have includes the full CDS that is required for apps that you build with Power Apps.

[peronis]

Ok.. thank you so much for your help.   I can't find where I can find what that 1gb consists of.  I'm going to do a sharepoint list instead.

So from what I understand, I can have >2000 entries in the sharepoint list.  What is the ideal way to filter my gallery to only show the entries about that specific student from the list? The student name is selected and stored as a global variable in the screen before my gallery screen?

This community is awesome!

[timl · Accepted solution]

Let's suppose you store the email address of the student against each record in your list. You can store the email address of the current user in a variable and call the Filter function to filter the records by the this variable.

Filtering text with the equals operator is delegable so you won't have any issues with the 2000 row limit.

https://docs.microsoft.com/en-us/connectors/sharepointonline/

[summitb]

The delegation issue happens when you put complex criteria's in filter. In you case since it's just Equal operation under it should be fine.