cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nunzie
Helper III
Helper III

Sum with Tasks Sharepoint List

Hello,

I have a tasks list on our SharePoint site which I am trying to create a power app for to make it a little nicer/data entry friendly. I am trying to create a button on the first page which would give a total number of tasks that meet specific criteria.

 

So I'm trying to create an overdue button which says Overdue: #

 

I have the following code on my button:

"Overdue" & " " & Sum(ForAll(Filter('SI Tasks','% Complete'<100 && 'Due Date'<Today(),1),Value)

I tried to stay away from countif due to delegation issues. So basically what I'm saying is count the records where the % Complete is less than 100 and the due date was before today.

 

If I remove the && Due Date portion, the button seems to show the correct label and number. However, adding that second condition I get a delegation error and it doesn't show any text in the button at all.

 

Perhaps I've done something wrong in adding the second condition?

5 REPLIES 5
eka24
Super User III
Super User III

Try;

"Overdue" & " " & Sum(ForAll(Filter('SI Tasks','% Complete'<100 && Text('Due Date',ShortDate)<Text(Today(),ShortDate),1),Value)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

v-yutliu-msft
Community Support
Community Support

Hi @Nunzie ,

The reason why you could not get right result when adding the second condition is that datetime data type is not delegate in filter function for sharepoint list.

Delegate function for sharepoint list:

Item Number Text Boolean DateTime Complex [1]
Filter Yes Yes Yes No [4] Yes
Sort Yes Yes Yes Yes No
SortByColumns Yes Yes Yes Yes No
Lookup Yes Yes Yes No Yes
= Yes Yes Yes No [4] Yes
<, <=,<>, >, >= Yes [2] No No No Yes
StartsWith - Yes - - Yes
IsBlank - No [3] - - No

 

Could you tell me how many records are there in your sharepoint list?

If it is less then 2000 records, you just need to change the delegate limit to 2000.

delegation.jpg

If it is more then 2000 records, I think there's no function that could meet your demands.

In this situation, I suggest you use collection to filter to avoid the affect of delegation.

Firstly, save data to collection.

Set the app's OnStart:

ClearCollect(collection1,'SI Tasks')

Then use filter for this collection:

"Overdue" & " " & Sum(ForAll(Filter(collection1,'% Complete'<100 && 'Due Date'<Today(),1),Value)

In collection, there's no delegation limit. So you do not need to only use delegate functions.

 

 

Best regards, 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

There will be a point it is over 2000 records so ok, I'll have to learn what collections are/how to use them. Thanks so much for your help on this.

Hi @Nunzie ,

Using collection to avoid the effect of delegation is a good choice.

As what I have described, you just need to firstly save your data to collection, then filter on that collection instead of your data source.

If you have any problem, you're welcome to post your idea here.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your reply. Ok so on App OnStart I have the following:

ClearCollect(tasksCollection,'SI Tasks')

 

When I view the collection I can see the first five records.

 

Then on the Text of the button I have the following:

"Overdue" & " " & Sum(ForAll(Filter(tasksCollection,'% Complete'<100 && 'Due Date'<Today(),1),Value)

 

But it is giving an error again and nothing shows on the button. It says:

Warning: This predicate is a literal value and does not reference the input table.

 

No clue where to go now. 🙁

 

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.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (67,556)