Unanswered

Trouble using SQL in a combobox and displaying records as text

(0) Share

Report

Posted on by rboyd3

I am attempting to use an SQL connection to populate the items in a combo box. It will display a list of ID numbers for the user to choose from, and write the selected value to an excel table once the form is submitted. I also want to display in adjacent labels the first name and last name of individuals associated with the ID number so that the user can confirm that they made the right selection.

I am able to populate a combobox control using this formula:

ITEMS = BIOGDATA.STUDENT_ID
DisplayFields = ["STUDENT_ID"]
SearchFields = ["STUDENT_ID"]

The name of the SQL table is BIOGDATA and the column I want to select values from is STUDENT_ID. This successfully populates the combo box with the ID numbers I want, but, for some reason when I try to set the IsSearchable property to true, it immediately reverts back to false. This is essential because there are nearly one million rows of IDs that users need to wade through.

I thought I would try making my own search box by adding a text input control set to accept numerical values, and then filter the combo box using the text input.

Items = Filter(BIOGDATA.STUDENT_ID, Text(StuID_TextInput.Text, TextFormat.Number) = STUDENT_ID)

However, this results in an error because I am comparing a text value to a numerical value, even though the text input control's text format is set to TextFormat.Number and I am converting the input text to a numerical format in the formula. How do I resolve this?

I am hoping the solution will also allow me to add labels that display the associated student first names and last names for the selected ID. I'm also having issues there because those are record values and I am trying to display them as text.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

timl 37,269 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@rboyd3

>> for some reason when I try to set the IsSearchable property to true, it immediately reverts back to false.

The reason for this behaviour is because search only works against text columns. If the data type of Student_Id is INT, the IsSearchable property will revert back to false as you describe.

The work around around for this is to create a view that's based on your BIOGDATA table and to return an extra column where you cast Student_Id to a VARCHAR data type.

>> I thought I would try making my own search box by adding a text input control set to accept numerical values, and then filter the combo box using the text input

This is the correct syntax:

Items = Filter(BIOGDATA.STUDENT_ID, Value(StuID_TextInput.Text) = STUDENT_ID)

Was this reply helpful? Yes No
rboyd3 46 on at

Like
a
(0)

Report
Copy link

Link copied!
@timl Thanks for your response!

The work around around for this is to create a view that's based on your BIOGDATA table and to return an extra column where you cast Student_Id to a VARCHAR data type.

This is done in the SQL server itself, correct? Unfortunately that is not an option for me because I have view-only access to the server (and getting the IT dept. to agree to do this is a long and arduous process that's not worth the time). Do you know of any other workarounds that I can do within power apps?

Additionally, the syntax you shared below throws this error message: "Nested errors not supported in this control property: The value 'Text Input' cannot be converted to a number. Location: StuID_ComboBox.Items"

Items = Filter(BIOGDATA.STUDENT_ID, Value(StuID_TextInput.Text) = STUDENT_ID)

Is there any way within power apps to convert a record, table, or numerical value to a text string?

Was this reply helpful? Yes No
timl 37,269 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@rboyd3

As the Items property of the combo box returns only a numeric column and the combo box requires a text column, you can use this syntax to add a text representation of student_id.

Items = AddColumns(Filter(BIOGDATA, Value(StuID_TextInput.Text) = STUDENT_ID), "STUDENT_ID_Text", Text(STUDENT_ID) )

Was this reply helpful? Yes No