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:
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
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.
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.
Solved! Go to 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.
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.
This is from my subscription screen
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.
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....
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.
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?
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.
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.
Check out new user group experience and if you are a leader please create your group
Check out how to claim yours today!
We are excited to announce that Demo Extravaganza for 2021 has started!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Don't miss the call this month on June 16th - 8a PDT