cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Moritz21
Regular Visitor

500 Item Limit - SQL

Hello, im trying to edit/view SQL databases in PowerApps. The databases have up to 1.5 million rows. Do you have an idea how I can bypass the 500 item limit?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Drrickryp
Super User
Super User

@Moritz21 

Most PowerApps functions are delegatable in sql so the limit doesn't apply to them. 

View solution in original post

@Moritz21 

ForAll(Stamm_Vertraege As myRecord;If(Textsuche_3.Text in Vertrags_ID,myRecord))

Does the above version also keep the limit or does it bypass the limit? 

View solution in original post

7 REPLIES 7

@Moritz21 

 

1. Write delegable formulas only that give no yellow triangle warning of the formula not being delegable to the data source.

2. Even if the formula has no yellow triangle warning, do not use Set, Collect, ClearCollect - these have an invisible yellow triangle warning when used with something other than another collection itself (e.g. if used directly on the data source, for example). In other words, you will get no yellow triangle warning when you use these, but you might still see only 500 items if you use any of these three functions, so you should pretend as if the yellow triangle warning is still there if you use these three functions. These three functions can only be called in batches of <delegable item limit> against a data source i.e. batches of 500 in your case, if you really want to use them. So for example, the Collection might be able to be bigger than 500 items, however, you can only call Collect on batches of 500 or less - so this requires some planning and more complex usage. So instead, for simplicity, just use your data source directly into a Gallery rather than try to put it into a variable or Collection so you could be sure there are more than 500 Records.

 

3. If you cannot write delegable queries, you must use a complex way of getting the records 500 at a time.

 

4. I recommend you raise the limit to 2000. You can do do like this:

 

A. Go to Settings

B. Scroll down to Data row limit

C. Change it to 2000

D. Close the modal

poweractivate_0-1664872234598.png

 

 

See if above helps as starting point @Moritz21 

 

Moritz21
Regular Visitor

@poweractivate 

 

Hello, thanks for the help.

Loading the Data directly in the Gallery works.

My only problem now is, that i need to filter it.
Is there any way? I have more then 1 million rows...

 

Thank you very much.

 

Drrickryp
Super User
Super User

@Moritz21 

Most PowerApps functions are delegatable in sql so the limit doesn't apply to them. 

It does.
I filtered the Gallery with:

Filter(Stamm_Vertraege;Textsuche_3.Text in Vertrags_ID)

and after that i only had 500 Items in the Gallery...

@Moritz21 

ForAll(Stamm_Vertraege As myRecord;If(Textsuche_3.Text in Vertrags_ID,myRecord))

Does the above version also keep the limit or does it bypass the limit? 

Drrickryp
Super User
Super User

@Moritz21 

The best way to test if your formula is delegatable is to set the limit to 1 and see if it still works.  If not, consider doing the filter in sql and using a view as the items of the gallery.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,918)