cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Re: SQL Connected Drop Down not showing all POs

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!

Highlighted
Helper I
Helper I

Re: SQL Connected Drop Down not showing all POs

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 ? 

 

Highlighted
Advocate IV
Advocate IV

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.

 

 

Highlighted
Super User
Super User

Re: SQL Connected Drop Down not showing all POs

Hi @Safifaruqi 

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.

Highlighted
Helper I
Helper I

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. 

 

 

 

Highlighted
Advocate IV
Advocate IV

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.

 

 

Highlighted
Helper I
Helper I

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

 

 

Highlighted
Helper I
Helper I

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 ?

 

Highlighted
Super User
Super User

Re: SQL Connected Drop Down not showing all POs

Hi @Safifaruqi 

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) 

 

Highlighted
Helper I
Helper I

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 

 

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,835)