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 ?
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
@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)
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
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)
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 ?
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
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.
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.
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.
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.
WarrenBelz
791
Most Valuable Professional
MS.Ragavendar
410
Super User 2025 Season 2
mmbr1606
275
Super User 2025 Season 2