500 Item Limit - SQL

[Moritz21]

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?

[poweractivate]

@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

See if above helps as starting point @Moritz21 

[Moritz21]

@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.

[poweractivate]

@Moritz21 

Use the Filter function, use this link for a guide.

See if it helps @Moritz21 

[Drrickryp · Accepted solution]

@Moritz21 

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

[Moritz21]

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...

[poweractivate · Accepted solution]

@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]

@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.