cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pearldrumbum
Level: Powered On

Concatenate multiple lookup values

I have a dropdown that is tied to a column in my list called 'Stores'. This column is actually a lookup from another list of stores containing more information such as store numbers, addresses, etc. When I created the lookup column I also checked the boxes for Stores:City, Stores:State, and Stores:Number so that I could use that info as well for display purposes. I've successfully concatenated columns into a dropdown list before but it was easy to do when both columns were text values (ex: [FirstName] + [LastName]).

 

What I want is for the dropdown list to present choices in the form of "[Store:#] - [Store:City] - [Store:State]"

 

How can I accomplish this? Simple concatenating doesn't work because they aren't text values, and I'm not sure how to convert lookup types to text values.

1 ACCEPTED SOLUTION

Accepted Solutions
strategery
Level 8

Re: Concatenate multiple lookup values

I think perhaps the easiest way to tackle this is using a calculated column in your 'Stores' list

 

The formula for the column would be something like:

=StoreNumber & " - " & StoreCity & " - " & StoreState

Now in 'myList', your lookup column will point to this calculated column. This should take care of it without a lot of extra work

 

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Concatenate multiple lookup values

Hey @pearldrumbum, you may try wrapping the Store # in a Text function, like:

 

Text('Store Number')

 

That would convert it to text. I am guessing the other values should be text.

 

Also guessing from the name you are a drummer? Guitar player here

pearldrumbum
Level: Powered On

Re: Concatenate multiple lookup values

Rock on... I am indeed a drummer 🙂

 

So here's what's currently in the dropdown object under 'Items':

Choices('myList'.StoreNumbers)

That works fine, but it just shows store numbers, which members of my team suggested is too vague, as not everyone who will be using this tool have store numbers memorized, so they want it to show ['#' - 'City' - 'State'] for each item. So I tried:

Choices(Concatenate(Text('myList'.StoreNumbers), Text('myList'.StoreCity)))

and that's throwing error for Choices (invalid argument). Even though StoreCity is a text field, it seems to require the Text function, because StoreCity is actually a Lookup column from another list. 'myList' doesn't recognize it as text.

 

 

pearldrumbum
Level: Powered On

Re: Concatenate multiple lookup values

Actually that seems to not even be working. Text([Lookup Value]) doesn't work. "Expected a text or numeric value"

strategery
Level 8

Re: Concatenate multiple lookup values

I think perhaps the easiest way to tackle this is using a calculated column in your 'Stores' list

 

The formula for the column would be something like:

=StoreNumber & " - " & StoreCity & " - " & StoreState

Now in 'myList', your lookup column will point to this calculated column. This should take care of it without a lot of extra work

 

View solution in original post

pearldrumbum
Level: Powered On

Re: Concatenate multiple lookup values

That's actually exactly what I ended up doing 🙂 Much easier solution.

 

Now my problem is sorting this list. Store numbers range from 0001 to 8xxx. At the very bottom I have some generic entries like "All Stores", "Temporary Stores", "Full Size Stores" Etc and I'd like those to appear at the top.

strategery
Level 8

Re: Concatenate multiple lookup values

Add another column to your stores list, something like SortVal, and leave it as "not required". Only enter values for the ones you want to appear at the top, for example:

"All Stores" -> SortVal=0

"Temporary Stores" -> SortVal=1

"Full Size Stores" -> SortVal=2

0001 thru 8xxx -> (leave blank/null)

 

Then

Items = SortByColumns(StoresList, "SortVal", Ascending, "StoreNumber", Ascending)

 

Records with a SortVal entered get sorted to the top in order, then all the blank/null SortVal records get sorted by StoreNumber

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,079)