SharePoint Lookup Column - Sort Dropdown Values

(0) Share

Report

Posted on by Matren

454 Super User 2024 Season 1

Hello Power Platform Community,

I have created a Lookup Column in SharePoint that looks up values in another SharePoint List.

The dropdown values are currently sorted (1-8) in ascending order. I would like the dropdown values to be sorted (8-1) in descending order.

This seems simple but has been challenging to pull off.

Do you have a solution? Can someone suggest JSON Column formatting to do a descending sort?

Thanks in advance!

Matren

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Michael E. Gernaey 53,963 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi,

Now, just tossing this out :-) I personally cannot stand using lookups. I much prefer simply storing the ID of the other List. IMHO much cleaner.

Is the # you are displaying the ID or some other Int field???

Is this a Canvas or Model Driven?

@WarrenBelz or @Pstork1 do either of you have a personal opinion on this just curious, for scale and filtering etc I just prefer ID versus lookup.

P.S. This is the Power Automate Forum, not the Power Apps Forum for next time.

Was this reply helpful? Yes No
Matren 454 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Michael E. Gernaey,

Thanks for your response! In answer to your comments/questions:

1. The number displayed is not an ID field. It is a text field that is a string that is alphanumeric. I have simplified it to a single number for the sake of example and privacy.

2. The issue I described is not a power app, it is a lookup field within a SharePoint list.

3. The other list which the lookup field is pointing to is populated from a power automate flow which pulls data from Power BI. I used a lookup field (instead of a flow) because it was a simple way for the user to select a value from another list with values that can change.

The fundamental question is how does one easily sort dropdown values in a SharePoint list lookup field?

Best,

Matren

Was this reply helpful? Yes No
David_MA 14,614 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

This seems to be more of a SharePoint question than a Power Automate question. The sort is done based on the ID of the item in the source list. I found this post that talks about some ways to sort it, but there are none I have tried: Sorting data through lookup column in SharePoint - Microsoft Q&A.

We use Nintex forms and when you add a lookup, with Nintex it allows you to choose a view to determine the order the items appear.

Was this reply helpful? Yes No
Matren 454 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @David_MA,

Thanks for your response! Based on your statement that "the order of the values in a Lookup dropdown column is determined by the ID field" from the source list, I first tried to sort the values in the source list based on the ID field, and that didn't work. The order of values in the Lookup column's dropdown remained the same.

It appears that the IDs in a Lookup Column are ALWAYS sorted in ascending order. In order to do a descending sort, one would have to reassociate those values with the IDs, so for example in my case, instead of value 1 relating to ID 1, it would need to relate to ID 8. Vice versa, value 8 would need to relate to ID 1.

I then resorted the values in Power BI, so that the last value would be related to ID 1, once it was drawn into the source list by a Power Automate Flow. That didn't work, because the flow contained an update step, which looked up on a key field that wasn't the ID.

To solve this issue, it appears I need to clear all values in the source list, and then run the power automate flow which will draw values from Power BI into the source SharePoint list. Instead of using update, a create step in the flow will be used, and then value 8 from Power BI will be drawn into the list as the first ID. Value 7 will be drawn into the list as the second ID and so forth.

The challenge with clearing out the values in the source list, is it will erase all the lookup values that were previously chosen by users. I tried it and had to go back and enter them again. I am reluctant to do it again.

Thus I am interested to see if there are any other thoughts on how to do this, or if the method I proposed above is the only way ...

Best,
Matren

Was this reply helpful? Yes No
Michael E. Gernaey 53,963 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Matren

What is the screen I am looking at with the Dropdowns?

If I wanted to sort in Power Apps for instance I would do

Sort('Secondary List with LookUp', MatrenId.Value,SortOrder.Ascending)

Sort('Secondary List with LookUp', MatrenId.Value,SortOrder.Descending)

Because Sort can deal with the Value (which you said was a int atm), where as SortByColumns does not.

So is that a SharePoint form or what and if its a SharePoint Form, just change it to be a Direct Power App created Form. I dunno what else to say, since I still don't know what I am looking at.

you keep saying fundamentality I want to sort, sort it where? In a drop down that is still not identified as what type of visualization. I just shared how to sort it based on lookups.

Was this reply helpful? Yes No