Filter Data source [after is 6 month ]

(0) Share

Report

Posted on by JHChoi81

Hi, I have sharePoint list that has about 4500 row.

So, I want to load the table with filter.

1. Filter 1 : Date > after 6 month

ex) Date > today -180.

2. Filter 2 : Status = "Ongoing"
Ex) Table (Row about 4,500)

Date	Value	Status
2019.1.1	x	Done
2019.1.1	Y	Ongoing
2019.1.3	x	Done

--Please help me

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

leyburn19 2,157 on at

Like (0)

Report

Using dates has delegation issues. It will only ever filter based on the delegation limit which is initially 500 but can be modified to 2000. No matter what you do it will only look at that amount of records for your filter. Any records outside the delegation will not be returned. Example:

You want a list where the Date >= Today()-180. If you are using 2000 settings it will return all the records created in the first 2000 that match it. If one one was created as the 2001 record it will not be returned. You can reverse your sort on ID to this based on the last 2000 records created

My work around is I archive any records no longer required as they are closed top a duplicate list. This keeps my list under the 2000. When I want to report on both I have the lists directly linked to Excel, where I combine the lists

Was this reply helpful? Yes No
JHChoi81 51 on at

Like (0)

Report

Okay,
IF I use only one Filter, Is it possible to reduce the row?
IF It is "Yes" , How to use ?

I want to use "search " to gallery view.
When I search ID 2001, It doesn't display in the gallery.

Thank

Was this reply helpful? Yes No
DeepakS 2,301 Most Valuable Professional on at

Like (0)

Report

Hi @JHChoi81

As suggested, when you use SharePoint as data-source in Power Apps, you should understand Delegation, or you won't get the desire results.
For your issue, generally, I try to filter large data first with delegable formula.
For example, First filter the data by status then apply date filter on it. This way, you can avoid the delegation issue.
Example:
Filter(
Filter(
SampleDATA,
ChoiceField.Value = "C1"
),
Created > Today() - 160
)
Please Note - In example above green formula is delegable, but the red formula will not, so this will work as expected if the output of the green formula is <=2000.

Regards

Was this reply helpful? Yes No
JHChoi81 51 on at

Like (0)

Report

I can't find where i write the formula.
I've test with create excel data that has 2834 rows.
and then, I applied filter at the " ITEM".
But, I can't see the above 2000 row.

How can I see it?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
@JHChoi81 ,
You cannot use Excel for more than 2000 rows, so that is the result you will get.
SharePoint as @DeepakS and @leyburn19 have explained is able to access more than this, but only with Delegable queries and a Date query is not Delegable. There is one possibility if all your dates under 180 days are in the last 2000 records. Also Date is not a good name for a field as it is a Reserved Word and can lead to Ambiguity.
So firstly collect the newest 2000 records (you will also need to set this limit in Advanced Settings)
ClearCollect( colMyCol, //call this whatever you want Sort( RenameColumns( YourSPListName, "Date", "MyDate", //call this whatever you want ), ID, Descending ) )
then the Items of your Gallery
Filter( colMyCol, DateDiff( MyDate, Today(), Days )<180 )
there is a further workaround involving a shadow numeric date field, but please explore the above first.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report

Hi @JHChoi81 ,
Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No