Filter items from SharePoint list with date and other filters

[Anonymous]

Team,

I am using the below code to filter the items in SharePoint list for the latest 3 days.

SortByColumns(Filter('Shift Details',DateNumber>=CutOffDate), "Date", Descending, "Shift", Descending)

For some time, the item is not presented between todays date and cutoff date. At that time gallaery is empty.

Ex. CutOffDate - 20190724 & DateNumber - 20190726

If there is no record on 24 & 25th of July, then it will display only today's item (26 Jul).

Please provide suggestion to have atleast latest 3 days record in any condtion.

Thanks

Philip

[SkiDK]

I made a test on my side and it works for me, although I made dummydata in a collection.

My dates are just numbers. When I want all the records from the last 3 dates (thus date = 3, 4 and 5) I get the third last date (3) and then filter all records with a date >= 3. All the correct values are shown as above.

I'll take a look into it a bit further, but the logic should be correct. Might be a difference with a collection or a datasource, but I'm not sure. (If this is the case, you might consider loading the datasource in a collection and work with the collection.)

I suppose your datasource contains a lot of records?

[Anonymous]

My datasource is SharePoint list and it contains more than 1 lakh record. DateNumber field values are 20190730, 20190723, 20190722, 20190721, 20190718 and so on.

It is not displaying with the latest date instead it displayed last year records. Please help me to resolve this issue.

[SkiDK]

Okay, because your recordamount is so large, Powerapps won't be able to process the data locally (max 500), which means we cannot use Distinct(), First() and LastN() (see links below).

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sour...

https://docs.microsoft.com/en-us/connectors/sharepointonline/

We'll have to directly query to correct table out of the database, which is quite a challenge in this case. 

I'll look further into it, but be sure to check it out too.

[Anonymous]

Okay thanks. Please share the formula when you apply it in the below scenario.

[SkiDK]

Hi @Anonymous 

Could you try next, might be a warkaround the delegation issue.

In your OnVisible screen (or anywhere) put the next code:

ClearCollect(colShiftDetails, Sort('Shift Details', DateNumber, Descending)) //Or you can also descend on ID if DateNumber doesn't do the job well

And then to filter to correct code as before but then with the collection:

Filter(colShiftDetails, DateNumber >= First(LastN(Distinct(colShiftDetails, DateNumber), 3)).Result)

This way you'll basically only get the last 500 items from your list on which you can use all functions. I suppose 500 records will suffice for the records of the last three days.

I haven't tested it this way yet so it is mainly a theory but I think it could work in a way like this. I wanted to leave the idea before I headed home. Let me know if it works.

[RandyHayes]

@Anonymous 

See if @SkiDK 's suggestion helps you out.  It should do the trick.

A couple things to add

1) If you are dealing with less than 2000 records, you can increase your record limit to a maximum of 2000 in your advanced settings.  500 is the default, but you can increase it.

2) You can also combine the suggested formula into one for the same results:

Filter(
    Sort('Shift Details', DateNumber, Descending),
     DateNumber >= First(LastN(Distinct(Sort('Shift Details', DateNumber, Descending), DateNumber), 3)).Result
    )
    

No need for the extra collection, and don't worry that this makes round trips to the datasource twice - it will not.  It will only have one.

[Anonymous]

Hi Randey,

Thanks for shared the formula. If I use the formula which you have shared, I am getting delegation warning even though I changed to 2000 in advanced settings. It is displaying more content.

Ex. it is displaying for the dates - 20190730, 20190723, 20190722, 20190721, 20190719and so on.

But for me, it should display latest 3 dates.

How to resolve this?

[Anonymous · Accepted solution]

Hi @SkiDK ,

I tried your approach and it returns all items. I changed the formula as below and it is displayed only the latest 3 dates.

SortByColumns(Filter(colShiftDetails, DateNumber >= Last(FirstN(Distinct(colShiftDetails, DateNumber), 3)).Result), "Date", Descending, "Shift", Descending)

I just changed the LastN to FirstN and First to Last.

Thanks for shared the solution, ideas and support.

[SkiDK]

@Anonymous 

Oh I see haha. Just a switch around, but I'm glad you figured it out.