Concatenate SQL server Column-Show result in ComboBox

(0) Share

Report

Posted on by omi18

640

Hello People,

I'm trying to concatenate columns as a Combobox list/search text. Below is my formula.
Distinct(SortByColumns(Filter(Concatenate('[travel].[CourseDesc]'.FirstName," ",'[travel].[CourseDesc]'.LastName),SemesterBox_1.Selected.Result).Result,"Result",Ascending),Result)

Thanks,

Omi

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@omi18
In general, you could combine FirstName and LastName in a new column called "Full Name" like this

AddColumns(your_datasource_name, "Full Name", FirstName & " " & LastName)

Then apply the rest of your code...

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
omi18 640 on at

Like (0)

Report
Copy link

Link copied!

Hi @mdevaney ,

Thank you for your reply.

I'm not sure, what I'm missing from the formula.

Distinct(SortByColumns(Filter(AddColumns('[travel].[CourseDesc]',"Full Name",FirstName & " " & LastName),SemesterBox_1.Selected.Result).Result,Ascending),"Result")

Thanks,
Omi

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @omi18 ,

Your latest formula still has some problems.

Could you tell me:

1)what is SemesterBox_1 used for

2)what is your filter rule? "SemesterBox_1.Selected.Result" could not represent the filter rule? filter based on which column?

Right syntax should look like this:

Filter(table,SemesterBox_1.Selected.Result in fieldname)

3)which column do you want to sort based on? "SortByColumns(.........Result,Ascending)" could not represent filter based on Result column.What's more, do you have column named Result in your '[travel].[CourseDesc]' table?

Right syntax should look like this:

SortByColumns(table,"fieldname",Ascending)

4)which column do you want to get no-repeated value? "Distinct(...,"Result")" could not represent distinct by the Result column.

Right syntax should look like this:

Distinct(table,fieldname)

5)'[travel].[CourseDesc]' represent what? a table? or a field? Whether does your table name '[travel]' or '[travel].[CourseDesc]'?

I assume that you have column named Result in table '[travel]',filter based on Result column , sort based on this column, distinct based on this column.

If so, try this formula:

Distinct( SortByColumns( Filter( AddColumns( '[travel]',"Full Name",FirstName & " " & LastName ), SemesterBox_1.Selected.Result in Result ),"Result",Ascending ),Result )

If my assumption is incorrect, please describe your feather more clearly.

Best regards,

Was this reply helpful? Yes No
omi18 640 on at

Like (0)

Report
Copy link

Link copied!

Hello @v-yutliu-msft ,

Thanks for your comment.

1)what is SemesterBox_1 used for: To filter the semester.
2)what is your filter rule?: Distinct(Sort(Filter('[travel].[CourseDesc]',Label2_1.Text = email1),Semester,Descending),Semester)

CourseDesc table has FirstName, LastName, Course, Section, and Semester columns.

In the attached image, the User will need to select the semester and bassed on the selected semester faculty names will get filtered. Then the user can search faculty by full name.

Let me know if you need more information.

Thanks,
Omi

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @omi18 ,

Ok, do you mean this:
1)filter based on semester?

2)search based on full name(FirstName & LastName)?

3)this search works for the combo box for faculty, you want to set this combo box's Items?

Could you tell me what does this represent "Label2_1.Text = email1"? You did not mention anything about Label2_1 or email1.

If my assumptions are all right, try this:

semester combo box's Items:

Distinct('[travel]',semester)

faculty combo box's Items:

SortByColumns( Filter( AddColumns( '[travel]',"FullName",FirstName & " " & LastName ), SemesterBox_1.Selected.Result in Semester ), "FullName",Ascending )

set faculty combo box's search field: FullName

//then you could search based on full name in this combo box.

Best regards,

Was this reply helpful? Yes No