cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hidayat
Helper I
Helper I

Sum in Gallery once Patch is done

Hi all,

Is there a way to show an updated sum of values once patch is done?

Initially, my Gallery1 has an item A quantity is 1.

When a customer placed an order, the data is patch to the SPO list and creates a new record with Item A quantity -1.

The SUM in SPO of item A is 0. 

But in gallery1, the quantity is still 1.

 

Below is the label, text input:

"Available Quantity: " & Text(Sum(ThisItem.'AvailableQuantity'))
 
I'm not sure if this needs a statement to refresh gallery or screen.
Thanks for the help in advance!
 
 
Regards
Hidayat
14 REPLIES 14

@Hidayat 

To recap the assumptions:

- You have a Gallery with an Items property that includes your SharePoint list as a data source (not a collection)

- You have a column in your SharePoint list that is a numeric column called AvailableQuantity

- You are executing a Patch or UpdateIf directly to the Item in your SharePoint list and decrementing or otherwise changing the Value in AvailableQuantity - you see this reflected immediately in the SharePoint list

- Your Gallery label, however, does not change

 

Please review the above and let me know where there might be differences as the above should work perfectly.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

ok so you want to view the item and the total in the gallery.

 

you will have to add a column and .. addcolumns(GroupBy(yourSharePointList,"ColumnToGroup","Balance"),"AggregateIt",Sum(Balance,ValueColumn))

 

add a label in the gallery and set the text to thisitem.AggregateIt

 

hope it helps

View solution in original post

Hi @RandyHayes ,

 

All points are correct.

 

In SPO list this is the record after patch:

ID# 1 = Original record

ID# 2 = Patch Record

ID#Item NameAvailableQuantity
1Item A1
2Item A-1
 Grand Total:0

 

In PowerApps, the datacard, text label shows AvailableQuantity = 1 instead of 0.

"Available Quantity: " & ThisItem.'AvailableQuantity'

 

It seems text label result shows  1st value in record instead of SUM the column Available Quantity. I changed  SPO record ID#1 to random number 54321 and it indeed reflects the random number 54321 upon screen refresh.

 

Is there a way to SUM available quantity column in Gallery?

Regards

Hidayat

 

 

 

@Hidayat 

I see, so my impression was that you had ONE record for Item A in which you were keeping the current quantity available.  When you say AvailableQuantity, a negative did not make sense.   Apologies that now re-reading your original post with this knowledge, what you were saying there makes more sense.

So basically, you are instead keeping a journal of Item quantity changes.  I would caution on this as you might start to run into invalid sums if your items list grows too large.  If PowerApps runs into its record limits, you might drop off important quantities in your sum.

A suggestion is that you keep one record per item with the current AvailableQuantity and then if individual transaction records are needed, separate that out into another list.

 

But, as you have it now, your Gallery needs to do this through a GroupBy operation.

So your formula would need to be:

AddColumns(
    GroupBy(youSPOList, "Item Name", "records"),
    "availableQty", Sum(records, AvailableQuantity)
)

 

Then change your label Text property to : ThisItem.availableQty

NOTE: if you have any other labels in your Gallery that are showing columns values other then "Item Name", those will need to be adjusted with this modification.

 

You should now see the proper amount in the AvailableQuantity all the time.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

What the french toast.

It works!!!! 😁

1+(-1) = 0


Thank you for the guidance @RandyHayes @rubin_boer !!

 

The shopping app is about for 20-50 people, and will run live for 1 week.

@RandyHayes , not sure whether it's the best practice, 1st time building this out. The current design is that when an order is submitted through a collection, there will be 2 ForAll & Patch:

1st patch to update SPO Order list to contain only orders; 

2nd patch  to update SPO Stocks list i.e if customer ordered quantity is 1, it will record the ordered quantity as -1*1 as the new entry.

You are right, there is always a possibility the SPO Stock list grows too large.

The workaround I can think of now is to create a flow that sends an email when SPO Stock list record has 4000 entries.

 

Regards

Hidayat

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Top Solution Authors
Top Kudoed Authors
Users online (2,357)