Simple Client Request is Driving Me Insane

[Nathan Filz]

Good Evening :) I have been a lurker on these forums for long enough, I suppose it's time for me to make my first desperate plea to the Office 365 Masters.

I am doing some work for a law firm and the partners have a simple request. They keep a list of every case that the firm has ever taken on and wish to continue doing so. Currently they are using Excel 2007 and the worksheet has 13,000 rows, each one representing a single case.

Summarizing here but basically the fields look something like this

<ClientName><CaseName><CaseNumber><TypeOfLaw><DateOpened><DateClosed><DateDestroyed><StorageBox>

They currently have a worksheet that is not formatted at all, just raw fields with a horrid set of 26 macros that are supposed to make browsing this mess easier (they do not work)

I am trying to come up with a better way to maintain this information and I am overthinking it. I have gone down the access route, sharepoint list route, powerBI route, Teams Route, etc. All of it ends up looking like a Legal Management Software mess that they specifically want to avoid. (They have the enterprise sas suite that has all of this information, but they want something quick and easy without having to fire up the bloated Software solution)

In short, I need a clean and easy way to store 13,000 records in a format that is searchable, easily editable and impossible to break.

Does anyone have any insights as to how I could accomplish this without ripping the remainder of my hair out? I thought this would be an easy task and I have so far devoted 2 days to it.

Thank you in advance to all the gurus that will absolutely prove why you guys get the big bux :)

[#2]

How about a PowerApp using the Common Data Service?

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-intro

Personally, I have experimented with PowerApps but I'm yet to use the CDS as a data source.

[#3]

Agree here, if custom dev is not an option I would explore PowerApps possibilities

[#4]

What’s wrong with a modern Sharepoint list. It can do all of the above. With views and alerting?

[#5]

I suppose I could use a SP List, the 13,000 records seemed a little unwieldy but I suppose if Excel 2007 can handle it...

The one thing I was a little concerned with was staying away from getting into the weeds with permissions and data privacy. While no personal protected information is on the list, it would be an easier pitch if the data was somewhat shielded from the public internet. The records also should not be able to be edited. 

The more that I think about it and read these responses, it is probably the best way to go. 

[#6]

SharePoint online is shielded from the Public internet. Only authorized user can access the data.

SP lists can handle way more than 13,000 records, especially with the new intelligent indexing that was recently added.

You may want to consider creating one or more Term Sets for some of the data so that there can be a master set of values that can be reused in many locations, the listing of clients names and TypeOfLaw would be ideal candidates for this.

Another option would be to use BCS to connect directly to the original system, there are several 3rd party tools that make this very easy.

Another option would be to use Flow with a Data Gateway to populate one or more SharePoint lists with data when the source system changes.

[#7]

I would add on here one thing.

1. I think the Modern SP list is going to be your easiest option. Its private by default, and you can organize the permissions how you want. 
2. Its searchable, which makes finding information that much easier.

3. You could integrate in some cool flows to perhaps help them out too

Something like "When a new case is added, if that case has the type of law as divorce, notify this person"

Just something cool to think about as potential updates/automation onto the SharePoint List.

I also like PowerBI in the "I want to look at all the cases over time to find trends" idea, but to just store them.

Adam

[#8]

SharePoint List with Modern View.  Remember to create indexes on each column before you import the data.  Restrict the permissions to Read Only with the exception of whoever is administering the data.

If you then want to be fancy (and have licences) you can connect Power BI to the list to create a dashboard.