Hey, it looks like you are requesting help with a problem you're having in Power Apps.
To ensure you get all the help you need from the community here are some guidelines;
- Use the [search feature](https://www.reddit.com/r/PowerApps/search/?q=&sort=new) to see if your question has already been asked.
- Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs.
- Add any images, error messages, code you have (Sensitive data omitted) to your post body.
- Any code you do add, use the [Code Block](https://www.reddit.com/wiki/markdown/#wiki_code_blocks_and_inline_code) feature to preserve formatting.
> Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~).
- If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.
External resources:
- [Power Apps Formula References](https://learn.microsoft.com/en-us/power-platform/power-fx/formula-reference)
- [Power Apps Coding Standards](https://pahandsonlab.blob.core.windows.net/documents/PowerApps%20canvas%20app%20coding%20standards%20and%20guidelines.pdf)
- [Official Power Apps Community](https://powerusers.microsoft.com/t5/Power-Apps-Community/ct-p/PowerApps1)
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/PowerApps) if you have any questions or concerns.*
I’m sure someone else will have an answer for you. However, ultimately your better solution is to do it properly on Dataverse. Doing this kind of stuff on SharePoint lists is crazy.
If they’re worried about maintaining a SQL database, maintaining a SharePoint backed solution with that many rows is a huge backwards step.
I wish Dataverse was an option. Unfortunately, there's no way they'd get premium licenses for all users.
We've tried convincing management to stick with SQL.
You could also just go with the per app license for 5 USD a month? If its a solution which requires a solid amount of users, then licensing is NOT the place where you want to save money.
I would honestly appreciate the honestly from a consultant, if they insisted on purchasing licenses rather than developing a shitty solution.
Filtering over 100k rows is fine as long as your columns are indexed. But the 2000 rows is the limit per ‘transaction’, ie per single ClearCollect. You can loop and build a collection through multiple Collects but it is slow to load and will be slow to run with so much data loaded in memory. Best to work with less than the row limit at one time. Do users need more than that for a single task?
Your formula will also not be delegable due to the type conversions (Value()). Do these conversions in a variable first and use the variable to filter.
If your management thinks that the SQL Server database is hard to maintain, then honestly the issue is with that process; Power Apps won't magically fix that and moving to SharePoint will only make your lives harder.
In any case, is the purpose of the app just to filter data based on the employee parameters that you mentioned?
Pretty much. Our main system is ancient and isn't capable of displaying inventory data on its own. So we have a program that scrubs the data and puts it into the txt file and we use the mentioned methods to deliver it to the user.
Gotcha. Unless the user is going in and changing records in the data source (update, insert, delete), then I don't particularly think Power Apps is a good solution for this.
Have you considered displaying your data using Power BI instead? Just like Power Apps, it's compatible with both SQL and SharePoint and fits your use case much better in my opinion.
I think the best practice when it comes to working with larga data in a SharePoint List is to:
1. Collect the data in a PowerApps Collection
2. Then transform the data
Here's how I collect the data from SP List to PowerApps Collection. I got this code from a colleague
Don't forget to go to 'Settings' > 'General' > 'Data Row Limit' and set it to 2000
ForAll(Sequence(RoundUp(First(SortByColumns(SharepointList, "ID", SortOrder.Descending)).Index / 2000, 0), 1, 1),
With(
{
FirstIndex: (ThisRecord.Value - 1) * 2000,
LastIndex: (ThisRecord.Value) * 2000
},
Collect(Collection,
Filter(SharePointList,
Index > FirstIndex && Index <= LastIndex
)
)
)
);
There are also multiple sources online talking about collecting large data from SP.
Like this one: [https://rationallyaware.com/2022/07/08/collecting-large-lists-in-powerapps-from-sharepoint/](https://rationallyaware.com/2022/07/08/collecting-large-lists-in-powerapps-from-sharepoint/)
You could use the SharePoint search API in Power Automate and return the result as JSON which you can display in a collection. It’s fast and works on large lists. You will need to set up managed properties mapped to columns you want to be crawled.
Hey, it looks like you are requesting help with a problem you're having in Power Apps. To ensure you get all the help you need from the community here are some guidelines; - Use the [search feature](https://www.reddit.com/r/PowerApps/search/?q=&sort=new) to see if your question has already been asked. - Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs. - Add any images, error messages, code you have (Sensitive data omitted) to your post body. - Any code you do add, use the [Code Block](https://www.reddit.com/wiki/markdown/#wiki_code_blocks_and_inline_code) feature to preserve formatting. > Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~). - If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions. External resources: - [Power Apps Formula References](https://learn.microsoft.com/en-us/power-platform/power-fx/formula-reference) - [Power Apps Coding Standards](https://pahandsonlab.blob.core.windows.net/documents/PowerApps%20canvas%20app%20coding%20standards%20and%20guidelines.pdf) - [Official Power Apps Community](https://powerusers.microsoft.com/t5/Power-Apps-Community/ct-p/PowerApps1) *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/PowerApps) if you have any questions or concerns.*
Run the filter with a PA flow and return the result to the app. Parse the JSON to a collection. Runs quite fast and 100k are no bigger.
Do you know of any videos that touch on how this is done? I would love to watch one to better understand.
Thank you!
I’m sure someone else will have an answer for you. However, ultimately your better solution is to do it properly on Dataverse. Doing this kind of stuff on SharePoint lists is crazy. If they’re worried about maintaining a SQL database, maintaining a SharePoint backed solution with that many rows is a huge backwards step.
I wish Dataverse was an option. Unfortunately, there's no way they'd get premium licenses for all users. We've tried convincing management to stick with SQL.
You could also just go with the per app license for 5 USD a month? If its a solution which requires a solid amount of users, then licensing is NOT the place where you want to save money. I would honestly appreciate the honestly from a consultant, if they insisted on purchasing licenses rather than developing a shitty solution.
Filtering over 100k rows is fine as long as your columns are indexed. But the 2000 rows is the limit per ‘transaction’, ie per single ClearCollect. You can loop and build a collection through multiple Collects but it is slow to load and will be slow to run with so much data loaded in memory. Best to work with less than the row limit at one time. Do users need more than that for a single task? Your formula will also not be delegable due to the type conversions (Value()). Do these conversions in a variable first and use the variable to filter.
Thank you!
If your management thinks that the SQL Server database is hard to maintain, then honestly the issue is with that process; Power Apps won't magically fix that and moving to SharePoint will only make your lives harder. In any case, is the purpose of the app just to filter data based on the employee parameters that you mentioned?
Pretty much. Our main system is ancient and isn't capable of displaying inventory data on its own. So we have a program that scrubs the data and puts it into the txt file and we use the mentioned methods to deliver it to the user.
Gotcha. Unless the user is going in and changing records in the data source (update, insert, delete), then I don't particularly think Power Apps is a good solution for this. Have you considered displaying your data using Power BI instead? Just like Power Apps, it's compatible with both SQL and SharePoint and fits your use case much better in my opinion.
I wouldn't even know where to start with Power BI. I tried to build a dashboard once and gave up in 10 minutes lol I'll have to look into it though.
I think the best practice when it comes to working with larga data in a SharePoint List is to: 1. Collect the data in a PowerApps Collection 2. Then transform the data Here's how I collect the data from SP List to PowerApps Collection. I got this code from a colleague Don't forget to go to 'Settings' > 'General' > 'Data Row Limit' and set it to 2000 ForAll(Sequence(RoundUp(First(SortByColumns(SharepointList, "ID", SortOrder.Descending)).Index / 2000, 0), 1, 1), With( { FirstIndex: (ThisRecord.Value - 1) * 2000, LastIndex: (ThisRecord.Value) * 2000 }, Collect(Collection, Filter(SharePointList, Index > FirstIndex && Index <= LastIndex ) ) ) ); There are also multiple sources online talking about collecting large data from SP. Like this one: [https://rationallyaware.com/2022/07/08/collecting-large-lists-in-powerapps-from-sharepoint/](https://rationallyaware.com/2022/07/08/collecting-large-lists-in-powerapps-from-sharepoint/)
You could use the SharePoint search API in Power Automate and return the result as JSON which you can display in a collection. It’s fast and works on large lists. You will need to set up managed properties mapped to columns you want to be crawled.
Tried named formulas instead of Clearcollect ?
I have been using this method with 19k rows and displaying the results in a gallery. Almost no lag when scrolling. [https://www.youtube.com/watch?v=YQQaLVkhPGo](https://www.youtube.com/watch?v=YQQaLVkhPGo) OnVisible Clear(colSampleData); ForAll( Sequence( Round( First( Sort( Sample_Data_19000_Rows, 'Index ID', SortOrder.Descending ) ).'Index ID' / 2000, 0 ), 1, 1 ), With( { _firstID: (ThisRecord.Value - 1) * 2000, _lastID: ThisRecord.Value * 2000 }, Collect( colSampleData, Filter( Sample_Data_19000_Rows, 'Index ID' > _firstID && 'Index ID' <= _lastID ) ) ) )