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

Sum data in SharePoint

I am building a leave request app where each leave request is stored in Sharepoint in the following format:
Name | Date | Hours

 

One of the limitations I am trying to build is a "Blackout" process where we only allow a certain number of Leave Hours across the team on a specific date. If that threshold is met, the app would, in theory, prevent anyone else from requesting that date off.


The easiest way I could think of to do this would be to do a quick sum of hours for the date the person is trying to request off. Something like this: Sum(Filter(Team_PTO, Date = RequestDate), Hours)

 

If the sum is greater than or equal to, let's say 64 hours, prevent the user from continuing with their request for that date.

 

The issue, however, is that this gives me a delegation warning. My team is pretty big, so there are a lot of records in this database, so delegation is a must. Has anyone encountered this kind of issue before, and if so, how did you get around it?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sum data in SharePoint

Hi @DDCJOEE ,

Could you please share more details about the delegation issue within your formula?

 

Based on the issue that you mentioned, I think this Delegation issue is related to the Date type column in your Filter formula. Currently, within PowerApps, the Date type column from SP could not be delegable within Filter function. Please refer to the following article for more details:

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

 

If the amount of your SP List records is not more than 2000, you could ignore this warning issue. And the Sum function you provided would work well.

 

If the amount of your SP List records is more than 2000, as an alternative solution, you could add a Text type column (called "DateAsTextColumn") in your SP List to store the date value (in "mm/dd/yyyy" format) rather than use Date type column. Then within your app, you need to modify your formula as below:

Sum(
    Filter(Team_PTO, DateAsTextColumn = Text(RequestDate, "mm/dd/yyyy")), 
    Hours
)

 

In addition, you could also consider bulk-load your SP List records into your app through collections, then merge the multiple collections into a single one collection, then use the single one collection as data source in your app instead of the Original SP List.

Please check and see if the alternative solution I mentioned in following thread would help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Sort-gallery-with-multiple-fields/t...

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Delegation-Formula-Help/m-p/394039#...

 

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.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Sum data in SharePoint

Hi @DDCJOEE ,

Could you please share more details about the delegation issue within your formula?

 

Based on the issue that you mentioned, I think this Delegation issue is related to the Date type column in your Filter formula. Currently, within PowerApps, the Date type column from SP could not be delegable within Filter function. Please refer to the following article for more details:

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

 

If the amount of your SP List records is not more than 2000, you could ignore this warning issue. And the Sum function you provided would work well.

 

If the amount of your SP List records is more than 2000, as an alternative solution, you could add a Text type column (called "DateAsTextColumn") in your SP List to store the date value (in "mm/dd/yyyy" format) rather than use Date type column. Then within your app, you need to modify your formula as below:

Sum(
    Filter(Team_PTO, DateAsTextColumn = Text(RequestDate, "mm/dd/yyyy")), 
    Hours
)

 

In addition, you could also consider bulk-load your SP List records into your app through collections, then merge the multiple collections into a single one collection, then use the single one collection as data source in your app instead of the Original SP List.

Please check and see if the alternative solution I mentioned in following thread would help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Sort-gallery-with-multiple-fields/t...

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Delegation-Formula-Help/m-p/394039#...

 

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.

View solution in original post

DDCJOEE
Level: Powered On

Re: Sum data in SharePoint

Thank you so much! Funny enough I was actually already storing my Date field as a plain text field for that very reason. Early on I ran into delegation issues with filtering a date so I switched the column to single line of text. That said, your second suggestion about bulk-loading the date fields did just the trick! 

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,068)