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?
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.
So that was my first thought, but Value is not an option for me
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??
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?
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!
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
User | Count |
---|---|
253 | |
113 | |
92 | |
48 | |
38 |