Filtering Sharepoint list and retrieving newest item

(0) Share

Report

Posted on by schwibach

2 Moderator

As a beginner, I'm struggling still with some simple stuff unfortunately.

I have a Sharepoint list (BVJ_Zahlen) with the name of the organisation in the Title and want to know the last date/time that somebody from that organisation made an entry to the list.

I have the name of the organisation of the user stored in VARorganisation.

I was trying to do it like this:

Last(LookUp(BVJ_Zahlen;Lower(Title) = Lower(VARorganisation);Created))

I really appreciate any advice you could give!

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@schwibach

All SharePoint lists have a sequential ID column that is hidden but accessible. You can use First(Sort(BVJ_Zahlen; ID, Descending)).Title in a label. You need to use First(Sort( because Last() and Max() are not delegatable and will not find the right record if your list is >2000 items.

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report
@schwibach
Here's the code you need to do it. LOOKUP will grab the first record from the BVJ_Zahlen list sorted by newest to older date created.

LookUp(Sort(BVJ_Zahlen; Created; Descending); Title = VARorganisation))

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report

Thanks!
This returned the newest item.

I made a mistake in my question. I have the name of the organisation in the column "Direktorat" in that list and want to get only the newest item of that organisation.
So, I'd need to filter the column 'Direktorat' for the variable VARorganisation before getting the newest item.

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report

I changed the code to this:
First(Sort(Filter(BVJ_Zahlen; Direktorat = VARorganisation); ID; Descending)).Created

It returns me a date and time, but the formatting of the SharePoint list doesn't let me see if it's correct. It just shows me "3 hours ago"... I'll see about reformatting it.

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report

I added a new item to the list (and updated the connections to the Sharepoint lists in Powerapps),
but neither the code above nor this
First(Filter(Sort(BVJ_Zahlen; ID; Descending); Direktorat = VARorganisation)).Created
will show me the newest entry.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@schwibach

Avoid the "First(Filter" usage. As @mdevaney mentioned, your formula should be the following:

LookUp(Sort(BVJ_Zahlen; Created; Descending); Title = VARorganisation))

Have you tried that?

Also, and why I chimed in...if you are seeing your dates a "3 hours ago" in your SharePoint list and want to see the precise time instead, go into the column settings for Created (or Modified) and change the Display Format from "Friendly" to "Standard"

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report
Thanks mate...
Yes, I already changed the date format to Standard.

For some reason, Powerapps had not correctly updated the data source.
It is updated now, and this here works for me:
First(Filter(Sort(BVJ_Zahlen; ID; Descending); Direktorat = VARorganisation)).Created

I changed the other bit of code to
LookUp(Sort(BVJ_Zahlen; Created; Descending); Direktorat = VARorganisation))
because I had given the wrong column.
But it doesn't return any result.
Should the function lookup return just the first result or would it give an array seeing as there are mutiple items in the list that match the criteria?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@schwibach

Filter returns a Table. LookUp returns a Record.

Your second formula deals with the Created column to sort. You mention the first returns something and you are using ID for the sort.

Incidentally,

First(Filter(Sort(BVJ_Zahlen; ID; Descending); Direktorat = VARorganisation)).Created

Is equivalent to:

LookUp(Sort(BVJ_Zahlen; ID; Descending); Direktorat = VARorganisation).Created

The difference between the first and the second is that, in the first, PowerApps filters all of the records and returns an entire table of all the records that are found that match the filter criteria...and then returns the first one.

The second formula iterates through all of the sorted records and returns the first one it finds that matches the criteria.

So, it's more about performance and the amount of data transferred that differentiates the two. It is just a much better practice to get in to to never use the first one.

So, here are the things to try:

1)

LookUp(Sort(BVJ_Zahlen; ID; Descending); Direktorat = VARorganisation; Created)

2)

LookUp(Sort(BVJ_Zahlen; Created; Descending); Direktorat = VARorganisation; Created)

Your stated originally that your requirement was to get the most recent, so that would be date/time based, and thus sorting by Created (#2 above) would be best.

Technically the ID will be pretty much in the order of created in a SharePoint list and would get what you want if you sort by that, but, it was not what you originally stated.

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (1)

Report

Thanks a lot for the explanation.
I tried the second line of code and it worked nicely.

This is my second day with PowerApps, so I'm quite happy with what I can already get it to do.

Thanks again!

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@schwibach

Excellent! Keep on PowerApping...

Was this reply helpful? Yes No