cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamH
Post Patron
Post Patron

Using a value from a gallery SUM calculation from a different screen and different SP list.

Hi All, 

 

I have an app that has a SP list database. I have another SP list setup with a one-to-many relationship.

 

The second SP List is being used to record hours, so on the bottom of the gallery, I have added a label and used:

Sum(Gallery2.AllItems,NQP_x0020_PEd_x0020_Hours)

2021-11-22 10_54_00-Window.png

I should also mention, the Gallery has a filter on it because it filters out hours depending on which staff record your in.

Filter('Staff Hours',Ref_x0020_ID = gblSelectedEmployee.ID)

 

I would like to take the value highlighted in the picture above and use it on another screen - How would I go around doing that? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
gabibalaban
Super User
Super User

@AdamH ,

First of all, you might want to change the approach of calculating the sum. On large amount of data in the gallery the Sum() result will be calculated based on the loaded items in the gallery (visible item, not all items result from filter query).

So the text label should be:

 

Sum(
    Filter('Staff Hours',Ref_x0020_ID = gblSelectedEmployee.ID),
    NPQ_x0020_PEd_x0020_Hours)
)

 

If you need the value of the label in other screens, you can:

1. On Navigate() function to specify this value as a context variable for the next screen like:

 

Navigate(NextScreen,None,{labelValue:Value(HoursLabel.Text)})

 

 2. Use OnHidden property of the screen and set a global variable with this value:

 

Set(labelValueGlobalVariable,Value(HoursLabel.Text))

 

The names of the variables and the control can be changed as you need.

Hope it helps !

View solution in original post

5 REPLIES 5
gabibalaban
Super User
Super User

@AdamH ,

First of all, you might want to change the approach of calculating the sum. On large amount of data in the gallery the Sum() result will be calculated based on the loaded items in the gallery (visible item, not all items result from filter query).

So the text label should be:

 

Sum(
    Filter('Staff Hours',Ref_x0020_ID = gblSelectedEmployee.ID),
    NPQ_x0020_PEd_x0020_Hours)
)

 

If you need the value of the label in other screens, you can:

1. On Navigate() function to specify this value as a context variable for the next screen like:

 

Navigate(NextScreen,None,{labelValue:Value(HoursLabel.Text)})

 

 2. Use OnHidden property of the screen and set a global variable with this value:

 

Set(labelValueGlobalVariable,Value(HoursLabel.Text))

 

The names of the variables and the control can be changed as you need.

Hope it helps !

View solution in original post

Hi @gabibalaban 

 

Thanks for the tip on the Sum()

 

I took the global variable option but what I failed to tell you was that the other screen I wanted to use the value in is a gallery, so all of the gallery items are listing the same value. How would I make this specific to an individuals staff members record? 

 

2021-11-22 20_38_30-Window.png

@AdamH ,

Add to your existing formula in items gallery the following:

AddColumns(yourExistingFormula,”NewColumn”,labelValueGlobalVariable)

and you can reference to it inside the gallery with ThisItem.NewColumn.

Hope it helps !

AdamH
Post Patron
Post Patron

Hi @gabibalaban 

I tried similar to that earlier before I posted and it still returned the same value. 

I also tried your suggestion and the same happened. 

 

2021-11-23 06_54_40-Window.png2021-11-23 06_57_26-Window.png

AdamH
Post Patron
Post Patron

@gabibalaban 

 

I've found a way to achieve what I need, probably not the best way but I used the code that you suggested and changed it a little:

Sum(
    Filter('Staff Hours',Ref_x0020_ID = gblSelectedEmployee.ID),
    NPQ_x0020_PEd_x0020_Hours)
)

to

Sum(Filter('Staff Hours','Ref ID' = ThisItem.ID), 'NQP Hours')

and placed a ThisItem.ID record in the the gallery. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,464)