Slow Loading Drop Down Values

[MelindaK]

Hi all,

I have an app which is in use for about 1 month now, and all of a sudden I have users complaining that drop downs are not populating anymore.

There are 3 drop downs with items from a SharePoint Online list (1021 items) which mimic the idea of a Region Area Branch structure - based on the region the area drop down is populated; based on region and area the branch is populated. I am using the Filter function to get the items and it does not apear with the blue icon.

Up until now it took seconds to load them - I've tested them today and it takes around 1,30 min. The other dropdown which gets items from another SharePoint online list (352 items) loads in about 5-10 seconds. 

Is anyone else having similar issues? I want to rule this out before I check with my networks team.

Thanks,

MelindaK

[m_takatsuka]

Hi

Have you acquired 1021 items by Filter?
So, the number of Filter acquisitions with no other problems is about 300 items
Perhaps I think that the number of extracted items that can be delegated to a data source was limited to about 500 (since it was a while ago, it may be more now)

Please try out the problematic Filter expression with 500 data sources.
So I think that the cause of the performance deterioration is beyond the number of delegated cases unless the blue attention is out.
I will introduce the following links for reference.
Https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

I think that it is serious, but I hope that it will get better.

[MelindaK]

I have 3 drop downs which are all based on the 1021 items from the SharePoint online list. Each drop down has a different number of items based on the previous selection (there's never more than 50 items in a drop down), and it was working perfectly up until last week when users started raising the issues.

As I said there is no blue dot, and the other drop down that is bringing around 300 items at once, is working fine. 

I think my question is more about what have changed in the past couple of weeks that slowed down the loading time.

[m_takatsuka]

Hi
Apparently I'm sorry I made a different answer than your intent.
I am also using the Powerapps application based on sharepoint online, but performance has not declined, especially recently here.

However, I am interested in a dropdown based on over 1,000 data sources you say.
I tried to reproduce your phenomenon and created 1,400 data on sharepoint online.
The sample data are fields of city, ward, town, and the data is grouped.

I made a Powerapps application with this sharepoint online as a data source.
I have three dropdowns, and I set the items properties to the following expressions.

City drop-down: Distinct (datasource, city)
districts drop-down: Distinct (Filter (datasource, city = City Dropdown.Selected.Value), districts)
Town drop-down: Distinct (Filter (datasource, districts = districts Dropdown.Selected.Value), town)

As you probably imagined, in the above method the Distinct statement read the data source 500 records.
So, in the above statement, I can not take a region-area-branch structure.
In this example it works with no blue marks, but I could not get the correct results.

When sharepoint online is used as a data source, I can not set Filter expression to multiple conditions, so I think that it is necessary to use another method to set conditions with three dropdown controls.

Can you tell me a sample of the dropdown equation that actually worked?
I think that there is a separate data source for the dropdown, or it may be that a collection is created every time a dropdown is selected.
I am thinking that you are using a wonderful method.

I deviate from your original question, but please reply if you like.

thank you.

[MelindaK]

No problem 🙂 we're all still learning here, so I'm more than happy to share my solution.

So my first drop down "Region" has the following function: SortByColumns(Distinct(BranchAreaRegion, Region), "Result") - which returns all the values from the Region column from my list (which are 10 distinct values)

The second one  "Area" has the following function: SortByColumns(Distinct(Filter(BranchAreaRegion, Region = [@ddRegion].Selected.Result).Area, Area), "Result") - which is based on the selection from the "Region" drop down (here are around 10-20 items at once)

And the final one "Branch" has the following function: SortByColumns(Filter(BranchAreaRegion, Region = [@ddRegion].Selected.Result && Area = [@ddArea].Selected.Result), "Branch").BranchRef1 - which is based on both the previous selections (here are around 10-30 items at once)

The only difference I can see is that from my filter function I always ask for the specifc column back, not the entire data set. 

I think I have had a similar situation when I wanted to display all these in a single drop down and only 500 were showing - which makes me think that the 500 limit is for when there's more than 500 items to be shown at once (but not sure).

Hope this helps 🙂 

[m_takatsuka]

I appreciate your kindness.
The taught code gave me a very nice impression.
Reasonable and elegant code was very helpful for me.
When I come back from the company today, I wanted to try your code and ran and went back.

I tried the code taught to my application.
Region dropdown is a single choice because there are 600 items in Region that correspond to the result in my data.
However, its idea is wonderful, so depending on the case it will be a very useful tool.

Also, in my 1600 sample data, the time to drop down was usable for a few seconds.

I thought of another means there.
The way to write will probably not be your reference.

My escape route added three fields to the data source and entered 1 in the first record of each of Region, Area, Branch.
I think that my cheat is already here for you to be wise.

Region: Filter (datasource, FirstOfRegion = "1"). Region
Area: Filter (datasource, Region = RegionDropdown.Selected.Value && firstOfArea = "1"). Area
Branch: Filter (datasource, Region = RegionDropdown.Selected.Value && Area = AreaDropdown.Selected.Value) .Branch

Anyway, I was able to operate the target dropdown, but I think that I can not bear practical use.
I regret that I can not serve you.
Thank you for telling me in reverse.

Cheers to your kindness and goodwill and talent!!!

[MelindaK · Accepted solution]

Just as a follow up to this, if anyone's interested, the issue has been fixed by raising a support ticket with Microsoft. Not quite sure what the problem was, but it has been resolved now 🙂