cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dyee4614
Helper V
Helper V

Delegation Help

Hi.  I just switched from Excel to sharepoint.  I had a few questions:

 

1) My dates field doesn't look like it has fully loaded.  It has been hours after the initial load.  Also, it only loads to 8/9 while my data source goes up to 8/22.

 dateloading.png

2) I switched to sharepoint so I could delegate.  However, I'm having problems with my formula:

I want to filter my data source to only values within the last 30 days

 

Filter(DataSource,(Value(Today())-30)< Value(DateColumn))

 

I'm getting warnings about delegation under the - and < symbols but they're both on the Microsoft approved Delegation list. 

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

 

What am I doing wrong?

3 REPLIES 3
Jeff_Thorpe
Super User
Super User

Date fields in SharePoint aren't delegable in a Filter. 

 

See this page for details:

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



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
RezaDorrani
Dual Super User II
Dual Super User II

Hi @dyee4614 

 

Date is not delegable

 

Trick is to create a number field and store the date as YYYYMMDD format

 

example 8/23/2019 - store in number field   20190923

 

and then run queries on this number field to avoid delegation

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

v-siky-msft
Community Support
Community Support

Hi @dyee4614 ,

 

To your first question, do you mean you didn’t load your data completely from the Excel?

How do you convert your Excel data to SharePoint? How many rows of the Data? Maybe you can try again.

Please refer to this doc: Export an Excel table to SharePoint

 

To your second question, the DateTime Column value are not delegable by SharePoint right now.

for more information, you can read this doc:  PowerApps delegable functions and operations for SharePoint

 

If you want to overcome the 500 items limitation, you can increase the total limit items from 500 to 2000(Max) if the amount of record is under 2000.

You can find it from File (top left corner) > App settings > Advanced settings > set value for non-delegable queries.

If the amount is more than 2000, one way to do it is to somehow get all of the results into a collection.

The Collect and ClearCollect are not delegable, so you would have to split your Filter function so that it only collected <2k records at a time

Note: I assume the amount of data source is 6000, and have the primary identify type column “ID”.

Concurrent(
    ClearCollect(VarCollection1, Filter(dataSourceRead, ID >= 1 && ID <= 2000)),
    ClearCollect(VarCollection2, Filter(dataSourceRead, ID >= 2001 && ID <= 4000)),
    ClearCollect(VarCollection 3, Filter(dataSourceRead , ID >= 4001 && ID <= 6000)),
    );
ClearCollect( VarCollection, VarCollection 1, VarCollection 2, VarCollection 3)

Tips: If you don’t know the amount of datasource, you can check this:

UpdateContext({firstrecord: First(datasource)});
UpdateContext({lastrecord: First(Sort(datasource, ID, Descending))});
Amount= lastrecord.ID - firstrecord.ID

Modify your formula, use Collection as datasource:

Filter(VarCollection, Value(Today()-30)<Value(DateColumn))

Please have a try with above workaround, and check if the issue is solved.

 

Best regards,

Sik

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (30,063)