cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kreed1
Frequent Visitor

Calculate cumulative sum in power app based off of UID presented in form

Hello,

I'm struggling to figure out how to have a text field in my app calculate a cumulative sum for total hours based off employee ID, after all the research I've done I'm not even positive if this is possible. 

I have a sharepoint list ("Time Off Request") that captures leave requests. The same employee can have multiple requests.

 

I have a column for the amount of hours that is requested for that particular request as well as a lookup column that pulls in the employees UID from a different sharepoint list (Employee List), this all works fine. 

 

The idea is to have my app find each sharepoint item that contains the UID then sum their total hours. Similar to a SumIf function like "If this UID, then Sum hours". Below are screenshots of my list and app. I may be doing this all wrong, still a rookie here.

 

kreed1_0-1627492046476.png

 

kreed1_2-1627492319781.png

 

below is the formula I have currently, its in the OnChange property, I did have it in the Default property but was getting same error which is kreed1_4-1627492463924.png

 

 

kreed1_3-1627492445781.png

 

Any help would be greatly appreciated!

 

1 ACCEPTED SOLUTION

Accepted Solutions
GarethPrisk
Resident Rockstar
Resident Rockstar

For SharePoint, you'll have limited options for this type of operation that are delegable. You'll see that delegation warning in most scenarios to accomplish this summing of a column.

 

In the final screenshot, is that a Label control? You'd use that same Formula, or something similar, but put it in the Label property. It will automatically resolve if/when DataCardValue59 changes.

 

You should also switch it to use the Selected property, and not SelectedItems.

Coalesce(
    Text(
        Sum(
            Filter(
                'Time Off Request',
                EmpID.Id = DataCardValue3.Selected.Id
            ),
            'Total Hours'
        )
    ),
    "Calculating"
)

That will also fallback (Coalesce) to a text label to let the user know it's not yet calculated.

View solution in original post

2 REPLIES 2
GarethPrisk
Resident Rockstar
Resident Rockstar

For SharePoint, you'll have limited options for this type of operation that are delegable. You'll see that delegation warning in most scenarios to accomplish this summing of a column.

 

In the final screenshot, is that a Label control? You'd use that same Formula, or something similar, but put it in the Label property. It will automatically resolve if/when DataCardValue59 changes.

 

You should also switch it to use the Selected property, and not SelectedItems.

Coalesce(
    Text(
        Sum(
            Filter(
                'Time Off Request',
                EmpID.Id = DataCardValue3.Selected.Id
            ),
            'Total Hours'
        )
    ),
    "Calculating"
)

That will also fallback (Coalesce) to a text label to let the user know it's not yet calculated.

View solution in original post

Thank you so much! That did the trick. I did have to tweek it just a little and ended up getting this to work. I added an additional filter to only calculate the "approved" hours. 

Coalesce(
Text(
Sum(
Filter(
'Time Off Request',
EmpID = DataCardValue38.Text, Approval.Value = "Approved"
),
'Total Hours'
)
),
"Calculating"
)

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,655)