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

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
Super User
Super User

Re: Delegation Help

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.
Dual Super User
Dual Super User

Re: Delegation Help

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
Level 10

Re: Delegation Help

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
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,697)