cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gjakova
Advocate II
Advocate II

Filtering with a Dropdown & Combobox - Issue with delegation?

Hi there, I'm creating a PowerApp and my screen basically contains a Data Table and above it 4 ComboBoxes and one Dropdown.

Under settings I changed my Data row limit to 7 (default is 500 and max is 2000), but I did 7 to see what is and what is not affected with this 500 row limit thing.

 

My PowerApp is connected to SQL and when I load my data directly is doesn't have any issue with the row limit in PowerApp. For this screen I use a View that I created in SQL, since I had to do some lookup values for a datatable and SQL was the most efficient way to do it.

 

Now I'm stuck on the following:

 

I want to filter my data table with multiple ComboBoxes, but for some reason I can't load more than 7 rows (I know I put 7 as a limit, but I thought this would not be affected with the delegation issue?)

 

So my ComboBox looks like this:

 

Items = '[dbo].[Statuses]'.Status_Name

 

and

 

Items = GeneralInfoView.Department_Name

 

  • '[dbo].[Statuses]' is my table in SQL and Status_Name is the column name.
  • GeneralInfoView is my view in SQL and Department_Name is the column name.

I thought it had something to do with Distinct, but even when I changed it to above scripts, it doesn't show me more than 7. Obviously I will change that limit to 500 or 2000, but even then it is not enough. How is it possible to get more than 2000 options in your filter? Is there a way for that?

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

Hi @Gjakova 

The short answer to your question is yes, the ComboBox will have a limit.

Let's suppose that your SQL view returns 4,000 rows. When you set the items property of a data table or a gallery to the view, the data control will load an initial batch of records. When the user scrolls to the bottom of the control, Power Apps loads the next batch of records until all 4,000 rows are displayed.

 

Let's take the example of a Combobox with the default setting of 'search enabled'. The combo box will search the SQL view for records that match the search criteria, and will populate the combo box with the top 2,000 rows (assuming that the data row limit is set to 2,000). Unlike the data table or gallery control, Power Apps will not load a subsequent 2,000 row batch when the user reaches the bottom of the combo box list. Therefore, there is a limit of 2,000 rows. In practice, this does not prevent a user from selecting the desired record in a combo box, because a search will typically reduce the list of selectable items to under 2,000 rows.

 

To clarify this a bit further, let's now suppose that you decided not to use a SQL view for the items property of your combo box, but to use a non-delegable expression that utilises the LookUp function from your previous post. In this case, the combobox will search only the top 2,000 rows of the base SQL table, and will most likely not return all expected results, as it will not return any matching records from row 2,001 onwards.

 

Therefore, the setup that you have here with the items property set to a view will work OK in practical terms where you want to provide a combo box control for record selection purposes.

View solution in original post

6 REPLIES 6
timl
Super User
Super User

Hi @Gjakova 

Could you clarify, or post a screenshot of the formula for your data table control? Specifically, is there any blue highlighting in the formula that suggests a delegation problem?

Hi there @timl,

 

Filter(
    GeneralInfoView,
    Dropdown_Filter_11.SelectedText.Value = Type,
    ComboBox_Filter_43.Selected.Result = CaseNumber || IsBlank(ComboBox_Filter_43.SelectedItems) || IsEmpty(ComboBox_Filter_43.SelectedItems),

    ComboBox_Filter_44.Selected.Department_Name = Department_Name || IsBlank(ComboBox_Filter_44.SelectedItems) || IsEmpty(ComboBox_Filter_44.SelectedItems),
    
    ComboBox_Filter_41.Selected.Insured = Insured || IsBlank(ComboBox_Filter_41.SelectedItems) || IsEmpty(ComboBox_Filter_41.SelectedItems),

    ComboBox_Filter_45.Selected.Status_Name = Status_Name || IsBlank(ComboBox_Filter_45.SelectedItems) || IsEmpty(ComboBox_Filter_45.SelectedItems),

    StartsWith(ContractID, tbSearchContractID_1.Text),

    ComboBox_Filter_46.Selected.Result = SigningYear || IsBlank(ComboBox_Filter_46.SelectedItems) || IsEmpty(ComboBox_Filter_46.SelectedItems)
    
)

 

Above you can see how I filter my data table. It doesn't show me any warnings. To search the ID of my contract I applied the same method as the other ComboBoxes, but because of above issue I replaced it with a Search Box (text input field). On the ComboBoxes I applied the formulas which I had written in my opening post.

 

So when I use GeneralInfoView on my Data Table it shows me everything, but when I use it on a ComboBox or Dropdown it shows my max. 7. Do ComboBoxes and Dropdown have certain limits?

 

timl
Super User
Super User

Hi @Gjakova 

The combo box control will return the items that match the search text that the user enters into the combo box control. It retrieves the top X search results from the data source, where X is the "data row limit" value. Therefore, this limits the items that are available for selection in the combobox control and this is the reason why you only see 7 items.

I believe the drop down control has an additional hard limit on the number of items that it can display, which is 500.

Hi @timl 
So you mean that the ComboBox itself has a limit? Because the Data Table uses a SQL view which you had provided for me last week in another topic, that one loads all the data I need. That same view is being used in the ComboBox so I thought it would show me all the data in the ComboBox, but I couldn't find anywhere in the documentation that ComboBoxes have a certain limit or so. Except for Drop Down having a max of 500 (which I assume cannot be "tricked" into loading more?).

timl
Super User
Super User

Hi @Gjakova 

The short answer to your question is yes, the ComboBox will have a limit.

Let's suppose that your SQL view returns 4,000 rows. When you set the items property of a data table or a gallery to the view, the data control will load an initial batch of records. When the user scrolls to the bottom of the control, Power Apps loads the next batch of records until all 4,000 rows are displayed.

 

Let's take the example of a Combobox with the default setting of 'search enabled'. The combo box will search the SQL view for records that match the search criteria, and will populate the combo box with the top 2,000 rows (assuming that the data row limit is set to 2,000). Unlike the data table or gallery control, Power Apps will not load a subsequent 2,000 row batch when the user reaches the bottom of the combo box list. Therefore, there is a limit of 2,000 rows. In practice, this does not prevent a user from selecting the desired record in a combo box, because a search will typically reduce the list of selectable items to under 2,000 rows.

 

To clarify this a bit further, let's now suppose that you decided not to use a SQL view for the items property of your combo box, but to use a non-delegable expression that utilises the LookUp function from your previous post. In this case, the combobox will search only the top 2,000 rows of the base SQL table, and will most likely not return all expected results, as it will not return any matching records from row 2,001 onwards.

 

Therefore, the setup that you have here with the items property set to a view will work OK in practical terms where you want to provide a combo box control for record selection purposes.

Thanks once again for your clear explanations! So if I get it right... The ComboBox can handle a max. of 2000 rows (or whatever you set up as the max. row limit under settings). But if you 5 million rows, and you have exactly 2000 rows with the word "Orange" in it, it will return all 2000 rows. And if you would've had 2100 rows with the word "Orange in it, it would only return 2000 rows and leave the last 100 rows with "Orange" in it out, correct?

 

One more question though, I have quite some duplicates within the column that I also want to use as a filter. Using a Distinct on it gives me errors, or at least... the ComboBox shows only blank rows. Would above description still work when using Distinct, or would it be better to create a View for that in SQL?

 

And if it is solvable in Power Apps, would you happen to know what could be going wrong? Has it something to do with the DispalyFields and/or SearchFields?

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 (5,701)