cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PYGDL
Level: Powered On

Problems with delegation when filtering by date

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 Smiley Sad

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Problems with delegation when filtering by date

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
14 REPLIES 14
Super User
Super User

Re: Problems with delegation when filtering by date

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Super User
Super User

Re: Problems with delegation when filtering by date

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Problems with delegation when filtering by date

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Problems with delegation when filtering by date

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Super User
Super User

Re: Problems with delegation when filtering by date

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
PYGDL
Level: Powered On

Re: Problems with delegation when filtering by date

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!

Community Support Team
Community Support Team

Re: Problems with delegation when filtering by date

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PYGDL
Level: Powered On

Re: Problems with delegation when filtering by date

Good afternoon @v-xida-msft 

 

I tried to filter using the ID but I also get a delegation error Smiley Sad
IDtest.JPG
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!

Super User
Super User

Re: Problems with delegation when filtering by date

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/



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 162 members 4,575 guests
Please welcome our newest community members: