Filter on Date Picker not working

[pgd]

I have a canvas app connected to a Sharepoint list. I have a data table called datatable3. This has two columns: endproduction time and addedby. Endproductiontime is text.

On the same screen as the data table, I have a date picker called datepicker1. When the user clicks a button called barcodescanner2_1, it's onscan property is: set(barcodehistory,barcodescanner2_1.value).

Once the user scans a barcode, I want the data table to filter to only matching barcodes of that type that were scanned between today and the last 60 days.

For some reason, when I choose 12/1/2022 as the date in the datepicker, the table is sorted perfectly and returns the correct results ranging from October - December. When I choose 1/1/2023, however, it only returns one result, which is 11/21/2022. The table will not return any results after 11/21/2022. Nothing scanned in December 2022 or January 2023 is populating in the table. 

Here's the formula: 

SortByColumns(
   Filter(
      AddColumns(
         'Data',
         "_EndProductionTimeDATE",
         DateTimeValue(EndProductionTime)
      ),
      DateTimeValue(EndProductionTime) < DatePicker1.SelectedDate && 
      DateTimeValue(EndProductionTime) > 
      DateAdd(
         DatePicker1.SelectedDate,
            -60,
            Days
        ) && 
      Title in Barcodehistory
    ),
   "_EndProductionTimeDATE",
   Descending
)

[v-xiaochen-msft]

Hi @pgd ,

The formula is not delegable , please check whether there are more than 500 records in the SP list.

If the number of records does not exceed 2000, then you can manually modify the delegation data row limit from 500 to 2000.

If the number of records exceeds 2000, we recommend that you consider using delegable formula replacements.

I think this link will help you a lot:
Understand delegation in a canvas app - Power Apps | Microsoft Learn

Best Regards,

Wearsky

[pgd]

@v-xiaochen-msft I currently have 454 records, but I know this will soon exceed 500. How can I edit my formula so that it's delegable? 

[v-xiaochen-msft]

Hi @pgd ,

First,you need to create a new datetime column in 'Data', and replace "EndProductionTime" with the new datetime comlum, cause AddColumns/DateTimeValue/SortByColumns/in are not delegable , you need to compare a datetime value with a date time column.

Second, you could try this formula

Sort(

   Filter(

         'Data',

          NewDataTimeColumn < DatePicker1.SelectedDate &&

          NewDataTimeColumn > DateAdd(DatePicker1.SelectedDate,-60,Days) &&

          Title = Barcodehistory

    ),

   NewDataTimeColumn,

   Descending

)

Best Regards

Wearsky

[pgd]

@v-xiaochen-msft Would I add the column to the powerapp or the sharepoint list? If the answer is the powerapp, can you tell me where I should add it? Thanks. 

[v-xiaochen-msft]

Hi @pgd , 

Add a column to the Sharepoint list.

Best Regards,

Wearsky