cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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
Highlighted
Community Support
Community Support

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
Highlighted
Community Support
Community Support

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

Highlighted
Helper II
Helper II

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,134)