cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pearldrumbum
Frequent Visitor

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

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
wyotim
Resident Rockstar
Resident Rockstar

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

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.

 

 

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

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

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.

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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