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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.