Select oldest record do some action then go to the next oldest record and do some act

[vbnet05]

Hi all.

I'm working on a vb.net script in Visual Studio 2010 that needs to go through a collection of records and do some action and then move on to the next until the Quantity = 0.

Each record is a "picking order" each order has an order date, product and order amount.

Picking Order----OrderDate---Product---OrderAmount
123----04/01/2014---ABC---50
456---04/03/2014---ABC---20
789---04/05/2014---ABC---60

The Quantity field is the amount we have in stock for the product. In my example, product ABC the Quantity = 60

So, I need to be able to loop through my picking orders and begin slotting the quantity I have into each picking order beginning with the oldest one.

Using my example,

(1) I start with Quantity = 60, and apply 50 to Picking Order 123 thus closing that one. And now I have Quantity = 10
(2) Then I move on to the second oldest picking order, in this case 456, and apply the remaining Quantity of 10 to it, thus making picking order 456 have an order amount of 10.
(3) If there is only 1 picking order then apply the 50 it needs and place the remaining quantity of 10 in a field called "overage", or if there only 2 picking orders and we've closed them both out and still have some "overage" then apply that "overage" to the last blanket order (in this case the second one).

The picking orders are pulled from a query to the database:

Select * From PickingOrders Where ItemNumber = "the item number I'm analyzing"

Any idea how I can do this? I was thinking a combination of for each loops, do while loop or do until loop, but need some guidance.

Thank you in advance!

 

[jmcilhinney]

There's no completely neat way to write the loop. You would have to either use a For Each loop and then add an If statement to Exit For if the quantity dropped to zero or else use a Do or While loop and Exit if you ran out of records. Personally, I'd go for the For Next loop. I'd initialise an 'overage' variable to the quantity on hand and then start looping. In the loop, I'd get the lesser of the current overage value and the amount for the record using Math.Min and subtract that from both the record amount and the overage variable. If the record amount was zero then the record get closed, whatever exactly that means in your scenario. Finally, check whether the overage is down to zero and Exit if it is. Once that loop is done, your 'overage' variable contains either zero or the number of items left and each record that can will have been processed. It looks like your data is sorted by date anyway but it might be a good idea to add that explicitly too.

 

[Herman]

The best thing to do would be to purchase a proper inventory management software. If you are designing one, then I will say this is a poor approach. Instead you should treat each purchase order, inventory adjustment, and picking order as individual and distinct objects. Then you calculate your overage at any given date, given your on-hand quantity, the picking orders expected delivery dates, your purchase orders expected delivery dates, etc...

If you insist on "pen and paper" method (which is what you described, nevermind the fact that you are using a computer to do it...), as stated above just a For Next loop, and add an ORDER BY clause to your SQL query to ensure the data comes in sorted by date. Alternatively you could also sort your local data set.

 

[vbnet05]

Thank you for suggestions!

I was able to get it to work by using a do while loop, for each loop and if-else statement. A bit ugly, but it works.

 

[jmcilhinney]

Thank you for suggestions!

I was able to get it to work by using a do while loop, for each loop and if-else statement. A bit ugly, but it works.

Perhaps you should show us what you did and we might be able to point out where it could be improved.