Solved: Re: SQL Connected Drop Down not showing all POs

Anonymous · ‎01-22-2020

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 ?

timl · ‎01-22-2020

Hi @Anonymous

The workaround is to create a view like this in SQL Server..

CREATE VIEW [vwDistinct_PurchaseOrders] 
AS
SELECT DISTINCT [Purchasing Document Number]
FROM [sbi].[PurchaseOrders_History]

You would then add the view [vwDistinct_PurchaseOrders] as a data source to your app, and set the Items property of your combobox to this view. That should hopefully solve your problem.

View solution in original post

mcolbert · ‎02-07-2020

@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)

View solution in original post

mdevaney · ‎01-22-2020

@Anonymous

I don't think there's enough information here to answer your question. Please show your code.

Anonymous · ‎01-22-2020

this is the code I am using for the PO look-up : Distinct('[sbi].[PurchaseOrders_History]','Purchasing Document Number').Result

Screenshot attached: now while the 658 PO is searchable and pops up in the combo box list. The 261 PO does not.

I am confused as to why the combox is unable to search or populate the complete list of POs. The same PO look-up just fine when I look it up through SQL server.

Thanks for your help!

timl · ‎01-22-2020

Hi @Anonymous

The reason for this is because the Distinct function isn't delegable. There's more information here on this topic.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-distinct

With SQL Server, the workaround would be to create a SQL Server view that returns the distinct values, and to use that to populate your combobox.

mdevaney · ‎01-22-2020

@Anonymous

Agreed with @timl. Suggest you take his advice.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Anonymous · ‎01-22-2020

Thank you for your response, much appreciated it.

Can you please elaborate more on the work around, this actually the first tool that I am building and not sure where to go from here to have the complete data pull.

Anonymous · ‎01-22-2020

@timl I guide from Microsoft states that, there will be a delegation warning when the it may not be possible to do so, I do not see any warning on my application when I try to run it.

What can I do at this point to have a full pull from the SQL data source?

timl · ‎01-22-2020

Hi @Anonymous

The workaround is to create a view like this in SQL Server..

CREATE VIEW [vwDistinct_PurchaseOrders] 
AS
SELECT DISTINCT [Purchasing Document Number]
FROM [sbi].[PurchaseOrders_History]

You would then add the view [vwDistinct_PurchaseOrders] as a data source to your app, and set the Items property of your combobox to this view. That should hopefully solve your problem.

Anonymous · ‎01-22-2020

@timl

Thank you so much, quick question here, I have other comboboxes that look-up values based on the PO number referenced, would those need a separate (distinct) SQL view to work in tandem with the PO view.

Thanks for your help. I will try it out and get back to you on whether it worked.

timl · ‎01-23-2020

Hi @Anonymous

So long as you want to populate your other comboboxes with the same distinct values of POs, you can use the same view, rather than create separate views for each combobox.