SQL Connected Drop Down not showing all POs

[Anonymous]

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 ? 

[Anonymous]

I just implemented the SQL View this morning and will be testing out the solution later today, I will get back to you as soon as I have something on it ! thanks guys!

[Anonymous]

Hi @timl  @mcolbert 

Implemented the SQL view 

SELECT [Purchasing Document Number] FROM dbo.tblPurchaseOrders_History

Union

SELECT [Purchasing Document Number] FROM dbo.tblPurchaseOrders_Recent

sbi.PurchaseOrders_Distinct <-- this was the resulting view which was then referenced to the PO Combo Box:

Combo Box Formula : Distinct('[sbi].[PurchaseOrders_Distinct]','Purchasing Document Number').Result 

BUT ! Still no look-up I still only see a limited number of SKUs now, I wonder if I have to modify the formula at the item level to see the desired result ? 

[mcolbert]

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.

[timl]

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.

[Anonymous]

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

[mcolbert]

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.

[Anonymous]

@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 

[Anonymous]

@mcolbert 

 

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 ?

[timl]

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) 

[Anonymous]

@timl 

Hi, 

for the Branch DropDown:

 

Items : '[sbi].[PurchaseOrders_HeaderInfo]'

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

The result looks something like this : 

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