cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bhanes22
Helper II
Helper II

SortByColumns with lookup column

Hello, I am having an issue with the sortbycolumns function, specifically with a lookup type column. Odd thing is that I have another lookup type column called country in my formula and it is working just fine, but this one is not, I think it has something to do with the Value. Anyway, here is my code for the items property of the gallery: 

SortByColumns(
    AddColumns(
        Filter(Transactions, 
            Search_Requests_2.Text in Title ||
            Search_Requests_2.Text in 'Request ID' ||
            Search_Requests_2.Text in 'External Person/Organization'.Value ||
            Search_Requests_2.Text in Submitter.DisplayName
        ),
        "Submitter Name", Submitter.DisplayName,
        "EPO", 'External Person or Entity'.Title,
        "Country Name", Country.Value,
        "Department", 'Function/Department'.Value,
        "Product_Line", 'Product Line'.Value,
        "Submission Date",'Date of Submission'
    ),

    varSort,
    If(varOrder, Descending, Ascending)
)

The column in question that is not working is the column "EPO". 

Here is the code for the onselect button of the header for this column. 

If(varSort="EPO",UpdateContext({varSort:"EPO", varOrder:!varOrder}),UpdateContext({varSort:"EPO", varOrder:false}))

is it possible to do sort by this lookup column? 

Also related to this, I have a separate list that displays answers to project questions and in this gallery, I am displaying general details of a project, which includes two of the answers in that separate list. Is it possible to sort on columns in this same gallery when one of the columns exists in another list? Or possibly sort on whats directly in the gallery maybe? 

 

6 REPLIES 6
RandyHayes
Super User
Super User

@Bhanes22 

Yes, you can do the same with a Lookup column.

 

A lookup column is a record with two columns - an Id (the ID of the record in the list) and a Value (the text value of the field/column from the looked up list - as defined in the lookup definition).

 

SO, if EPO is your Lookup column, then change your formula to:

SortByColumns(
    AddColumns(
        Filter(Transactions, 
            Search_Requests_2.Text in Title ||
            Search_Requests_2.Text in 'Request ID' ||
            Search_Requests_2.Text in 'External Person/Organization'.Value ||
            Search_Requests_2.Text in Submitter.DisplayName
        ),
        "Submitter Name", Submitter.DisplayName,
        "EPO", 'External Person or Entity'.Value,
        "Country Name", Country.Value,
        "Department", 'Function/Department'.Value,
        "Product_Line", 'Product Line'.Value,
        "Submission Date",'Date of Submission'
    ),

    varSort,
    If(varOrder, Descending, Ascending)
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Bhanes22
Helper II
Helper II

So that was my first thought, but Value is not an option for me

RandyHayes
Super User
Super User

@Bhanes22 

My guess is that it is not the correct name of the column in your list...the lookup column.

 

Based on the Filter part of your formula, it looks like it is : 'External Person/Organization'

That is perhaps the one you really want??

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Oh my god, Its been a long day, you're right I was using the separate list not the column name oooof.

Not to take up your night too, but any ideas on the other question I had regarding sorting on a column from another table in the same gallery? 

RandyHayes
Super User
Super User

@Bhanes22 

Long day here too!  It happens!!

 

How are you bringing in the column from the other table?  I am not seeing any reference to it in your Items formula!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

So I am looking them up via Project ID, each answer is associated with a project ID and then also each answer has its own corresponding question ID. My code for this label in the gallery is : 

LookUp(Answers, 'Project ID' = ThisItem.'Request ID' && 'Question ID' = 1).Answer

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,409)