Splitting a sharepoint list used as database

%3CLINGO-SUB%20id%3D%22lingo-sub-1269892%22%20slang%3D%22en-US%22%3ESplitting%20a%20sharepoint%20list%20used%20as%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269892%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20recently%20became%20the%20owner%20of%20a%20Powerapps%20application%20to%20code%20calls%20for%20our%20call%20center.%20Though%20I%20understand%20data%2C%20know%20some%20very%20basic%20coding%20and%20know%20my%20way%20around%20excel%2C%20I%20am%20still%20relatively%20green%20when%20it%20comes%20to%20the%20more%20technical%20aspects%20of%20storing%20and%20managing%20larger%20sets%20of%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20problem%20%3A%26nbsp%3B%3CBR%20%2F%3EThe%20powerapp%20was%20created%20internally%20by%20a%20manager%20with%20what%20he%20had%20learned%20online.%20The%20app%20itself%20works%20perfectly%20however%20the%20data%20entries%20from%20the%20call%20coding%20are%20pushed%20to%20a%20sharepoint%20list%20that%20is%20used%20as%20a%20database.%20In%20the%20past%2C%20this%20allowed%20us%20to%20extract%20to%20excel%20from%20sharepoint%20and%20manipulate%20to%20gain%20insight%2C%20however%20the%20list%20has%20reached%201%2C07%20million%20entries%2C%20which%20makes%20it%20very%20slow%20to%20extract%20and%20handle%2C%20on%20top%20of%20losing%20out%20on%20some%20of%20the%20data%20for%20having%20reached%20excel%20limit.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20it%20be%20possible%20for%20me%20to%20split%20the%20list%20so%20I%20could%20archive%20year%20by%20year%20therefor%20reducing%20the%20amount%20of%20entries%20to%20extract%3F%20Or%20is%20there%20a%20better%20solution%20that%20is%20accessible%20for%20someone%20with%20my%20level%20of%20experience%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1269892%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFiles%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282360%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20sharepoint%20list%20used%20as%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282360%22%20slang%3D%22en-US%22%3EBump%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1282507%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20sharepoint%20list%20used%20as%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1282507%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F601536%22%20target%3D%22_blank%22%3E%40Riverain96%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESplitting%20the%20list%20by%20year%20is%20a%20good%20approach.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20create%20additional%20Views%20that%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fuse-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4731-8f9b-3dfaeacea3d4%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Efilter%3C%2FA%3E%20based%20on%20year%20and%20export%20those%20rows%20to%20Excel.%20From%20Excel%2C%20I%20would%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fmicrosoft-365-community%2Fblob%2Fmaster%2FCommunity%2Fimporting-data.md%23lists--export-spreadsheet-to-sharepointpower-user%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eexport%3C%2FA%3E%20to%20a%20new%20SharePoint%20List.%20Removing%20the%20archived%20rows%20is%20time-consuming%20and%20I%20would%20suggest%20looking%20into%20Powershell%20to%20do%20it%2C%20specifically%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Fsharepoint-pnp%2FRemove-PnPListItem%3Fview%3Dsharepoint-ps%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERemove-Pn%3CWBR%20%2F%3EPList%3CWBR%20%2F%3EItem.%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20not%20change%20the%20default%20view%20that%20is%20referenced%20by%20the%20PowerApp.%20Also%2C%20consider%20a%20yearly%20archive%20and%20purge%20to%20keep%20performance%20high%20(in%20its%20current%20state%20anyway).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20this%20helps.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENorm%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1308929%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20sharepoint%20list%20used%20as%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1308929%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20reply!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20will%20look%20into%20powershell%20to%20see%20if%20I%20can%20make%20it%20work%20that%20way%2C%20though%20it%20seems%20more%20and%20more%20that%20a%20sharepoint%20list%20just%20isnt%20the%20proper%20way%20to%20store%20our%20data.%3CBR%20%2F%3E%3CBR%20%2F%3EAlso%20-%20I%20looked%20into%20creating%20a%20flow%20to%20archive%20part%20of%20the%20list%20into%20a%20new%20list%20but%20the%205000%20line%20limit%20seems%20to%20make%20it%20impossible.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again%20for%20the%20help!%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1308934%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20sharepoint%20list%20used%20as%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1308934%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F601536%22%20target%3D%22_blank%22%3E%40Riverain96%3C%2FA%3E%26nbsp%3Bv%3CSPAN%3Eiews%20that%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fuse-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4731-8f9b-3dfaeacea3d4%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Efilter%3C%2FA%3E%3CSPAN%3E%26nbsp%3Bbased%20on%20year%26nbsp%3Bshould%20overcome%20the%205%2C000%20limits.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, 

I recently became the owner of a Powerapps application to code calls for our call center. Though I understand data, know some very basic coding and know my way around excel, I am still relatively green when it comes to the more technical aspects of storing and managing larger sets of data. 

 

Here is my problem : 
The powerapp was created internally by a manager with what he had learned online. The app itself works perfectly however the data entries from the call coding are pushed to a sharepoint list that is used as a database. In the past, this allowed us to extract to excel from sharepoint and manipulate to gain insight, however the list has reached 1,07 million entries, which makes it very slow to extract and handle, on top of losing out on some of the data for having reached excel limit. 

 

Would it be possible for me to split the list so I could archive year by year therefor reducing the amount of entries to extract? Or is there a better solution that is accessible for someone with my level of experience? 

 

Thanks in advance for any help. 

4 Replies
Highlighted
Highlighted

Hi @Riverain96,

 

Splitting the list by year is a good approach.

 

I would create additional Views that filter based on year and export those rows to Excel. From Excel, I would export to a new SharePoint List. Removing the archived rows is time-consuming and I would suggest looking into Powershell to do it, specifically Remove-PnPListItem.

 

I would not change the default view that is referenced by the PowerApp. Also, consider a yearly archive and purge to keep performance high (in its current state anyway).

 

I hope this helps.

 

Norm

Highlighted
Thanks for the reply!

I will look into powershell to see if I can make it work that way, though it seems more and more that a sharepoint list just isnt the proper way to store our data.

Also - I looked into creating a flow to archive part of the list into a new list but the 5000 line limit seems to make it impossible.

Thanks again for the help!


Highlighted

@Riverain96 views that filter based on year should overcome the 5,000 limits.