Automatically reduce "yet to be fulfilled" from Order quantity, based on quantities from multiple dispatches

[LLD]

Our customer places multiple orders for multiple SKUs, through the year. An SKU may appear in more than one order.

The quantities produced of each SKU, do not match the ordered quantities (whether from a single order, or cumulated from 2 or more orders). We may produce more or less than ordered quantities, depending on other production related issues.

As a result, the quantities dispatched do not necessarily match the total quantities due on each SKU (i.e. quantities due from previous orders + quantities in new order).

How do I automatically reduce the “yet to be fulfilled” quantity of each SKU in each order, as and when SKUs are dispatched? I have tried to illustrate this through the image below.

Please note that, production quantities are only shown to illustrate the differences between ordered quantities and produced quantities… ultimately, only the dispatched quantities are compared with the reducing balance of orders, to determine, what quantities are still due for fulfillment.

[WillowMobileSys · Accepted solution]

LLD:

How do I automatically reduce the “yet to be fulfilled” quantity of each SKU in each order, as and when SKUs are dispatched?

This is hard to answer for your specific use case without more details of how you are actually dealing with the different quantity inputs. But let me try to answer it generically.

---

I’ll assume you are tracking Dispatchments by each Order. I.e. when a dispatch occurs, that is being entered into the system.

For this I would make the Dispatch table a child table of the Order. To enter a disopath, you would go to the Order, add a Dispatch record of the amount being sent out and then upon Save of that record the Order should calculate the new dispatched quantity and the new Balance Due value.

The dispatched quantity is a SUM() expression on the Order table. The Balance Due calculation is then simply the Ordered Qty - Dispatched Quantity.

This is all very generic, I know. Let us know if this helps in the right direction and if you need more help give us some more details on how you actually have things setup in the app and we can go from that.

[WillowMobileSys · Accepted solution]

LLD:

How do I automatically reduce the “yet to be fulfilled” quantity of each SKU in each order, as and when SKUs are dispatched?

This is hard to answer for your specific use case without more details of how you are actually dealing with the different quantity inputs. But let me try to answer it generically.

---

I’ll assume you are tracking Dispatchments by each Order. I.e. when a dispatch occurs, that is being entered into the system.

For this I would make the Dispatch table a child table of the Order. To enter a disopath, you would go to the Order, add a Dispatch record of the amount being sent out and then upon Save of that record the Order should calculate the new dispatched quantity and the new Balance Due value.

The dispatched quantity is a SUM() expression on the Order table. The Balance Due calculation is then simply the Ordered Qty - Dispatched Quantity.

This is all very generic, I know. Let us know if this helps in the right direction and if you need more help give us some more details on how you actually have things setup in the app and we can go from that.

[LLD · Accepted solution]

Dear John,

Thank you for advice. Yes, I need to provide more detail, especially since the dispatched units are individually identifiable by unique serial number. Further, multiple serial numbers (units) are added to a Master Carton. The MC is dispatched.

I’ve reached as far as populating the dispatch date for an MC (today()), based on change in dispatch status (y/n), and this in turn copies the MC dispatch date to the serial numbers.

Now, each of these serial numbers needs to be linked to an order. I should not be able to add units beyond the order quantity.

I’m sorry if this isn’t coherent. Pls give me a day to write out a detailed, clear case.

Again, thanks.