Another user posted something similar but mine is slightly different so I started a new post. I'm really looking for best advice.
I have a checkin app for the lobby of an elementary school. Parents late dropping off/pickup early no longer use a sheet of paper, they do it on an app. The app writes checkin/out data to a Sharepoint Online list but Sharepoint has its data limitations. On a good day, there are 25-30 checkin/outs. On a 180-day school year, that is 5,400 rows of data...too much for Sharepoint. Last school year, we implemented the app around the end of November so I ended up with less data as opposed to this year where I'm starting at the beginning. This year I plan to write the data to MYSQL.
1. It is easier for the front desk staff to view a live Sharepoint List on the browser to see checkins/outs. They are not familiar with SQL. Also, a live stream is needed to ensure parents actually complete the checkin process. In a perfect world, i would like to print a tardy slip live on a small label printer.
2. Could I patch the data to Sharepoint as I am currently doing and then use Microsoft Flow to write each line to MYSQL at the end of the night and delete data from Sharepoint?
3. ...Or other suggestions. Just not sure what the best option would be. Guidance is appreciated!
First, SharePoint isn't limited to 5,400 rows.. You can have millions of rows in a SharePoint list. You just can't retireve all of them at once. So if you put something as simple as writing just the month name and day number to a column as a string in the list and then filtering on that you can easily get your retrieved record count below the 500 record limit for retrieval.
1) I would make a screen in the app for viewing the checkin/checkouts whatever datasource you use.
2) MySQL will require a premium license. So although that scheme will work it will add expense and isn't really necessary.
3) See my opening paragraph. If designed right SharePoint will work fine.
@Pstork1 I agree with you. That's how I've been managing it to-date. I've filtered on [today] to just view today's checkins. The only problem with that is that i can't analyze data which is a part of my job. I probably should've mentioned that in the original post. It's more than just about viewing it.
Let's say the Principal needs to know how many times a student has been late/left early because let's say that student is struggling academically, what I've had to do is to send the data to excel to analyze. The problem I'm having with that is it is a huge dataset which is hard to view at once and I'm sending it to excel whenever i need information instead of querying a dataset that already exists in SQL. It just gets choppy.
I've added a flow to send each Sharepoint row to an excel workbook I have saved but every time I go to open it, it interrupts the data stream and throws an error because it's trying to write at the same time, I guess. So, I figured SQL would work. I don't necessarily have to use MYSQL but maybe MS SQL....
Again, there are ways to do that in SharePoint without moving it to either Excel, SQL, or MySQL. The problem you will run into filtering on [today] as you move forward is that DateTime isn't a delegable filter. So it fails in larger recordsets. That's why I suggested encoding the date/month in a string column. That is a delegable filter. That is also true of student name. If that is entered in a column as text it is easy to filter even really large amounts of data. If it is stored as a person/group column it becomes more difficult.
Its not that using SQL or MySQL is a bad design. Its just that it will add to your licensing costs and isn't necessary if you learn to use SharePoint more effectively.
Read the announcement for more information!
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Features releasing from October 2019 through March 2020
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications