cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirvana_moksh
Level: Powered On

Using Sharepoint List

Hello All,

 

I have migrated 5 Power Apps which previosuly wrote to Excel to use SharePoint List now. The reason being:

 

  • I can now add Attaching files as a fucntionality 
  • Delegation benefits
  • Managing SharePoint List is much easier
  • 30,000,000 items per list

The most amount of records that one of this App will write to Sharepoint will be nearly 2000 items a month so at most 30k records a year. I know the browse gallery shows at most 500-2000, but how have you approached this issue? I was thinking of maybe using collect and showing the most recent 500 comments and the 'Search' box will be used for finding old items. I have not done this before and would really appreciate input on this front. The users mostly want to see the entry the just made after they hit 'Submit'.

 

 

Thank You

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft Employee

Re: Using Sharepoint List

There're 2 ways to resolve the issue with search results in a large Sharepoint list:

 

1. Use delegation for filtering the list and then load up to 500-2000 search results.

Advantage: No custom indexes required (see explanation below). Very simple to implement.

Disadvantage: Delegated search in SharePoint is pretty limited. You will be able to use only "=" and "StartsWith" functions

 

2. Load all the data in batches (all at once or some batches only) and do full search locally.

Advantage: Full search functionality.

Disadvantage: Because of the limitations with delegated functionality in SharePoint, you will need to add and maintain a custom index in your list. For example, you could create a "batch number" column in your list (set batch_number to 1 for items 1 - 500, batch_number=2 for items 501-1000 and so on). so you can use the delegated "=". 

 

Note1: Other sources have much better support for delegation. For example, a regular "autoindexer" would be enough in order to implement batch loading from SQL server. 

Note2: You can find all the information about the delegation here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

 

Let me know which method would work for you.

8 REPLIES 8
Microsoft Employee

Re: Using Sharepoint List

Hello nirvana_moksh,

 

Please note that the 500-2000 limitation is on the connector that pulls data and not on the gallery control. The gallery control may contain virtually unlimited number of items.

In your case, it seems like loading data in batches of 500 and adding them to a collection that is bounf to the gallery control will make sense.

Hope this helps.

nirvana_moksh
Level: Powered On

Re: Using Sharepoint List

Hey Dinusc,

 

Thanks for your reply, can you suggest how do I go about loading data in batches of 500 and adding them to a collection like you suggested, I have never worked with the Collect/Collection options. Also, can you suggest a way I can make the connector pull the most recent 1000 comments based on the insert date to show up on the gallery always?

 

Thank You

Super User
Super User

Re: Using Sharepoint List

I can get over 2k SharePoint items in a gallery as long as I don't use any functions or operators that aren't delegable. The pain comes with scrolling in the gallery. The gallery only loads 100 items at a time. When you get to the bottom of the gallery you have to scroll up a little bit and then back down to get the next 100 to load. I don't think the gallery scrolling is a limitation of SharePoint because I get the same experience when I am SQL as my data source.

Microsoft Employee

Re: Using Sharepoint List

Jeff, are you sure it's not just a delay at the end of the scroll? If you have to scroll up as you mention then this is definitely an issue which I will check.

@nirvana_moksh, I will provide a sample of loading in batches later, if you're still interested.

I'm pretty sure I was playing with a gallery with more than 100K items. This was in a browser though (not on a mobile device)

Super User
Super User

Re: Using Sharepoint List

@dinusc,

 

We use browser for most of our PowerApps (Stand Alone or Custom Form). The scrolling issue is happening in the browser. I did open one of my large list apps on an iPad in the PowerApps app and I was able to use the touch screen jester to scroll. In this case the gallery did load the next 100 when I got to the end of the previous 100. If I scrolled really fast then I had to wait as it loaded next 100 but I think that would be expected. So the scroll down up and back down issue is with the web browser experience on the PC.

nirvana_moksh
Level: Powered On

Re: Using Sharepoint List

I still am very interested in it! And would be very appreciative if you could help me by providing that Smiley Happy

Highlighted
Microsoft Employee

Re: Using Sharepoint List

There're 2 ways to resolve the issue with search results in a large Sharepoint list:

 

1. Use delegation for filtering the list and then load up to 500-2000 search results.

Advantage: No custom indexes required (see explanation below). Very simple to implement.

Disadvantage: Delegated search in SharePoint is pretty limited. You will be able to use only "=" and "StartsWith" functions

 

2. Load all the data in batches (all at once or some batches only) and do full search locally.

Advantage: Full search functionality.

Disadvantage: Because of the limitations with delegated functionality in SharePoint, you will need to add and maintain a custom index in your list. For example, you could create a "batch number" column in your list (set batch_number to 1 for items 1 - 500, batch_number=2 for items 501-1000 and so on). so you can use the delegated "=". 

 

Note1: Other sources have much better support for delegation. For example, a regular "autoindexer" would be enough in order to implement batch loading from SQL server. 

Note2: You can find all the information about the delegation here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

 

Let me know which method would work for you.

Microsoft Employee

Re: Using Sharepoint List

@Jeff_Thorpe, do you still see this issue in the latest release in the browser? The rendering functionality (including paging) has been changed several versions ago. The old one can still be used by switching to "render in classic mode" from applciation settings).