Skip to main content
Community site session details

Community site session details

Session Id : cV1boGQHp/34B8Z7IQMb1l
Power Apps - Building Power Apps
Answered

SQL Connected Drop Down not showing all POs

Like (0) ShareShare
ReportReport
Posted on 22 Jan 2020 15:08:00 by

Hi Guys, 

 

I have set-up combo boxes where if we enter a specific PO it pull all relevant information against the referenced PO. all of these combo boxes are connected to SQL database. Now the look-up work fine but I only see a very limited number of POs in the list and similarly a lot of POs do not look-up in the search but I can see them just fine when I look them up on SQL server. 

 

Am I missing something is the data point limit to PowerApps ? Any possible work arounds to this ? 

  • Community Power Platform Member Profile Picture
    on 10 Feb 2020 at 15:04:45
    Re: SQL Connected Drop Down not showing all POs

    @timl @mcolbert 

     

    Thank you both the cascading dropdowns work well now! and I am able to lookup all distinct POs. 

     

    I cant thank you all enough for your help here, really really appreciate it. This will hopefully enable to have much cleaner data moving forward. I thank you all for your support!

     

    Regards, 

    safi 

  • Verified answer
    mcolbert Profile Picture
    126 on 07 Feb 2020 at 15:46:54
    Re: SQL Connected Drop Down not showing all POs

    @Anonymous 


    Your items collection needs to filter the view, not just use the same view. This is the reason you are getting all of the branchs. What you are doing is creating what is called cascading parameters. You select a value for the first parameter (po number) and then the next parameter is filtered by the first (branch in your case).

     

    So just to level set, your PO Number dropdown is set to:

    Items : '[sbi].[PurchaseOrders_HeaderInfo]'

     

    In order to get teh cascading behavior, the branch dropdown Items need to be based on a filtered set matching the selected PO Number, or;

    Items : Filter('[sbi].[PurchaseOrders_HeaderInfo]', 'Purchasing Document Number' = ComboBox1.Selected.'Purchasing Document Number')

     

    Then in the branch dropdown property sheet "Value", select the branch number column as the value to display.

     

    Now, when you select a PO Number from the first dropdown, the filter will be applied to the Branch dropdown and the list of only the branches associated with that PO Number should be included. And the branch dropdown should default to the first branch in the list of matching branches.

     

    If you want something different defaulted, you can set the Default property with an expression that gets the value you want.

     

    If you want the branches to be sorted by branch number for example, then add SortbyColumns to the Items expression;

     

    Items : SortbyColumns(Filter('[sbi].[PurchaseOrders_HeaderInfo]'Filter('[sbi].[PurchaseOrders_HeaderInfo]', 'Purchasing Document Number' = ComboBox1.Selected.'Purchasing Document Number'), "BranchNumber", Ascending)

     

     

     

  • Community Power Platform Member Profile Picture
    on 06 Feb 2020 at 15:11:04
    Re: SQL Connected Drop Down not showing all POs

    @timl 

     

    Hi, 

    for the Branch DropDown:

     

     image.pngimage.png

     

    Items : '[sbi].[PurchaseOrders_HeaderInfo]'

    Default: LookUp('[sbi].[PurchaseOrders_HeaderInfo]','Purchasing Document Number'=ComboBox1.Selected)

     

    The result looks something like this : 

    image.png

     

    I am basically getting a long list of branches to choose from, I seek an output where the dropdown list filtered down to only the branches associated with the PO Number referenced earlier. In 95% of the cases, this will be a one to one relationship. 

     

    With respect to Vendor Name it will always be a one to one relationship but I applied the same formula at the items properties but unfortunately I am getting the same output. 

     

    Seems like we are very close to getting a working prototype here. I cant thank you all enough for continuous support. I will owe you all some beers or beverage of your choice. 

     

    Regards,

    Safi 

     

  • timl Profile Picture
    35,207 Super User 2025 Season 2 on 05 Feb 2020 at 10:33:19
    Re: SQL Connected Drop Down not showing all POs

    Hi @Anonymous 

    Can you confirm what you've set the Default property of your Branch Combobox to?

    The Default property specifies the selected item. So you would typically set the Items property to this:

    Items = '[sbi].[PurchaseOrders_HeaderInfo]'

    .. and then set the default property to this...

    LookUp('[sbi].[PurchaseOrders_HeaderInfo]','Purchasing Document Number'=ComboBox1.Selected) 

     

  • Community Power Platform Member Profile Picture
    on 04 Feb 2020 at 18:52:01
    Re: SQL Connected Drop Down not showing all POs

    @mcolbert 

     

     image.png

    I implemented the solution as you mentioned, and while I am getting the branch name correct as  you can see in the formula bar, that result does not show up in the actual DropDown. 

     

    Is there something I am missing ?

     

  • Community Power Platform Member Profile Picture
    on 04 Feb 2020 at 18:43:02
    Re: SQL Connected Drop Down not showing all POs

    @mcolbert  @timl 

     

    Hi Both, 

     

    I created a separate SQL View with all distinct POs along with the header information i.e. Vendor, Vendor Description, Plant, PO number. 

    SQL View [sbi].[PurchaseOrders_HeaderInfo] 

    Contained fields: PO Number, Vendor, Vendor Description, Plant 

     

    Now if you recall, the PO number combo box items are being used to look related information, i.e. PO Number entered will lead to other combo boxes being auto populated with information such as Plant, Vendor  etc. 

     

    The trouble I am having is that using my previous formulas are not yielding the same results. Please consider below :

     

    For instance for branch look-up combo box the item properties are as follows : 

    DropDown 

    LookUp('[sbi].[PurchaseOrders_HeaderInfo]','Purchasing Document Number'=ComboBox1.Selected) 

     

    Combo Box 1 Items properties :  '[sbi].[PurchaseOrders_HeaderInfo]' Fields : PO Number 

     

    The dropdown formula is not working for me and I am certain that my formula needs tweaking to accommodate the SQL View, can you gentlemen please help me with setting the correct formula to enable PO Number referenced look-up.

     

    Please let me know if you need any other info to help troubleshoot 

    image.png

     

     

  • mcolbert Profile Picture
    126 on 29 Jan 2020 at 18:56:55
    Re: SQL Connected Drop Down not showing all POs

    If I understand, after selecting a purchasing document number you want to know what the branch and vendor associated with that PO?

     Would there be a 1:1 relationship between purchasing document number and vendor and branch? Or is there a 1:many?

     

    If it's 1:1, you can add the columns to your distinct view and then just display what was selected; If the branch column is called BranchName for example, you could display the branch as Dropdown3.Selected.BranchName and follow the same for Vendor. 

     

    Dropdown3.Selected represents the row in the Items collection that is selected so the other columns can easily be referenced.

     

     

  • Community Power Platform Member Profile Picture
    on 29 Jan 2020 at 15:01:23
    Re: SQL Connected Drop Down not showing all POs

    @timl @mcolbert 

     

    Thank you guys for your responses. 

     

    I did check and the query is being passed onto SQL servers as per SQL Server.  I made the modification to the item properties to this :

    '[sbi].[PurchaseOrders_Distinct]'

     

    This worked and the just about all POs are now loading properly : 

     

    Now the other portion of our form is where I need some help. We use distinct PO's to pull information such as the following: 

     

    Branch --> Items Property = LookUp('[sbi].[PurchaseOrders_History]', 'Purchasing Document Number' = ComboBox1.Selected.Result) 

    Vendor Name ---> Items property = LookUp('[sbi].[PurchaseOrders_History]', 'Purchasing Document Number' = ComboBox1.Selected.Result) 

     

    I did change the reference to '[sbi].[PurchaseOrders_Distinct]'  on the above formula and but I do not see the value section showing on the right pane showing the 'Plant' field.  

     

    now my question is this, do I need to change the SQL View to base look-up other info based on the PO selected in the combo box prior? 

     

    I only have 3 dependent fields based on the PO number : Branch, Vendor Name, Vendor Account Number <--- all of these fields are there in the [sbi].[PurchaseOrders_History] table, I assumed that when we execute the distinct query on SQL the other fields would remain intact. 

     

    Can you guys please guide me as to how I can set-up the other fields to look relevant information based on the PO number input which is now working ? 

     

    Thank you so much for your help! I have been stuck on this for a while and so very happy to be one-step closer to launching this in my organization. 

     

     

     

  • timl Profile Picture
    35,207 Super User 2025 Season 2 on 29 Jan 2020 at 11:27:00
    Re: SQL Connected Drop Down not showing all POs

    Hi @Anonymous 

    The problem is that it's not possible to use Distinct in PowerApps against large datasets because it isn't delegable.

    With the formula below, PowerApps will retrieve a maximum of 2,000 rows from '[sbi].[PurchaseOrders_Distinct]', and return the distinct values from this subset of data. This is the reason why you see a limited number of SKUs.

    Distinct('[sbi].[PurchaseOrders_Distinct]','Purchasing Document Number').Result
    

    So the answer is to carry out the distinct operation at the SQL Server level by modifying your view like so:

    SELECT DISTINCT([Purchasing Document Number]) FROM
     (SELECT [Purchasing Document Number] FROM dbo.tblPurchaseOrders_History
     UNION
     SELECT [Purchasing Document Number] FROM dbo.tblPurchaseOrders_Recent
     ) AS [UnionedData]
    
    


    The Items property of your ComboBox will then simply become this.

    '[sbi].[PurchaseOrders_Distinct]'

    Hopefully, this will take you closer to resolving the problem.

  • mcolbert Profile Picture
    126 on 28 Jan 2020 at 19:17:17
    Re: SQL Connected Drop Down not showing all POs

    First, have you confirm the results from your view in SSMS?

     

    Second, have you monitored the query sent back to your data source using Profiler or Extended Events in SSMS. The PowerApp setting "data-row limit for non-delegable queries" by default is 500. When you look at the query passed to SQL Server it will likely include select top(500)... increasing this can return more rows, but cannot exceed 2000.

     

    There is also a reasonable limit for items you can work with in a drop down from a UI perspective. Maybe help us understand how many you need and if a filter on order number or date would be easier to narrow down the results.

     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2

Loading complete