How can I display more than 2000 records from SQL Database to combo box?

[Big_S]

Hi Guys,

How can I display values from SQL Database to combo box? this is my formula 

Sort(Filter(Supplier,Status = 1),Name , Ascending)

[poweractivate]

@Big_S To check on it -  is it showing more than 2000 in a Gallery?

[Big_S]

hi @poweractivate 
I'm only using a combo box to display the values from SQL database here is my example vaues from SQL didnt display all the records using my formula above

[LaurensM]

@Big_S 
If I remember it correctly, Power Apps is limited to data fetches in batches of 2000 records.

Is there a possibility to further filter the data set by e.g. Filter(DataSource, StartsWith(Name, Self.SearchText))?
That will also improve UX, because the user will have trouble finding the correct record by scrolling through a list of 2000 items.

[poweractivate]

@Big_S I'm aware you want to use a Combo Box. Please try it in the Items property of a Gallery temporarily so you could see if it shows more than 2000 Items.

[Drrickryp]

@Big_S 

Comboboxes and dropdowns are limited to the number of items they display.  Anyways, who wants to scroll through such a huge number of items?  As a user, I would find that quite irritating.

[poweractivate]

Try these steps

1. Click Settings

2. Scroll down a bit and check the Data row limit . Change it to 1 temporarily.

Now try your queries in the Items property of a Gallery.

If you get only one result and expect two or more, you know the data row limit is applying.

Otherwise, if you get two or more results on a Gallery but you get less results on a Combo Box or Drop down, then:

note that there are also other kinds of limits as well depending on the kinds of controls or methods used.

For example, note that a Collection populated by using a single call to ClearCollect is subject to the data row limit despite all inner statements being delegable, because ClearCollect by itself is subject to the data row limit. So if the data row limit were set to 1, your Collection may get only 1 item after using ClearCollect by itself.

There may be other limits depending on the kind of Control used as well. 

To test it more effectively and try to isolate the cause to data row limit triggered by use of a non-delegable operation, use the Items property of a Gallery, since there may be other limits besides just data row limit triggered by use of a non-delegable operation, when using something else such as say a Combo Box, Drop Down, Collection, and so forth.

Technically due to how the Gallery works note that it may not actually be showing all results all at once either, however it might be made in a way so that if you scroll for more results it might show more, and that may not be the case for the other controls like Drop Down and Combo Box.

Try the above steps and then use a Gallery's Items property and see if you are always getting just one result or not. If you get results cut off only with the other Controls and you can scroll through all the results in the Gallery, it could be because of the Control, and not necessarily the data row limit.

Note that Controls such as ComboBox and Dropdown may themselves have limits that are separate from the data row limit but may look like the same number. For example, it's possible 2000 is the upper limit on the Control, if you have your data row limit set to 2000 right now then it could be hard to tell whether a limit on the specific Control is the cause, or whether t's from data row limit triggered by non-delegable operation. To know more clearly if it's from data row limit triggered by non-delegable operation, use the Items property of a Gallery, and also perform the steps above to lower the data row limit to 1, so it's really obvious if there's just 1 record returned and you expect much more.

While it may or may not be possible to somehow force these controls like Dropdown or Combo Box to go above the control limit (if there is any), as @Drrickryp says it is not often recommended to try to do this. It's better to make a Gallery work instead for this if you really need it. Besides just a Gallery, it is advisable in most cases to avoid a very large number of results anyway to be shown, for UX reasons as @LaurensM says

---

Note that the data row limit is described as being related to delegation in the description.  This is true. However note that the data row limit is not only triggered due to the use of non-delegable operations with respect to the data source that are of the kind the Editor warns you about, there are other cases too. For example suppose the use of ClearCollect by itself. Suppose also all the inner statements inside of ClearCollect are delegable. Suppose your data row limit is 1. To your surprise, you may find only 1 item in the Collection. This is because the ClearCollect is subject to the data row limit, despite all of the statements inside of ClearCollect themselves all being delegable to the data source.

Note also that this kind of situation does not give any yellow triangle warning due to parts of the formula being "non-delegable" as well in the Editor, in this kind of case there is simply no warning at all. So to be sure of whether or not the data row limit is really applying to any of your formulas and scenarios, I suggest to lower the Data row limit to 1 and then testing from there to be more sure of it, rather than to rely on the yellow triangle warning's presence or absence in the Editor.

See if it helps @Big_S