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

Dropdown items formula for distinct concatenation

My data source is a SP list that has Companies and Sites. The same Company ID and Site ID can show up multiple times, so the data will look similar to below.

smileamile_1-1599842805918.png

I have added a drop-down, but need help with the formula for the items. I need to concatenate the distinct company ID and Company Name, and sort by the company ID. So the result for the drop-down selection should be this:

101 - Alpha
102 - Bravo
103 - Charlie
104 - Delta

 

Ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User III
Dual Super User III

Hi @smileamile 

 

Can you try to update the expression to:

Sort(AddColumns(Distinct(ListName, 'Company ID'),"ConcatenatedColumn",Concatenate(Result, " - ",LookUp(ListName, 'CompanyID' = Result).'Company Name')),Result, Ascending)

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

8 REPLIES 8
Dual Super User III
Dual Super User III

Hi @smileamile 

 

Can you try to update the expression to:

Sort(AddColumns(Distinct(ListName, 'Company ID'),"ConcatenatedColumn",Concatenate(Result, " - ",LookUp(ListName, 'CompanyID' = Result).'Company Name')),Result, Ascending)

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

@yashag2255 

That seems to only return the Company ID, not the " - Company Name" part.

@smileamile 

 

Can you confirm if ConcatenatedColumn is selected in PrimaryText and Search fields on the combobox control? See attached image for reference.

 
 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

 

 

Got it. I was using Drop Down and the Combo Box is way better! Thanks @yashag2255 

@yashag2255  - If I have a second combo box for the Site that needs to be filtered based on what's selected in the Company combo box (single selection), what would that formula look like?

If I selected 103 - Charlie for the Company, I'd want to see the following options for Site:

004 - Miami

063 - Asheville

 

@smileamile 

 

In that case, you can set the Items property of the second combobox control to:

Sort(AddColumns(Filter(ListName, 'Company ID' = Combobox1.Selected.Result),"ConcatenatedColumn",Concatenate('Site ID', " - ",'Site Name')),"SiteID", Ascending)

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Fantastic! Thanks so much.

@smileamile 

 

Glad that it worked!

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (3,794)