Answered

SortByColumns using a Lookup Column and Filtering

Like (0) Share

Report

Posted on 21 Dec 2023 19:07:13 by JWolmanSymplr

I am trying to do the following in the Items for my list (SharePoint list), but it's failing trying to sort by the "Look Up Field" field. Since it's a lookup I know I cannot sort by this column. I have researched and tried using the Add Columns but when I do that it's breaking a different part of my page where I am trying to use my sharepoint look up column and Patch a live change. What is the easiest way to sort by a look up column. In SharePoint you just click the column, why cannot we just tap into this in Power App for a SharePoint list.

SortByColumns

(

Filter

(

[@'API Resource Consumers'],

If (

ShowBlankConsumingProductsCheckbox.Value = true,

IsBlank('Consuming Product:ID'),

If(IsBlank(SearchConsumingProductInput.Text), true, SearchConsumingProductInput.Text in 'Consuming Product'.Value))

"Look Up Field", SortOrder.Ascending

)

Categories:

General Questions

All responses (5)

Answers (1)

JWolmanSymplr 20 on 21 Dec 2023 at 21:27:24

Like (0)

Report

Re: SortByColumns using a Lookup Column and Filtering

I was able to fix my OnChange method by doing this:

Patch('API Resource Consumers',LookUp('API Resource Consumers', ID = ThisItem.ID),{'Publishing Product':PublishingProductDropdown.SelectedText})

So with your solution and this solution, I feel I am able to continue with what I was doing. Thanks!
JWolmanSymplr 20 on 21 Dec 2023 at 20:48:07

Like (0)

Report

Re: SortByColumns using a Lookup Column and Filtering

What method would the ClearCollection go into?
Prabhakar_S 735 Super User 2025 Season 1 on 21 Dec 2023 at 19:28:31

Like (0)

Report
Re: SortByColumns using a Lookup Column and Filtering

You can create a separate collection that contains the sorted data and use that collection for your Gallery.

ClearCollect(
SortedData,
SortByColumns(
AddColumns(
Filter(
[@'API Resource Consumers'],
If (
ShowBlankConsumingProductsCheckbox.Value = true,
IsBlank('Consuming Product:ID'),
If(IsBlank(SearchConsumingProductInput.Text), true, SearchConsumingProductInput.Text in 'Consuming Product'.Value)
)
),
"LookupFieldSort", 'Consuming Product'.LookupField
),
"LookupFieldSort", SortOrder.Ascending
)
)

Use SortedData in your Gallery Items Property:

SortedData

In your 'OnChange' method, use 'ThisItem' from the 'SortedData' collection:

Patch('API Resource Consumers', ThisItem, {'Consuming Product': ConsumingProductDropdown.SelectedText})

This way, the 'OnChange' method and the Dropdowns will work with the sorted data and won't conflict with the sorting logic in the Gallery. Adjust the column names and types according to your actual data model.

Thanks!!!

Please consider marking my response as the accepted solution if it successfully resolves your concern. If you found the information beneficial in other aspects, kindly express your appreciation by giving it a thumbs-up.
JWolmanSymplr 20 on 21 Dec 2023 at 19:20:30

Like (0)

Report

Re: SortByColumns using a Lookup Column and Filtering

I have tried something similar to this but now my DropDowns in my VerticalGallery are failing in the OnChange method with this error: The specified column 'LookupFieldSort' does not exist. So this solution is breaking other code that works if I don't sort.

OnChange Method: Patch('API Resource Consumers',ThisItem,{'Consuming Product':ConsumingProductDropdown.SelectedText})
Verified answer

Prabhakar_S 735 Super User 2025 Season 1 on 21 Dec 2023 at 19:16:07

Like (0)

Report
Re: SortByColumns using a Lookup Column and Filtering

Hi @JWolmanSymplr ,

Try this,

SortByColumns(
AddColumns(
Filter(
[@'API Resource Consumers'],
If (
ShowBlankConsumingProductsCheckbox.Value = true,
IsBlank('Consuming Product:ID'),
If(IsBlank(SearchConsumingProductInput.Text), true, SearchConsumingProductInput.Text in 'Consuming Product'.Value)
)
),
"LookupFieldSort", 'Consuming Product'.LookupField
),
"LookupFieldSort", SortOrder.Ascending
)

Thanks!!!

Please consider marking my response as the accepted solution if it successfully resolves your concern. If you found the information beneficial in other aspects, kindly express your appreciation by giving it a thumbs-up.