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?
"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.
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:
|<, <=,<>, >, >=||Yes ||No||No||No||Yes|
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.
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:
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.
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.
Thanks for your reply. Ok so on App OnStart I have the following:
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. 🙁
Check out new user group experience and if you are a leader please create your group
Check out how to claim yours today!
Test your skills now with the Cloud Skill Challenge.
We are excited to announce that Demo Extravaganza for 2021 has started!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks