SharePoint Lists no longer being enough

[lguzmanc433]

Hello,

I launched a project of an app on my small university for students belonging to a Leadership movement, could check out on how many and which required activities they assist per quarter. Launched this very easily with a sharepoint list, I just used 2 lists, 1 for storing some data of each student that used the app and another one as an assistance list which just contains the activity that the student assisted, the student, and his email.

Pretty easy, by now I also want to add an activities list for students to check out on what are the possible activities on pipeline each quarter.

On the assistance list, I'm entering around 1500 items per quarter. So for having a good performance on the filtering of the app every quarter I have to store the list in an excel file and then remove all the records, of it and start again.

I would like to make this job more efficient, secure, long-term driven, automated and without caring on data space or performance on the app. For example for students to even be able to access records from past quarters and so... which sounds obvious I know but right know I don't have the knowledge for doing it, not on an online way for them to be able to access from any where, which is the main plus...

What I like about SharePoint is the accessibility and of it, I want students to be able to acces the data online whenever they want.

What would be the best way to create a long term solution for this, with the same or better accessibility... I don't know...

For example could this be achieved with SQL server or access data bases?

Guess someone as experienced same capacity and performance limitations and barriers, what have been the solutions?

Me and the student community love power apps due to it's simplicity, I want to keep it that way.

I hear you!

[PaulD1 · Accepted solution]

If you are careful with your delegable filters, you may be able to stick with SharePoint, but SharePoint is not a database and sooner or later it will bite you hard (also not secure as any user with permissions to access the data can get to it directly in SharePoint and edit it outside of the business rules enforced in your App).

The sad story for PowerApps is that the new licensing terms for SQL Connectors mean that PowerApps only make sense for 'high value, low user number' scenarios when connecting to SQL. This is exacerbated by the fact that PowerApps shouldn't be used for critical apps due to lack of control of when updates drop and a history of updates breaking production Apps. High value tasks are usually pretty critical.

MS seem to be pushing people to CDS, but that is expensive and very poor (imo) compared with SQL (some big companies have Dynamics for their ERP and so get CDS as part of that).

Access front-end to an Azure SQL DB would give you the performance, security (if using AD authentication) and scalability while avoiding high licensing costs (Access RunTime is free), but restricts users to Windows desktop machines with either Access or Access Runtime installed and users must be logged in as themselves (if using AD for authentication). It is also a hassle for keeping everyone on the same version and rolling out updates (though there are techniques to make that easier).

Unfortunately for the scenario you describe, you probably want to look at developing some form of Web app, but that means choosing and getting to grips with an appropriate technology stack (that's where I'm currently at).

[Anonymous · Accepted solution]

@lguzmanc433 I'm a SharePoint fan and think most of what you need can be achieved using it. Although, I haven't used SQL / CDS / Other data source so maybe I'm biased?

@PaulD1 mentions being careful with delegation solves a number of issues, and it can. @RezaDorrani has an excellent series on working with SP & managing delegation here. The other thing to note is to ensure you setup your column indexing before you reach 5000 items - you can't do it after you go past that limit.

To the issue of permissions / security that PaulD1 mentions he is correct in that it can be an issue. Ways that I/we have managed more sensitive data include:

1. App Patches to a SP List which everyone has permissions to. Then Flow (Power Automate) gets that item and puts it into another RESTRICTED ACCESS LIST and deletes the item from the first list. First list is always empty (has one item briefly then deleted) and 2nd list holds the data.

2. (a colleague of mine uses this process) Once a month he moves old data from a List into an Archive List using Flow. This reduces the number of items in the 'working list' to a manageable level. Works similar to 1 above.

3. Our SharePoint guy has recently been trialling the Power Automate Premium connector 'Plumsail SP'. Among other things, this connector allows you to 'turn-off' and 'turn-on' List permissions. eg App user wishes to patch data - Plumsail allows permission - data patched - Plumsail removes permissions. This appears to be working well and it looks like our company will start using it as a paid service.

I've not yet (fingers crossed) had a production app fall over due to PA updates so I can't comment on that.

[lguzmanc433]

Thanks to both of you for your comments on this @PaulD1  and @Anonymous ,

I love Sharepoint  as well, all of my power apps have been created from SharePoint data. I was just curious on if there were something else better for me to get as a said some more efficient way and less human required interaction for keeping the app working.

But from both comments I'm assuming there might be but they are too expensive, rara o without the same benefits as SharePoint.

@AnonymousThanks for the reference on delegation, I sure will study on that and try to get the best out of my lists jaja Also hae to study on indexes, haven't understand pretty well how does that work in SharePoint, which columns could be indexed, what does it involve to have an index column in a list you now... I would try to get more knowledge on that for sure.

Thanks!

[Anonymous]

@lguzmanc433 yes, studying delegation will allow you to access all your data so it's a great skill to have. I refer to those videos often because I cannot remember all of the content haha.

SP Indexing is basically a way to speed up data retrieval from you datasets / SP Lists. This article is a good intro and there are 2 other following articles that are worth reading. Which columns to index is up to you. You can index up to 20 columns in SP but the more you index the more load you put on the system. I generally use up to 5-6 columns but you pick columns that you think you will want to search on eg date columns.

On date columns, it's best to set them up with a 'buddy' date column in the format 'yyyymmdd' and of type number. Date columns cannot be filter using '<' or '>' in PowerApps so you cannot filter a between some dates. But you can filter between 2 numbers that look like dates. I think this is covered in one of those delegation videos.

All the best with your apps, hope they don't give you too many headaches 🙂 

[JohnP2]

@PaulD1 

@PaulD1 wrote:

The sad story for PowerApps is that the new licensing terms for SQL Connectors mean that PowerApps only make sense for 'high value, low user number' scenarios when connecting to SQL. This is exacerbated by the fact that PowerApps shouldn't be used for critical apps due to lack of control of when updates drop and a history of updates breaking production Apps. High value tasks are usually pretty critical.

MS seem to be pushing people to CDS, but that is expensive and very poor (imo) compared with SQL (some big companies have Dynamics for their ERP and so get CDS as part of that).

I couldn't agree more. Early last year MS convinced us to move from SharePoint lists to CDS. It was a mixed experience; especially the performance which was relatively poor compared to SharePoint. Then the licensing change came and we dropped CDS completely - $40 dollar per user per month for 1000 users? It scales poorly to put it mildly. We moved back to SharePoint. Deploying apps/SharePoint lists to other tenants is a bit more pain, but there are many tools to assist with that.