Tracking Back Orders

[LaRetta]

I have a Products table with one field - StockOnHand (standard number). It is updated from Inventory after each day's shipments. I use a static field so displays are quicker - an aggregate was too slow. I have a TO of Invoices which identifies which invoices should ship that day (Invoices Ready To Ship).

Problem: If we have insufficient stock to ship that day's Invoices (on any one product), we need to decide which orders to ship and which to flag as back order.

I need to sum each product scheduled to ship that day then compare that sum against Products::StockOnHand and present Sales Manager with a list (portal or columnar list) of those invoices which contains a product which will run short. He will decide priority. As he clicks an Invoice row (indicating it should take priority and ship, ie, reserve the stock for that Invoice), a calculation should decrease remaining stock until he has shipped all Invoices possible from the remaining stock until it hits zero (although the actual StockOnHand won't deduct the products until after the shipping process). The remaining orders should then be flagged as Back Ordered and not shipped (and they will appear in another BackOrder portal). Back order portal is simple number BackOrder = 1 in Invoices.

This needs to happen every day. If stock comes in and we are ready to ship that day, Invoices scheduled to ship PLUS any listed in backorder should then be summ'd and the process repeated. I hope my needs are clear ...

I see many ways of approaching this - horsing with script etc. And many ways I've considered would be very slow (summing LineItems etc) and I'm unsure on the best way to approach it. Perspective of Products, summing Invoices Ready To Ship plus Back Orders? Then script to write those invoices to multiline global for display to Sales Manager? Or from Invoices? Or from LineItems? Or?

This needs to go into effect on Monday.

Ideas would be most appreciated.

LaRetta

[LaRetta]

Okay, maybe I can simplify my request ...

I have a portal of invoices (ShippingToday TO). I need a summary of quantity by product. I need to compare that summary to a Products table StockOnHand field and, if there is insufficient stock, write the invoice numbers (for those invoices with that product listed) to multiline global for display in another (back order) portal.

I can get the summary by product by generating a report. And I can even export the summ'd quantity ... but I don't want to. I want to compare those sums with another table (products) instead. I think I'm missing something pretty simple here but I'm just too tired to see it. Any ideas please?

[Rob 7 Collins]

Eke! That is grizzly-complex.

Today I'm feeling particularly table-heavy with my suggestions, so maybe that's where this comes from... Anyways:

(this is just some pie-in-the-sky ideas that might help? hope so)

create a onorder table with fields for product, orders, date added. Relate product to the products table. When an invoice is added, add to that script to search the onorder table, add 1 to the orders table for that product if it is in the table, else add a new record with the product and an orders quantity of 1.

From the buyer's inventory screen you're creating, update this table as well as others you need to, for each change she can trigger ... when an onorder record's quantity of orders drops to zero, delete the record (or *gulp* flag it to delete later, if you must show her products she's already dealt with, later on -- transaction processing is not pretty).

... you don't need dates, or even a seperate table, if you can always work with all waiting invoices rather than just those from today (just add a shippingtoday field in the products table). you'll need an additional invoiceID field in the onorder table if you need to mark the invoice table(s) when the buyer works from your new screen or if your report must reflect invoices. --by using relations between onorders and the other tables, drawing your reports and layouts from this table, and using related fields from those other tables, you can design your onorders table to reflect only the information it needs.

[Brian C]

I only read your second post before answering this since my time is limted at the moment, hopefully this is what you are looking for:

1) Use a self join relationship to get a sum total for an item that is yet to be shipped. Use something like an shipping status in order to differentiate between shipped and unshipped items so that you only get a total of items that are not yet shipped. This can be accomplished using a calculation field that will always contain the desired status to be tracked by the relationship, combined with the id number of the stock item.

i.e. "ToBeShipped" & StockID

You can use a literal text string, as in the above example or you could use a global field that is populated by a script if you want to make this field a bit more multipurpose for other filtering purposes.

This will then link to a matching calculation field that contains:

ShippingStatus & StockID

2) Use a relationship to the stock table to retreive the total on hand

3) Subtract the sum toal for an item that is yet to be shipped from the total on hand to determine how much stock you will have remaining for processing further orders.

4) Once an item has been shipped, its status shoud be changed so that it will no longer be counted in the group of items that have not yet been shipped.

5) Your system also needs to update the stock on hand once an item's status has changed and has actually been shipped.

6) When you create a new invoice, as each invoice item is added, you can use this process to see if you have enough stock on hand and do some simple math to see if you can make a partial shipment or if you need to back order the entire order for a given stock item.

Since this is being done as each item is being added you can easily track and update back order totals in any manner you choose.

[LaRetta]

Rob thanks. You've sparked several ideas for me that I'll try tonight!

Brian said ... When you create a new invoice, as each invoice item is added, you can use this process to see if you have enough stock on hand and do some simple math to see if you can make a partial shipment or if you need to back order the entire order for a given stock item. Since this is being done as each item is being added you can easily track and update back order totals in any manner you choose.

We think alike, Brian. What you've listed is exactly what I have implemented. However it breaks because:

1) We enter orders which may not ship for weeks. At the time the order is entered, we may have sufficient stock but when ready to ship stock is gone. Originally, system would refuse to let them order something (for next day shipment) when there was insufficient stock. I had to disconnect that and just display a warning calc because Owner wants no orders refused. They want the ability to negotiate and change ship dates or split an order into two shipments only after talking to the buyers. Orders come in from fax, email, online and phone and all orders are entered so we can project future stock needs (hopefully).

