cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Sort result of a lookup from a sharepoint list

Hi all,

 

Having a frustrating issue that I cannot find a solution for!

 

I have two data sets (both Sharepoint Lists), although each table has different data they both have a unique identifier allowing me to lookup data from one set based off of a field from the other. Although this is working I am unable to "sort" the result by "most recent" or anything!

 

Both lists have been saved into the app as two seperate collections, 'VisitData' and 'LocalData'

 

Unique identifier column is called "Cost Centre" it is a 5 digital number formatted as text.

 

Label1.Text is the cost centre from the other data source, filtered by a location name to give me a single cost centre

 

My formula is as follows:

 

LookUp(VisitData,'Cost Centre=Label1.Text,'Date of Inspection')

 

The formula works, it uses the specified cost centre (label 1) from Data set A to look up that same value in Data set B and return the 'Date of inspection' value. However!! There are multiple dates of inspection per cost centre and I need the formula to return the most recent (which will be the last in the list). I have tried using the Last, Sort functions as well as a whole bunch of others but it just doesn't allow me to return the last in the list, it defaults to

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Sort result of a lookup from a sharepoint list

Hi @Crossman 

I suggest that you reference the two lists directly rather than to a label control.  It looks like you have a one to many relationship between the two lists.  If the Parent list is LocalData and the Child list is Visitdata, this assumes that each item in local data can have many Visits and  you would like to see the individual visits based on the location?  

Set up two galleries ,Gallery1 and Gallery2.  In the first, make the items property Localdata.  In the second, make the Items property Filter(Visitdata, "Cost Center" = Gallery1. Selected. "Cost Center").  Put some labels in each gallery.  When you select an item in Gallery1, Gallery2 will be populated by the visitdata for that location.  You can sort the items in each gallery based on one of the fields in each list respectively.  For example for Gallery2 if there is a field for visitdate, you can sort it as follows

Sort(Filter(Visitdata, "Cost Center" = Gallery1.Selected."Cost Center"),visitdate, Descending)

Will sort the items in Gallery2 by the most recent visitdate.  

View solution in original post

6 REPLIES 6
Highlighted
Super User
Super User

Re: Sort result of a lookup from a sharepoint list

Hi @Crossman 

I suggest that you reference the two lists directly rather than to a label control.  It looks like you have a one to many relationship between the two lists.  If the Parent list is LocalData and the Child list is Visitdata, this assumes that each item in local data can have many Visits and  you would like to see the individual visits based on the location?  

Set up two galleries ,Gallery1 and Gallery2.  In the first, make the items property Localdata.  In the second, make the Items property Filter(Visitdata, "Cost Center" = Gallery1. Selected. "Cost Center").  Put some labels in each gallery.  When you select an item in Gallery1, Gallery2 will be populated by the visitdata for that location.  You can sort the items in each gallery based on one of the fields in each list respectively.  For example for Gallery2 if there is a field for visitdate, you can sort it as follows

Sort(Filter(Visitdata, "Cost Center" = Gallery1.Selected."Cost Center"),visitdate, Descending)

Will sort the items in Gallery2 by the most recent visitdate.  

View solution in original post

Highlighted
Continued Contributor
Continued Contributor

Re: Sort result of a lookup from a sharepoint list

Hi @Crossman ,

You could first filter, then sort and then take the first item. Like next:

First(Sort(Filter(VisitData, 'Cost Centre=Label1.Text), 'Date of Inspection', Descending)).'Date of Inspection'

Check if this works.

Ski

 

Highlighted
Community Support
Community Support

Re: Sort result of a lookup from a sharepoint list

Hi @Crossman ,

Do you want to display the latest 'Date of Inspection' value within the Label?

 

Based on the needs that you mentioned, I think the First function and SortByColumns function could achieve your needs. Please consider modify your formula as below:

First(
      SortByColumns(
              Filter(VisitData, 'Cost Centre = Label1.Text),
              "DateofInspection",
              Descending
      )
).'Date of Inspection'

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Sort result of a lookup from a sharepoint list

Thanks for this, unfortunately it still returns an error. 

 

"Expected a Text Value" could this be because the date of inspection column is formatted as date rather than text?

 

Cheers, Jaz

Highlighted
Continued Contributor
Continued Contributor

Re: Sort result of a lookup from a sharepoint list

Yes, you can wrap the code (one of the previous posts) in the function Text() so it converts to text. You can format the date as well with that function, take a look at next page: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-text

Highlighted
Community Support
Community Support

Re: Sort result of a lookup from a sharepoint list

Hi @Crossman ,

Could you please show more details about the error message?

Could you please share a bit more about the 'Date of Inspection' column in your SP List? Is it a Date type column or Text type column?

 

If the 'Date of Inspection' column is a Date type column in your SP List, I think the formula I provided above could achieve your needs.

 

If you want to format the Date type value into a Text value, please try the following formula:

 

Text(First(
      SortByColumns(
              Filter(VisitData, 'Cost Centre = Label1.Text),
              "DateofInspection",    // replace here with actual colum name from your VisitData collection
              Descending
      )
).'Date of Inspection', DateTimeFormat.ShortDate)

 

Please share more details about the error message within your app, so we could track where the issue.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,181)