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.  

[summitb]

Which licensing package is the school using. If you have CDS then just use CDS. If you are using the Education license of Office 365, May be using a more flexible option like Azure Sql might be a data option.

[peronis]

This is from my subscription screen

Office 365 A1 for faculty

Project for Office (Plan E1)

Common Data Service

Education Analytics

Microsoft Kaizala Pro

Whiteboard (Plan 1)

Office Mobile Apps for Office 365

To-Do (Plan 2)

Azure Active Directory Basic for EDU

School Data Sync (Plan 1)

Microsoft Stream for O365 E3 SKU

Microsoft Teams

Microsoft StaffHub

Flow for Office 365

PowerApps for Office 365

Azure Rights Management

Microsoft Forms (Plan 2)

Microsoft Planner

Yammer for Academic

Office for the web (Education)

Skype for Business Online (Plan 2)

SharePoint Plan 1 for EDU

Exchange Online (Plan 1)

[summitb]

Looks like you do have Common Data Service why don't you use CDS. It great!

[peronis]

No idea how to use it, but i'll take a look.

1. If I used CDS would i be able to do something similar, like add all entries to one table? And will that allow my to filter on more than 2000 entries?

2. Would using one table for everyone be good, or would cds allow me to create/search from a different table for each student? Are there tables? Is there a web interface for cds I can view the data from?

3. Would teachers in my school be able to access cds without me sharing it to each teacher? As of now, with excel I'd have to share the file to each user to allow access to the data.

[summitb · Accepted solution]

The answer to all your question is yes. I have a CDS with around 100k and it works smoothly with PowerApps.

[timl]

Hi @peronis 

It sounds like you've done a great job on your first Power App! Here are some additional comments I would make. In summary, I would recommend that you move to a least a SharePoint data source.

------------

>> 1 Would a sharepoint list or another solution/way of doing what I'm trying to do solve my potential issue?

An Excel data source cannot handle the quantity of data that you require. As a minimum, you would need to use a SharePoint list. In your case, the benefit of using SharePoint is that you have a licence for this (it is included in your Office 365 A1 plan). CDS and SQL Server are the best performing data sources, but require additional licensing. With these data sources, you will have fewer problems with query delegation.

>>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!?

Although technically you can create a separate Excel sheet for each student, I would avoid this whole idea. There is a 2MB limit on Excel files that we access through Power Apps, so you will soon reach this limit of the amount of data that you want to store. If you were to use a single Excel file, or separate files for each user, you would need to add a separate data source for each student. This is not a maintainable way to move forwards.

>>3 Any other options or thoughts? Am I doing this all wrong? Hope this all made sense.

No, you're not doing this wrong! I would recommend that you try and anticipate some of the queries and search requirements that you might encounter in future. This can help you better plan your data structure to try and avoid any potential delegation issues.

------------
Comments on CDS

I agree with @summitb that the CDS offers the best solution. However, I would check with your administrator that you actually do have access to the CDS. This is what the documentation says....

https://docs.microsoft.com/en-us/power-platform/admin/pricing-billing-skus

Common Data Service functionality required by other Microsoft 365 applications appears as the "Common Data Service" service plan in the Apps section of the Microsoft 365 admin center. These limited capabilities of Common Data Service are only available through select Microsoft 365 licenses and can't be used to run any custom Power Apps applications.

In answer to your other questions...

>> 1. If I used CDS would i be able to do something similar, like add all entries to one table? And will that allow my to filter on more than 2000 entries?

If you were to use the CDS, you can add all entries to one table and you can filter on more than 2000 entries. The CDS offers excellent support for this.

>>2. Would using one table for everyone be good, or would cds allow me to create/search from a different table for each student? Are there tables? Is there a web interface for cds I can view the data from?

Yes, using one table for everyone is good. In CDS, tables are called entities. There is a web interface that you can use to view and edit both the structure and the data in entities. With the CDS, you can also build model driven apps on top of your data (in addition to the canvas app that you've created so far).

>>3. Would teachers in my school be able to access cds without me sharing it to each teacher? As of now, with excel I'd have to share the file to each user to allow access to the data.

A feature of the CDS is that it offers a configurable security model. For example, you can grant read/write/create access to specific users, and you can secure records at a row level basis. You would have to grant the requisite permissions to your users.

[peronis]

So I'm trying to use cds, but I think i need to be an administrator role to do so...  This is what I see under data->entities:

I don't think that is likely to be given by our it dept.  Does sharepoint give >2000 row limit? what is the limit?  or should i push to get admin role assigned to me?

[timl]

With SharePoint, you can store more than 2000 rows per list. However, query delegation limitations mean can prevent you from retrieving rows above 2000, depending on the operators that you want to use.

[peronis]

So to use cds, I need an environment first, then i create a cds in that environment right? I'm trying to figure out if its a admin role I need to have, or if there is a license issue. This is what I see:

Under environments there are none listed.