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.
Hi @Anonymous,
Unfortunately it is very hard for us to assist you as the question you are asking is rather difficult to follow. From what I gather you want to update the records in an excel spreadsheet based on form submissions which can occur at separate times but are associated with the same records.
Most of the complexity and logic to achieve this will need to be done in Microsoft Power Automate. For example you will need to identify which rows need to be updated (e.g. using a reference number like the PO and Item description) within your workflow and update them accordingly based on the response to the form. Given the nature of the problem it might be best to post your question in the Microsoft Power Automate Community Form.
Hi @Anonymous,
Unfortunately it is very hard for us to assist you as the question you are asking is rather difficult to follow. From what I gather you want to update the records in an excel spreadsheet based on form submissions which can occur at separate times but are associated with the same records.
Most of the complexity and logic to achieve this will need to be done in Microsoft Power Automate. For example you will need to identify which rows need to be updated (e.g. using a reference number like the PO and Item description) within your workflow and update them accordingly based on the response to the form. Given the nature of the problem it might be best to post your question in the Microsoft Power Automate Community Form.