2) We are growing 70% per month. Because of FM, the ordering and shipping process can handle it. But supplies and manufacturing can't keep up. We've rarely had back orders before. Now it's a constant (positive) struggle to keep up. Owner considers what we need, doubles his stock & materials purchases and we still overrun. I told him I hoped we got 'ahead' of it ... he grinned and said he hopes he ever does.

3) Manager now needs to negotiate with Chains and stores and offer discounts if they'll hold their order for 3 days. We fill the warehouse to capacity and empty it every day. Sometimes a Chain will need their shipment moved up and we don't even find out until the night before. And someone else must wait for their order.

4) My ReadyToShip portal relates on several fields (as you've mentioned) most are Boolean and of course ScheduledShipDate <=. One day's shipping contains 3,000 + lineitems and Sum() was slow and my ReadyToShip portal display crawled; in fact. I even added a multiline so it only related to stock items (Stock, Free Product, Marked Sample), omitting lineitem payments etc. to try to reduce the stress.

I am unsure how to speed up the process, provide a quick display of invoices and allow an override. I have an override flag. I need to run this process at the time of shipment. It addresses Invoices (Orders) but needs to use the LineItems. I need to stop orders from starting through the process because, once started through, it's costly to back out (shipper & materials time). It also opens up the possibility of error whenever we 'unship', ie, someone must physically pull it from the line.

I started to put together a demo of what I have. But I realized that, without my 180,000 Invoice/500,000 LineItems data, it wouldn't be a good indicator of how it would run. All I know is that in my solution it's a dawg. In fact, just displaying a summary report of the ReadyToShip Lineitems takes 25 seconds to display. Bummer. Maybe if I do a demo, you can spot some flaws in my logic which might speed it up.

That's why I was considering alternate methods. Would a demo help here or can you make additional suggestions with what I've given you? Thank you both so much for helping me.

LaRetta

[Brian C]

I keep coming up with ideas but then nix them as I re-read your post... I think I need to understand more about your readytoship processes. An example of some sort to look at would be helpful I think so that I dont wind up re-inventing the wheel for things you have already covered.

I'm going to make my tired brain give this a shot in any case (end of the day

As each item is ordered, you may want to have a new table that tracks the item similar to the invoice items table, the qty ordered, the date, priority, and a unique id number, and an id number that links it back to the invoice of origin. As new orders are created, records are added to this table and are re-prioritized as needed. Records that are forced into a state that would affect their ability to be shipped on time would create a record in an exceptions table so that a warning could be issued for specific invoices containing items that are not longer available for their original shipping date..... The client could then be contacted and an invoice could then be split.

How you would go about handling the prioritization of the ordered line items would really depend a lot on how you do business.

A half baked thought at best considering my concentration level at the moment. I will think about this a bit more tomorrow.

A few more ideas:

I'm not sure how helpful this suggestion to your situation but it sounds like doubling the stock & materials purchases is doing the job for you. To forecast inventory needs, a new table could be populated on a monthly/biweekely/weekly basis to track each product and how many were ordered over the specified time period. Using these results you could then project how much more and how often you would need to order specific items.

I'm not sure how you handle invoice splitting currently, but you may want to consider inactivating a current invoice and it's items; and by using a few fields you can then send a line item to a new invoice, or even split a single the line onto multiple new invoices.

So for example:

Item: Qty: Split1 Qty Split2 Qty Split3 Qty SplitTTL

Mercedes 20 X 13 X 7 20

In the above example the mercedes order will split to 2 different invoices only after a validation has occurred to insure the split qty total is equal to the original qty total for the line item.

This will allow you to quickly go through an invoice line by line and split them between 1 to 3 different new invoices and then a script could control the creation of the new invoices with the new shipping dates for each.

Use a new key field on the split invoices to link them back to the parent invoice ID for later reference to the original.

[LaRetta]

Hi Brian,

Yep, Owner has biweekly columnar portals per product back through several years to project stock. It's just that we keep exceeding it every single week. I believe he'll get the hang of it soon and x4 his supply requests. Our suppliers are feeling the crunch and part of our holdup is their surprise in our current stock/materials requests. In truth, my belief is that a warehouse should always be empty, ie, stock sitting costs money. We of course don't want orders held but then again, we certainly don't have to deal with expiration date issues this way.

Invoice splitting works well now. Because of Purchase Order stipulations this is a rarity and has only been used a few dozen times but it hasn't failed yet so I'm okay with how that part works. We sometimes drop ship and kick a lineitem onto it's own invoice also. But the same back order problem will happen - which invoice (amongst several) will take priority to ship. I've discussed identifying priority by client but this won't work either. Only a human can make these decisions on an individual order/product/client basis.

... you may want to have a new table that tracks the item similar to the invoice items table...

Yep, this is similar to Rob's ideas and one I'm working on now. I don't like the 'horsing' approach but may need to just to speed things up. I've been playing with event trigger (SecureFM) which populates a static Product/Sum field in a 'Staging' table. If I haven't figured out something nifty tonight, I'll post a demo tomorrow.

My LineItems (the beast) continue to be difficult to handle and I need alternate ways to break it down in size. One would think the more filters I apply to the TO, the less related LineItem records and the quicker it would be ... but that isn't always proving the case. It appears that the filters themselves can slow the whole thing down also. This is all speculation and observation however. I hope to find the right balance to produce instant displays and summaries based upon ANY group of filters or ranges.

I demand fast. If I don't succeed to my satisfaction, I'll go back to event triggers. I will be upgrading all our systems to 7.0v3 this weekend; maybe that will improve the speed also. It's good to be reassured that I'm not really off track here. At least I'm tracking both of your suggestions (and thinking) just fine. As we continue to grow, LineItems will be even more of a challenge. I just need to become a Beast Master.

LaRetta