Skip to main content

Notifications

Power Automate - Using Flows
Unanswered

Calculating outstanding/pending quantities dynamically

Posted on 27 Nov 2024 15:57:58 by

Hi there,

I am facing an issue where i want to update the outstanding quantities dynamically by using two methods, I am not sure on how to go about it, any suggestions would be helpful.
I have a form where a user has to select the purchase number from the list (data from sheet1) where the user can see:
- RequestNo, (like orderID)
- PO number (list where he can select the po number),
- Received date (automatically input when logged in),
- Items (from sheet1)
- Ordered quantity (from sheet1),
- Received quantity (manually type),
- Outstanding quantity (dynamically change)

So, when the form is submitted it will save in sheet2 like this (shown in image):

I want to achieve the result like above in excel sheet which is like that whenever there's new order to be added from form submission it should dynamically look in sheet2 if there's any existing entry of that same po number then it should calculate the outstanding quantities dynamically (previous outstanding quantity - current received quantity).

For now I am at that stage where i can display the latest outstanding quantities from sheet2 but i am facing an issue if the entry doesn't exist then it is showing on the form and excel like:

(Which is displaying wrong as it should calculate outstanding quantity normally with the subtraction of ordered qty - received qty.)

And, if i use calculate value under data tab in outstanding qty as:
value = row.orderedQty - row.receivedQty

then it will display correctly on form for first entry as:

But, let's say i received the same items of same po number after two weeks, then on form its displaying as:

As you can see that, the fourth row should display: 1, instead of displaying: 3

How can i achieve the result of first table so that whenever there's no entry it should look in excel sheet2 then automatically subtract normally for the first entry from form submission but if a user receives the delivery for the second time of the same po number then it should look in sheet2 and display the latest previous outstanding qty in sheet2 on the form and accordingly calculate the value of it when items were received for the second time.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,137

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,813

Leaderboard

Featured topics

Restore a deleted flow