Gallery displaying over 2000 records from excel

[Anonymous]

Hello,

I am a beginner with Power Apps. I failed to display over 3000 records in the gallery and use a search function to get certain records. I have changed the advanced setting to allow a limit of 2000. How can I allow over 3000 records to be searched?

I have seen a few posts that suggest using concurrent(….), for this onstart commands, where do I put these to? Do I need to create a new ID column in the excel file? Thanks a lot!

[PowerAddict]

Hi,

You are most likely running into delegation issues. What is the Items property of your gallery?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[Anonymous]

Hi Hardit,

I have a big excel file consisting of over 20 columns and I am trying to search for 5 to 6 columns in the gallery. SO the Item property is search(datatable, searchbox.selectedtext, column1, column2... column5) but I cannot search over 2000 records. Thanks

[PaulD1]

Excel is very limited as a datasource - if possible, consider using something else. Even SharePoint will allow you to perform delegated (performed on the datasource server, not by the client, so returning just the matches) searches/filters for most simple data types.

SQL has best delegation support plus the ability to create your own views (Queries) but for 3000 records in a single table, SharePoint should suffice.

[v-siky-msft]

Hi @Anonymous ,

How did you connect excel to PowerApps? By OneDrive business?

There are lots of known limitations to Excel, and you are facing a delegation issue. So it is not recommended to use excel as data source fo a productive app. Please check this article https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#known-issues-and-limitations

Two workarounds:

1. use statics Excel by 'Import from Excel' connector, you can have 10000 rows and all users can have access to all rows, because the statics excel is attached to your app, and the important thing is the statics Excel is read-only, so try it if you don't need to modify data.

2. convert Excel to CDS, Sharepoint list or SQL. They are delegable except for some specific function, please refer to there connector document: SharePoint , SQL server , CDS 

Sik

[Anonymous]

Hello Sik,

Thanks very much for your reply. Yes, I uploaded the data file onto One Drive business then connect it from there. If I upload the excel file to the SharePoint list, once I make any modifications, will both the excel file and the SharePoint list be modified or just the SharePoint List.

Thanks a lot in advance. 

[poweractivate]

---

@Anonymous wrote:

If I upload the excel file to the SharePoint list, once I make any modifications, will both the excel file and the SharePoint list be modified or just the SharePoint List.

Thanks a lot in advance. 

---

@Anonymous 

Please clarify "Uploading an Excel file to a SharePoint list"

Are you attempting to modify a SharePoint List with an Excel File?

Are you trying to do this from the Canvas App itself?

You cannot currently upload an Excel file through PowerApps Canvas App to a SharePoint List to our knowledge to update a SharePoint List from Excel file rows and columns, this is unsupported out of the box in PowerApps .

If you are referring to the Edit in Excel feature on the List, that will update the SharePoint List. There will be no effect on your Excel "data source".

Your Excel data source as you referred to it initially is actually used literally if you use it as the PowerApps data source - in other words, the Excel File itself is used as  the underlying database engine!

We recommend like @v-siky-msft  not to use an Excel file as the actual data source in a production PowerApp.

[v-siky-msft]

Hi @Anonymous 

Do you mean import excel files to the SharePoint list? If so, the modification on SP list will not map to Excel files.

However, If you export the SP list to excel, the excel will maintain a connection to SP list. That means that any changes in the list in SharePoint will propagate down to your Excel file on a computer. Excel will not change automatically. You have to refresh it for changes to trickle down. Here is how to do this:

Note: the connection is one-way, information flows from SharePoint to Excel, not the other way around! So any changes in Excel will not go back to SharePoint. Once you click on Refresh, SharePoint will overwrite whatever you did in Excel.

Sik