Filter a non-distinct list into a distinct list

[Anonymous]

Hi 

I am currently having some trouble trying to filter a sharepoint list into distinct list based on a latest dates for each entry with the help of a searc bar. 

The sample table that I currently have formated up to current looks like this: 

Carpark lot number	Last updated	current status
A	11/26/2018	Used
A	11/20/2018	Empty
A	11/15/2018	Used
B	11/24/2018	Empty
B	11/15/2018	Used
C	11/16/2018	Empty
C	11/15/2018	Used
D	11/15/2018	Empty

My current code for items to achieve this format is as follows :

SortByColumns(SortByColumns(Search('sample table',SearchText1.Text,"Carpark lot number"),"Last Updated",Descending),"Carpark lot number",If(SortDescending1, Descending,Ascending))

However, what I am looking to do is as follows: 

Carpark lot number	Last updated	current status
A	11/26/2018	Used
B	11/24/2018	Empty
C	11/16/2018	Empty
D	11/15/2018	Empty

Is there a way to get to the desire format? I have tried the distinct function but it only gives me a single entry from the entire table due to each entry having a different latest date. 

The code that I tried looks like this: 

Filter('sample table', Last updated =First(Sort(Distinct('sample table',Last updated),Result,Descending)).Result)

Hope that I can get some advice on how to modify my codes. Thanks for the help.

[TimW]

I'll have a go - using an intermediate collection (I haven't tested it):

ClearCollect(colSortedSampleTable, Sort('sample table', 'Last updated', Descending));
ClearCollect(colLatestItemSampleTable, Filter('sample table', LookUp(colSortedSampleTable, 'Carpark lot number' = 'sample table'[@'Carpark lot number'])))

or if it has to go into a non action formula then:

Filter('sample table', LookUp(Sort('sample table', 'Last updated', Descending), 'Carpark lot number' = 'sample table'[@'Carpark lot number']))

but this doesn't look very efficient - the sort runs once for each record!

[Anonymous]

Hi TimW, 

Thanks for the advice, however I seem to have an error with both methods. 

For the clearcollect method, the item property of the browse gallery list does not accept the clearcollect function at all. Am I suppose to put it there in the first place? 

As for the filter method, I seem to have an error with lookup function especially with the condition section. It seems to be expecting a different argument type, which in turn causes the filter condition to fail. 

Is there any way to fix the errors? 

[TimW · Accepted solution]

OK, change of approach - I tested this although with a different table and field names:

AddColumns(GroupBy('sample table', "Carpark lot number", "Details"), "Latest", First(Sort(Details.'Last updated', Descending)).'Last updated')

Breaking it down, GroupBy creates a new table with a column for the key (Carpark lot number) and a column containing a sub table for the details (in this case the column is also called Details). AddColumns adds a new column to this table called Latest which is calculated by sorting the Details sub table by Last updated and then getting the first result.

Hope that makes sense!

[Anonymous]

Hi TimW, 

Thanks for the advice, it works!

However, how to I link it back to the old table? Cause I have a link to for the entries to be edited. 

[TimW]

You should be able to use the Ungroup or LookUp function to do that - how is it linked?

[Anonymous]

Hi TimW, 

Thanks for the reply, the list is currently the gallery page of the standard 3 page setup given by the power apps.

After I have managed to filter the list into the new table, the orginal links to the details page and edit page will no longer work. (The data from the pervious list will also no longer be displayed on the gallery list as well). 

[TimW]

You have a table like this:

Carpark lot number	Last updated	current status
A	11/26/2018	Used
A	11/20/2018	Empty
A	11/15/2018	Used
B	11/24/2018	Empty
B	11/15/2018	Used
C	11/16/2018	Empty
C	11/15/2018	Used
D	11/15/2018	Empty

which has been summarised to this:

Carpark lot number	Last updated
A	11/26/2018
B	11/24/2018
C	11/16/2018
D	11/15/2018

What should happen when the user clicks on the summarised row? For example, if they click on Carpark lot number A then there are 3 related records:

Carpark lot number	Last updated	current status
A	11/26/2018	Used
A	11/20/2018	Empty
A	11/15/2018	Used

[Anonymous]

Thanks for your help with this post @TimW!

@Anonymous