Unanswered

SortByColumns using a Lookup Column and Filtering

(0) Share

Report

Posted on 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:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Prabhakar_S 735 Moderator on at

Like (0)

Report
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.

Was this reply helpful? Yes No
JWolmanSymplr 24 on at

Like (0)

Report

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})

Was this reply helpful? Yes No
Prabhakar_S 735 Moderator on at

Like (0)

Report
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.

Was this reply helpful? Yes No
JWolmanSymplr 24 on at

Like (0)

Report

What method would the ClearCollection go into?

Was this reply helpful? Yes No
JWolmanSymplr 24 on at

Like (0)

Report

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!

Was this reply helpful? Yes No