I'm fairly new to PowerApps, so forgive the lack of understanding. I have an issue which I hope there is either a practical workaround using AzureSQL or a quick fix on this as I've pored over this for the entire week and haven't found a workaround or a proper way to resolve this.
Firstly if you can view the idea is that I have tables in Azure SQL which is largely populated with a data input table for consumption and another for reference during the user selection only for use during either New or Edit on the selected record itself by the line item. So, two tables one for user input and entry named [dbo] [GlobalDataStatus] and another few tables of the richest one is a VIEW type table in the SQL server which is [dbo] [distinctCity]- which is trying its best to return the values of the Cities - in perspective of USA - no judging here its just how my client wanted the solution to be.
Building the cities into states (just for the sake of capping the distinct records into less than 500 in qty is cumbersome) and all that will mean I need to re-purposing of the entire tables again.
The solution for Cascading Dropdown is fairly simple in which the distinct selected value starts with the Parent = Region , Child = United States of America , City = Nashville. Here's where it gets hairy, I've seem that setting the data row limit in Advanced Settings in PowerApp - only allows up to 2000 rows to be displayed/returned on the app itself and no more. Keeping this in mind, the standard dropdown box only drags down up to 500 records (out of a needed 3183 records to be exact).
However, the distinct filter worked well in that it returned Nashville which is record number 1902 "phew". Kinda weird... or maybe I'm not understanding something here.
The Combo Box - However - I've not set it up properly yet, is amazingly able to display ONLY up to 2000 data rows in its dragdown selection criteria's BUT that it. I suspect it still boils down to the delegation limit. Maybe I don't know how to setup Combobox yet not too sure. Open to exploration?
I've been trying my best to figure out the best way but my records of cities under USA is more then 3000 in count of rows.
How do I build a solution based on this? **where, I need the user to click on the Dropdown Box and it shows all the needed selection of cities within a 3000+ selection list from [dbo][distinctCity], and when the user selects it, then updates main record as [dbo] [GlobalDataStatus]. The cycle rinses and repeats.
Item fx code Dropdown Box:
Distinct(Filter('[dbo].[distinctCity]',Region=DropdownRegionKey_tbl.Selected.Region && Country=DropdownCountryValue_tbl.Selected.Result),City)
Default fx code Dropdown Box:
If(GlobalData_EditForm.Mode = FormMode.Edit Or FormMode.View, Parent.Default, " ")
Update fx code for the DataCard "City" as seen below:
ANY help would be helpful at this point?
dropdown controls are hard limited to 500 items. Btw no one wants to scroll through
more items than that anyway. See if my post helps
Local collections don't have a record limit. If you want all 3000+ records, you'll need to create a local collection (ClearCollect), populate it with your data with successive calls, then use that collection as the items for your combobox.
Dear @Drrickryp and @Captjoemcd , Thanks for your responses. I have information that two other sources from separate regions on their opinions and not surprisingly is aligned with your thoughts with the options available. I'm typically slightly lazy and so are my users in terms of their patience.
I decided to workaround this by playing “slave” to the limitations of PowerApps. And chunked the data down instead. I was building a simple [dbo] of regions to countries to cities of which imagine having more than 3K rows when it comes to the USA. Instead what I’ve done is re-categorize them according to Alpha-ranges in spreadsheet and - get this, I changed back the DataRow Limit in Advanced Settings from 2000 back to 500 and the “Distinct/Filter” functions applied the same on the Item Properties for the Combobox, it is able to reference the accurate data dropdown by Alpha for example W_X ranges for the USA cities… first breakthrough, I had a beer after that. It was tasty.
I’m still learning and there are many ropes and strings to climb, and this was one very frustrating times always having to “cleanse” and “restructure” my tables so it works within Azure SQL and PowerApps. Not sure if others have had this "necessary evil" times. I still believe that Azure SQL is the way to go – but a lot of reconciliation is required between both platforms. The only respite and comfort is in the capabilities of SQL Server Management Server App (am on v18.5 during this post) itself and performing my queries from there.
I'm not Clear on the Collect function and have never really understood exactly how this concept works, unless someone makes a super simple video on how this is applied to helping with solving business issues - I will struggle. I basically did the above because I wanted PowerApps to continue performing as an optimized app as a platform so I just redesigned the table but the learning was tough, because I didn't know the above actually would WORK. Now I know it performs queries of all rows and return the first 500 matches on the same match e.g. W_X ranges - my rows on each alpha_sets never breached 500, so this was a safe haven.
The only question coming forth is would we rely on PowerApps to resolve Large SQL datarows or have a Primary Key and Foreign key design implemented from DataSource Azure SQL to finally have PowerApps perform as expected?
.... both are essentially Microsoft products so I hope internally, the team are allocating good resources to bring a good marriage between Azure and PowerApps.
Dropdown Combo Boxes to add details: I've never successfully been able to have multiple combo boxes stacked sequentially between 4 sets at a time when working with Filter and Distinct. A key tip on setting up Combobox properties was that my MultipleSearch = false. Then, in order to show default strings or values into the Combobox "pre-selected by default" from the main gallery during edit mode.
This is what I set on the DefaultSelectedItems property - it was the ONLY way to get it to work appropriately. Or that in order to at least "show" the items that was selected for that particular data card from the Main_Gallery.
This was a total shift of logic from the traditional method of regular dropdown button control elements within PowerApps. It seriously messed with my head, though I strongly believe combo boxes is the way forward so that as @Drrickryp has mentioned, it wouldn't make sense to "force" users to keep rolling that mouse wheel. I mean if it were me, I'd rather go type a value into a search box and the dropdown auto POINTS to that selection match - seriously saves time and to the point.
Thats the beauty of Combo Boxes at least to my knowledge (while maintaining its "uhhh, I can only return the first 2000 results and show you only 500 rows within my visible field of selections when you click me)
My filter and distinct functions remained within the Items property of the Combobox. This is the only way the default and the selective suggestions appear from the point of view on the dropdown options. Again, I'd like to reiterate that besides "chunking" my data as above in alpha_ranges to slim down categories of data rows, I've NOT used my main Azure SQL table for the DataSource, instead its from 4 different "distinct" SQL table VIEWs
Check out new user group experience and if you are a leader please create your group
Did you miss the call?? Check out the Power Apps Community Call here!
See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.
ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.