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:
Solved! Go to Solution.
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.
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
Hi @RandyHayes ,
All points are correct.
In SPO list this is the record after patch:
ID# 1 = Original record
ID# 2 = Patch Record
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?
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.
What the french toast.
It works!!!! 😁
1+(-1) = 0
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.
Check out the News & Announcements to learn more.
Check out the new Power Platform Community Connections gallery!
Congratulations, the new Super User Season 2 for 2021 has started!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.