Answered

Filtering, sorting collections (SharePoint)

(0) Share

Report

Posted on by Rob_CTL

Hi,

I am building out a tracker application, when app goes live it will a large dataset (held in SharePoint lists).

The plan is that I create a collection that filters on the users email address, there is a variable varUser that is set on app load, the email address is also stored in a column on the SharePoint list. The command below will return all items that match the filter but when that hits 500 the app will stop functioning correctly. The next part, which I am stuck on, would be to limit the number of items that are placed in the collection, ideally I only need the very most recent item. I have been playing around with SORT but I couldn't work out a way of doing a FILTER and SORT in the same collection command.

Any help on how I can achieve this would be gratefully received.

Thanks

Rob

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Rob_CTL

Yes, you can Filter based on the date sort.

Example:

Filter( Sort('Mileage Record', Created, Descending), Email = varUser.Email )

This by itself would give you the maximum record limit you have defined in your app (you mentioned 500, but you can go as high as 2000).

If you want to include an actual date range, then something like this would apply:

Filter( Sort('Mileage Record', Created, Descending), Email = varUser.Email && Created >= Date(2022, 1, 1) )

Just a pure example...this would give you all the records from Jan 1 to now for that user (or at least up to your record limit).

I hope this is helpful for you.

Was this reply helpful? Yes No
Rob_CTL on at

Like (0)

Report
Copy link

Link copied!

Hi @RandyHayes

Thanks for the quick response, that has helped a lot. Is there a way I can limit the number of results returned? or do I have to do that in the app settings?

Thanks
Rob

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

Like (0)

Report
Copy link

Link copied!
@Rob_CTL

Yes, you can utilize the FirstN function to limit to a specific number of results.

Keep in mind that this is "app-side", meaning that no matter what, the app will still pull up to the maximum number of results, and then FirstN will limit the table returned.

Example:

FirstN( Filter( Sort('Mileage Record', Created, Descending), Email = varUser.Email && Created >= Date(2022, 1, 1) ), 100 )

Will limit the results returned to 100 or less.

Was this reply helpful? Yes No
Rob_CTL on at

Like (0)

Report
Copy link

Link copied!
Hi @RandyHayes

Thanks again, I've tried the FirstN function but that doesn't return any results:

ClearCollect(colGetLastMileage, FirstN( Filter( Sort('Mileage Record', Created, Descending), Email = varUser.Email) ), 10 )
Maybe because I'm trying to wrap it in collection? With the FirstN removed I do get results in the collection, and sorted correctly.

Thanks
Rob

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@Rob_CTL

Not sure the value of the collection in this case unless you are planning to interact (edit. add, delete records) with it in your app.

You can alter the formula a little bit as the Formula Editor sometimes will change how it gets results from the backend based on the functions and order.

With({_items: Filter( Sort('Mileage Record', Created, Descending), Email = varUser.Email ) }, ClearCollect(colGetLastMileage, FirstN(_items, 10) ) )

Was this reply helpful? Yes No
Rob_CTL on at

Like (1)

Report
Copy link

Link copied!

@RandyHayes perfect, thanks!

Was this reply helpful? Yes No