Sort result of a lookup from a sharepoint list

[Crossman]

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

[Drrickryp · Accepted solution]

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.  

[SkiDK]

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

[v-xida-msft]

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,

[Crossman]

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

[SkiDK]

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

[v-xida-msft]

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,