Problems with delegation when filtering by date

[PYGDL]

Good day to all.
I have a problem with an application in which users register hours invested in work. In a single day they can make several registrations and at the end of the month or the fortnight in the application they could see how many hours they registered.
The records are stored in a Sharepoint list, currently there are around 15k.

The problem is that users for a while can no longer see their records made in the gallery, the data appears blank. The formula indicates a delegation error.

My formula what it does is filter the gallery according to the month that the user chooses and is the following:

If (Month_1.SelectedText.Value = "January"; Filter ('Hours_Inverted'; DateValue (Text ('Activity date'))> = DateValue ("25/12/2018") && DateValue (Text ('Date of activity ')) <= DateValue ("24/1/2019")))

The formula marks the DateValue part in blue. The funny thing about this is that the application works correctly in Team or powerapps web version, but in the desktop or mobile version it does not.

I have tried in many ways applying and modifying the filtering to apply the delegation but I have not had much success 😞

I would really appreciate it if anyone had any idea that I'm running away to be able to filter 15k records by date.

Regards!

[Pstork1]

DateTime values are not delegable in SharePoint.  If you want to filter based on the month then your best solution is to create a calculated column in SharePoint of type text and store the name of the month there.  I think you should be able to Filter on the month name with delegation.

[RandyHayes · Accepted solution]

@PYGDL 

If your datasource is SharePoint then there is no way to get around delegation warning (and incomplete results) for large lists when filtering with dates.  

Please reference the Delegable function, operations and column types for more information on what you can and cannot do.

So, the reality is, the only real problem you are having with that formula is that it works in one environment and not another.

Consider changing the formula to the following:

If (Month_1.SelectedText.Value = "January"; 
    Filter ('Hours_Inverted'; 
            'Activity date' >= Date(2018,12,25) && 
            'Date of activity' <= Date(2019,1,24)
           )
   )

You don't need to convert your column date to text and then back to a date again.  Also, the Date function will be a bit more reliable in this case.

NOW...that would only deal with the issue of working on other devices.  This does NOT resolve the delegation issue.

That is going to take more work.

In order for you to be able to delegate queries/filters on that list, you will need to do it by a delegable function and column type.  You could use a number or text to do this.

A solution you can use is to set up a Flow/Workflow on your list that when an item is created or changed, to have the Flow/Workflow copy the date value to a delegable column type.  So, for example, you could have it copy into a number column or a string column IN THE FOLLOWING FORMAT  - yyyymmdd

Now, once that is in place, you can use < or > on the numeric column without delegation issues. Or StartsWith on the text column.

Ex.  

    Numeric - 'Activity Date Num'  - Value:  20190102  

     You can use 'Activity Date Num' <= Date(2019, 2, 1)  without delegation issues

    Text - 'Activity Date Text' - Value:  "20190102"

     You can use StartsWith('Activity Date Text', "201901") to return all of Jan 2019 items...without delegation issues.

I hope this is clear and helpful for you.

[RandyHayes]

@Pstork1 

Calculated columns and "special columns" are not delegable either.  The only real solution to create these "alternative" columns is through Flow/Workflow.  OR, if your App is the ONLY consumer of the data in the list, then you can write the "alternate" column to the list at the time you create a new record.

[Pstork1]

I was afraid of that.  I normally try to stay away from calculated columns but it was the only workaround that I thought might work.

[RandyHayes]

@Pstork1 

Yep...it's like the first thing you think of when you want to do such a thing. But, PowerApps doesn't deal with it.  

In fact, there are several "special" columns that will appear on a SharePoint list (including ID, which is considered a special column) that cannot be delegated.  If you deal with a Document Library, you'll see a bunch of them that SharePoint kind of creates in it's signature, but they are all considered calulated/special.

Basically though, for those, if you don't see that column in your list definition, then it is not going to be delegable.

[PYGDL]

Thank you very much to both of you for your response.

@RandyHayes 

I had not come up with a solution like that. In the course of the week I will be doing tests to see if I have results.

Regards!

[v-xida-msft]

Hi @PYGDL ,

Based on the issue that you mentioned, I think it is related to the delegation warning issue.

Currently, the Date type column in SP List could not be delegated within Filter function. Please check the following article for more details:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#powerapps-delegable-functions-and-oper...

If you have faced a Delegation warning issue with formula in your app, you could not delegate the data process to your SP List, instead, you could only process data locally. In default, you could only process 500 records locally at most.

You could consider set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app, then you could process 2000 records locally at most.

In order to fix your problem, please check and see if the alternative solution within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

On your side, please take a try with the following workaround:

Set the OnStart property of the App control to following:

Concurrent(
          ClearCollect(col1, Filter('Hours_Inverted', ID >= 1 && ID <= 2000)),
          ClearCollect(col2, Filter('Hours_Inverted', ID >= 2001 && ID <= 4000)),
          ClearCollect(col3, Filter('Hours_Inverted', ID >= 4001 && ID <= 6000)),
          ClearCollect(col4, Filter('Hours_Inverted', ID >= 6001 && ID <= 8000)),
          ClearCollect(col5, Filter('Hours_Inverted', ID >= 8001 && ID <= 10000)),
          ClearCollect(col6, Filter('Hours_Inverted', ID >= 10001 && ID <= 12000)),
          ClearCollect(col7, Filter('Hours_Inverted', ID >= 12001 && ID <= 14000)),
          ClearCollect(col8, Filter('Hours_Inverted', ID >= 14001 && ID <= 16000)),
          ClearCollect(col9, Filter('Hours_Inverted', ID >= 16001 && ID <= 18000)),
          ClearCollect(col10, Filter('Hours_Inverted', ID >= 18001 && ID <= 20000))
);
ClearCollect(MergedCollection, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)

then set the Items property of the Gallery to following formula:

If(
   Month_1.SelectedText.Value = "January"; 
   Filter(
            MergedCollection; 
            DateValue(Text('Activity date')) >= DateValue("25/12/2018") && DateValue(Text('Date of activity')) <= DateValue("24/1/2019")
   )
)

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[PYGDL]

Good afternoon @v-xida-msft 

I tried to filter using the ID but I also get a delegation error 😞

I am making a list with more than 10k records to do some tests with @RandyHayes  solution to see if it works before making changes in production.

Regards!

[Pstork1]

Tests on the ID field are not delegable in SharePoint even if the operation is delegable.

"2The SharePoint ID field for a table is a number field in PowerApps. However, SharePoint only supports the equal ('=') operation for delegation on an ID field." From one of the footnotes in the delegation documentation on SharePoint.

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