Unanswered

Distinct Function Combo Box

(0) Share

Report

Posted on by R45

Hi all

I have a SQL table called Times which contains an Order No. column. I have tried using the distinct function to get these unique order numbers to appear as options within the combo box. This way any user can enter a new order number into the SQL database and only unique values will appear as options for the combo box. The code is as follows:

Screenshot 2023-07-25 121531.png

This outputs only two options and doesn't allow searching.

Any help would be great thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

victorcp 2,350 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi,

How any itens exist in your SQL?

Power Apps will retrieve the first 500 itens (you can also raise this limit to 2000) and then apply the distinct, if you have more itens than that I suggest changging this approach.

And you can enable the search here:

I hope it helps 🙂

Was this reply helpful? Yes No
R45 94 on at

Like (0)

Report
Copy link

Link copied!

Hi @victorcp

There is 65000 records in the database. What other methods would you propose?

Cheers

Was this reply helpful? Yes No
Talha_Dar 870 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @R45,

Here is an expression that displays only the first 500 items when no search is performed and filters the table when a value is added to the search.

If( ComboBox.SearchText = "", FirstN( Distinct( listName, columnName ), 500 ), Distinct( Filter( listName, columnName = ComboBox.SearchText ), columnName ) )

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
victorcp 2,350 Moderator on at

Like (0)

Report
Copy link

Link copied!

You can use the expression created by @Talha_Dar.

or

you can create another table with the all the available Order n°

Was this reply helpful? Yes No
R45 94 on at

Like (0)

Report
Copy link

Link copied!
Hi @victorcp @Talha_Dar

Thank you for your response. I have implemented the above code and it works. How can I make it possible to enter an order number and that appear in the combo box options once the record has been submitted. Right now I can only select from the combo box (works) or type a value in (fails)?

If(
OrderNumber1.SearchText = "",
FirstN(
Distinct(
Orders,
OrderDistinct
),
500
),
Distinct(
Filter(
Orders,
OrderDistinct = OrderNumber1.SearchText
),
OrderDistinct
)
)

Was this reply helpful? Yes No
Talha_Dar 870 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @R45,

You can use the following formula on the 'Update' property of your data card. It will update the value in your data source to the selected value if a value is chosen or create a new value if there is no existing value with a similar name.

Patch( listName, LookUp(listName, ID=1 // or some condition to get the record to update), { lookupColumnName: DropDown.Selected } )

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No