Current stock quantity

[sal88]

Hi all

This has been a well discussed topic however I have struggled to find a direct answer to:
Should the 'currenty qty' field in my stock table be a calculation that refers to related line items, or should it be modified via script when related line items are added/deleted to Invoices?

I have looked at Todd Geist's excellent Inventory Transactions tutorial and am very excited about implementing it for this and other functions. For one it would mean I could remove a constant server script that hides/unhides stock items (from the stock list that appears when users are creating line items) depending on whether the quantity is zero or not. To be honest I'm a bit surprised that the portal that is used to display stock items that have a quantity above zero can't just refer to the calculated quantity (I'm guessing i opted for the server script method because the alternative was too slow or it was unable to see the quantity given that it was an unstored calculation). I believe that in general things would be sped up too (at the moment there are just 3000 stock records and 9000 line items).

However I am partly confused as looking at FM15's Invoicing starter solution it would appear that a calculation field is used.

Which path should I take?

TIA

Edited July 19, 2016 by sal88

[webko]

My rough rule of thumb - if a field is only used for _display_ purposes and never searched on, then a calc should be fine (so long as there aren't too many of them etc etc)

If I want to search on it, it will always be either an auto-enter text/number field, or updated only via script.

And your portal should be able to be filtered to not show items with a quantity < 1 on either of these options

[Wim Decorte]

'current qty' is definitely something that will be on reports, on screens and searched on by users so it should be set by the scripts that handle inventory ins and outs.

[sal88]

Perfect, cheers guys.

[sal88]

Instead of having the script modify the current qty field would it be ok for it to modify the sold qty field? This would make things simpler as sometimes we loan items instead of selling them, in which case the loan qty field would be populated.

Also, in Todd Geist's example, he uses an exit loop if $PortalCount = $ItemCount step. Can I just utilise the exit after last option within the Go to Portal Row step?

Edited July 20, 2016 by sal88

[Wim Decorte]

4 hours ago, sal88 said:

Instead of having the script modify the current qty field would it be ok for it to modify the sold qty field?

I don't think that makes sense in the real world.  What if you send something back to the manufacturer without it being sold?  or send it out for repair/RMA? Your inventory should go down but your sold will not go up.

If you make a sell then the script needs to update both 'sold qty' and 'current qty' (actually not at the point of the sell but the point of shipping for the 'current qty')

4 hours ago, sal88 said:

Also, in Todd Geist's example, he uses an exit loop if $PortalCount = $ItemCount step. Can I just utilise the exit after last option within the Go to Portal Row step?

Ideally not, the 'exit after last' typically throws an error 101 and in your transactional script you check for any and all errors and this may run interference.  It is hard to distinguish whether the 101 is genuine or just because you happen to be on the last record.  To distinguish between the two you'd need to check that portal count and the item count so you may  as well use that to begin with.

[sal88]

14 minutes ago, Wim Decorte said:

Ideally not, the 'exit after last' typically throws an error 101 and in your transactional script you check for any and all errors and this may run interference.  It is hard to distinguish whether the 101 is genuine or just because you happen to be on the last record.  To distinguish between the two you'd need to check that portal count and the item count so you may  as well use that to begin with.

Understood.

14 minutes ago, Wim Decorte said:

I don't think that makes sense in the real world.  What if you send something back to the manufacturer without it being sold?  or send it out for repair/RMA? Your inventory should go down but your sold will not go up.

If you make a sell then the script needs to update both 'sold qty' and 'current qty' (actually not at the point of the sell but the point of shipping for the 'current qty')

Couldn't my stock::current_qty be a calculation that minuses the stock::sold_qty (which is added to/subtracted from when adding/deleting LineItems) from stock::initial_qty? Returning stock to the manufacturer isn't an issue (or at least it hasn't been!).

Or would having such a calculation, even though simple, defeat the object?

Is the idea to modify both current_qty and sold_qty during the script?

Edited July 20, 2016 by sal88

[Wim Decorte]

5 minutes ago, sal88 said:

Couldn't my stock::current_qty be a calculation that minuses the stock::sold_qty (which is added to/subtracted from when adding/deleting LineItems) from stock::initial_qty? Returning stock to the manufacturer isn't an issue (or at least it hasn't been!).

Or would having such a calculation, even though simple, defeat the object?

 

It could be a slippery slope.  What you propose would make the calc a stored one and that would make it performant for searches and reporting.  But if you go down the calc route, at some point you'll add something that will make it unstored and a performance hog.

Better to stick to the scripted approach all the way.

You shouldn't have an 'initial qty' field.  The 'qty on hand' or 'qty available' is updated with each inventory IN movement so that you can see a transaction history of how you came to have the qty on hand that you do.

 

8 minutes ago, sal88 said:

Is the idea to modify both current_qty and sold_qty during the script?

 

Yes.  But not necessarily the same script.  Typically the 'qty sold' field gets updated when the sell happens.  More often it is called 'qty reserved'.  The 'qty on hand' only gets updated when things are picked from the warehouse and shipped so that this field does not get affected when the sell gets cancelled, or when you do a partial shipment.

So be very careful when you design this without those complexities in mind.  If the client throws you a curveball you don't want your architecture to be in a bad place.

[sal88]

Ah I see. We have quite a rudimentary system by comparison, Instead of an inventory IN process we simply have a single line in Stock per every order that comes in from our suppliers, with an initial qty specified - there is no parent table. If an Item is sold then it is implicitly moved out of stock (though we do now have delivered/waiting/out for delivery statuses, but these are simply so we know where items are and what is waiting to get shifted).

One reason for this may be because we don't invoice as soon as Items are passed to the customer, invoices to all customers go out just once a month. Other than that I can see we may have to move to the more standard approach that you mention.

For now I will have the script amend all qty fields, and I will look in to a revamp. Is there an article you can recommend so that I can learn about the fundamentals of inventory systems?

Thank you for you invaluable help Wim.

[sal88]

Having discussed this at length we have the following structure and basic layout of a single stock (we are calling it product) as per the attachment. What we really need to do is track precisely which individual item has gone where, so each of the 'Items' records has the manufacturer serial and is referenced somehow when the items are sold - I haven't seen this in any examples.

However what we would also like to retain is the ability to sell products with a quantity above 1, as a single line, even if it spans multiple batches. In order to track precisely which individual items were sold, e.g.

25x 2.5 SAS SATA HDD Tray Caddy

Would this require the use of multikeys?

Is this a sensible approach to stock management?

Thanks

Edited September 13, 2016 by sal88

[Wim Decorte]

22 hours ago, sal88 said:

However what we would also like to retain is the ability to sell products with a quantity above 1, as a single line, even if it spans multiple batches. In order to track precisely which individual items were sold, e.g.

That does not make a lot of sense: you are confusing what is on the invoice with what happens on the inventory side.  One has nothing to do with the other.

If you want to do what you describe then I would build a workflow where the user:

- selects the products for the order, individually so you know what is sold to whom

- is asked when the invoice is created: one invoice item for the total or one per product

 

The invoice line items should NOT be your inventory movement records.

[sal88]

On 9/14/2016 at 3:57 PM, Wim Decorte said:

One has nothing to do with the other.

 

Surely they are related though? Whatever the arrangement, a line item on an invoice must include the stock ID to which it refers? And not just for the benefit of the recipient of the invoice, but so that there is a means of seeing to whom a stock item was sold?

(I've a feeling the answer is geneally no?)

Also: inventory movement records - what are these? Are these just records that refer to an incoming delivery of stock from a supplier?

Edited September 18, 2016 by sal88

[sal88]

OK having looked in this further I think I can see things a bit clearer.

Does this set up conform to the standard?

We will do away with the individual items table. So we are just left with Products and Batches.

The products table has a 'qty at hand' field which is a calculation that sums related batch quantities.
It also has a 'sold qty' field, and a 'current qty' auto-enter field which subtracts 'sold qty' from 'qty at hand'.
When a supplier shipment comes in, this is added as a new batch record. Qty and purchase cost are recorded, as well as supplier details and order no.
When selling items the quantity that is being sold is added to the 'sold qty' field in the Products record. This sold item record is what the invoice pulls in.
An item cannot be sold if the 'current qty' of the Product record is zero (or below the required qty).
When selling items the purchase cost is pulled in - this is the average purchase cost of all remaining batches. This is worked out using the Product current qty field and the most recent batches' purchase costs (not entirely sure what this calculation would look like).
When sold Items are leaving our stock room the manufacturer serials numbers are individually scanned in and recorded in the sold Item record (the one that appears on the invoice).

 

 

Am I right in thinking that what is missing from here (as far as the standard arrangement goes) - is an 'inventory out' transaction record every time items are removed from the stock room? I can tell that long term this is the right thing to do but I can't get my head around why!?

 

Edited September 19, 2016 by sal88

[Wim Decorte]

16 hours ago, sal88 said:

Surely they are related though? Whatever the arrangement, a line item on an invoice must include the stock ID to which it refers? And not just for the benefit of the recipient of the invoice, but so that there is a means of seeing to whom a stock item was sold?

(I've a feeling the answer is geneally no?)

Also: inventory movement records - what are these? Are these just records that refer to an incoming delivery of stock from a supplier?

Inventory movement: all in and out movements of stock.  You can't rely on your invoice items to be the sole place where inventory out is recorded.  As said before: you may need to return things to your vendor, you may decide to throw away or donate items,...

Invoice items and stock movements can obviously be related; the point I was making was about you wanting to have one invoice item line for two itemized stock items, and you wanting to track those stock items.  Trying to use a multi-key on the invoice line item is a sign that the structure is not good.

 

 

11 hours ago, sal88 said:

An item cannot be sold if the 'current qty' of the Product record is zero (or below the required qty).

 

 

Why not?  A item can be sold while on back-order...

Same as with the price calc: those are business rules to decide, not strictly standard inventory mechanics.

I don't really follow the mental ERD that you have in mind, especially around the 'batch record'; I thought one of the big issues you wanted to solve was to track individual stock items (serial numbers) through the incoming and outgoing movements.  I don't see where you are doing that on the incoming side.  A 'batch' then is a 'shipment received', related to an order you placed with a vendor.  Sometimes it is useful to name things exactly for what they are so that you can recognize the tables by looking at the nouns in the narrative.

I also don't follow what the distinction is between your 'qty on hand' and 'current qty'.   'qty on hand' should the exact # of items you have in stock.  That number goes down only when the item is physically shipped, not when it is sold.   Some inventory systems have a secondary field to show how many of those items have been sold but not shipped.

 

11 hours ago, sal88 said:

When selling items the quantity that is being sold is added to the 'sold qty' field in the Products record. This sold item record is what the invoice pulls in.
 

What table is the "sold item record" in?

[sal88]

1 hour ago, Wim Decorte said:

Inventory movement: all in and out movements of stock.  You can't rely on your invoice items to be the sole place where inventory out is recorded.  As said before: you may need to return things to your vendor, you may decide to throw away or donate items,...

Sorry I keep forgetting that point! I guess up till now we have 'sold' such stock to our company record (which never gets invoiced) - but the method you state is much more rational so we will try and migrate to that.

1 hour ago, Wim Decorte said:

Why not?  A item can be sold while on back-order...

Same as with the price calc: those are business rules to decide, not strictly standard inventory mechanics.

Not having to check stock each time items are sold/converted from a quote would make my scripting life much easier! But how would the person who has sold the customer an Item know whether it is in stock, (either on back order or completely out of stock)? How would we know it needed reordering, and how would we know what date of delivery to promise the customer? What if it emerges that Product cannot be reordered from the Supplier? There are bound to be a few Products that are one-offs e.g. custom made servers, how would the end user know that these are not be sold anymore?

How would we know what the purchase cost is, given that there are multiple inventory lines per Product (each with slight variances in purchase cost)? 
At the moment we are thinking of having the sale cost manually inputted by our stock manager - short term price fluctuations doesn't bode well for one thing. But up till now we are able to work out exactly how much profit is made per invoice, because each Line Item pulls in the stock purchase cost. Is there still a way of achieving this? I was thinking it pulls in the average purchase cost of all remaining cost (but i can't picture how this would be ascertained).  Is it common practice/necessary to know at the Line Item level the true purchase cost (and therefore the profit).

1 hour ago, Wim Decorte said:

I don't really follow the mental ERD that you have in mind, especially around the 'batch record'; I thought one of the big issues you wanted to solve was to track individual stock items (serial numbers) through the incoming and outgoing movements.  I don't see where you are doing that on the incoming side.  A 'batch' then is a 'shipment received', related to an order you placed with a vendor.  Sometimes it is useful to name things exactly for what they are so that you can recognize the tables by looking at the nouns in the narrative.

Understood. I'll make sure we refer to it as inventory in/out.
That's right, I no longer see the point in noting down mfg. serials as they come through the door. All that needs checking is that the quantity of the expected product has arrived. Then when we take out items from stock to deliver to a customer the mfg. serials are recorded via barcode scanner in to a single field in the LineItem record. Therefore we wouldn't need the 'Stock Individual Items' table (the right most portal on the layout screenshot). Serial tracking is just for customer warranty purposes so they have proof of purchase date.

1 hour ago, Wim Decorte said:

I also don't follow what the distinction is between your 'qty on hand' and 'current qty'.   'qty on hand' should the exact # of items you have in stock.  That number goes down only when the item is physically shipped, not when it is sold.   Some inventory systems have a secondary field to show how many of those items have been sold but not shipped.

Ah OK i was unsuccessfully reading between the lines there. The reason I have a 'current qty' as well as qty at hand is so that it is known whether a stock item can be sold (an issue that I am unclear about as per the above queries), i was assuming 'qty at hand' just adds up all 'IN' transactions and then current qty subtracts from that the sold Line Items (which would be based on an  incorrect relationship).
So, 'qty on hand' effectively minuses all inventory INs from all inventory OUTs. Is it a calculation or is it updated via script on inventory in/out transactions?
I think we would need the 'sold but not shipped' field that you mention. How would this figure be ascertained? Wouldn't it require a delivery status field on the Line Items (and a relationship between the two :S  (?) )

So I take it that when an item is physically removed from the warehouse (for whatever reason, it might be sold, or donated), we create an 'inventory out' transaction? But obviously if it is sold then there is ALSO the Line Item record that has already been created.

1 hour ago, Wim Decorte said:

What table is the "sold item record" in?

The sold item record is in the LineItems table - Invoices are essentially a list of these records (doesn't really 'pull' it in). So to rephrase:
When creating a LineItem, the related Product's 'sold qty' field is modified - the quantity specified in the LineItem record is added to it.

 

Overall I can understand the separation of stock + selling  processes and I can see how it will make life easier for everyone, however in ascertaining accurate profits and knowing when to reorder and when to sell I am a bit stumped.

 

Thanks for your help Wim!

[Wim Decorte]

1 hour ago, sal88 said:

But how would the person who has sold the customer an Item know whether it is in stock, (either on back order or completely out of stock)? How would we know it needed reordering, and how would we know what date of delivery to promise the customer? What if it emerges that Product cannot be reordered from the Supplier? There are bound to be a few Products that are one-offs e.g. custom made servers, how would the end user know that these are not be sold anymore?

 

They would see that in those 'qty on hand' and similar fields.

 

1 hour ago, sal88 said:

How would we know what the purchase cost is, given that there are multiple inventory lines per Product (each with slight variances in purchase cost)? 

That's a business rule and for the valuation of the stock itself it is an accounting rule (things like LIFO / FIFO,... if those don't ring a bell then check with the accounting folks).

Computing the purchase cost based on the historic purchase cost is just a matter of putting the right formula together.

 

1 hour ago, sal88 said:

So, 'qty on hand' effectively minuses all inventory INs from all inventory OUTs. Is it a calculation or is it updated via script on inventory in/out transactions?

Definitely a transactional script.  If you make it a calculation it will be become a performance hog for searching and reporting.

 

1 hour ago, sal88 said:

So I take it that when an item is physically removed from the warehouse (for whatever reason, it might be sold, or donated), we create an 'inventory out' transaction? But obviously if it is sold then there is ALSO the Line Item record that has already been created.

Not sure I follow the "But obviously..."

Yes: you definitely need to create an inventory out record, but only when the item is shipped, not when it is sold.

When it is sold you need a script to update the 'qty sold but not shipped' so that users can see both how many are in stock and how many have been 'committed' already.

Then when the item is shopped, an inventory out record is created and the 'qty on hand' and 'qty sold but not shipped' is also updated.

 

[sal88]

1 hour ago, Wim Decorte said:

2 hours ago, sal88 said:

How would we know what the purchase cost is, given that there are multiple inventory lines per Product (each with slight variances in purchase cost)? 

That's a business rule and for the valuation of the stock itself it is an accounting rule (things like LIFO / FIFO,... if those don't ring a bell then check with the accounting folks).

Computing the purchase cost based on the historic purchase cost is just a matter of putting the right formula together.

According to FIFO, would the following logic be correct (and the calculation achievable):
The quantity at hand is 10
The latest IN transactions are:
20 @ £10 (January 2016)
5 @ £11 (June 2016)
3 @ £12 (September 2016)
Therefore the purchase cost I will be pulling in to the sold Line Item is: (3x£12 + 5x£11 + 2x£10)/10

1 hour ago, Wim Decorte said:

2 hours ago, sal88 said:

So I take it that when an item is physically removed from the warehouse (for whatever reason, it might be sold, or donated), we create an 'inventory out' transaction? But obviously if it is sold then there is ALSO the Line Item record that has already been created.

Not sure I follow the "But obviously..."

Yes: you definitely need to create an inventory out record, but only when the item is shipped, not when it is sold.

When it is sold you need a script to update the 'qty sold but not shipped' so that users can see both how many are in stock and how many have been 'committed' already.

Then when the item is shopped, an inventory out record is created and the 'qty on hand' and 'qty sold but not shipped' is also updated.

Excellent. What I meant is that the two stages are separate, for example:

1. 5 Items of the same Product are sold via a LineItem record
2. An OUT inventory transaction is made when it is shipped

One potential difference here is that many Items are not actually shipped, they are taken with the engineer onsite (a customer's premises) - at which point the status of the Line Item is changed to 'checked out'. If they return with the item then the Line Item is deleted, but if it remains on site then it is sold and the delivery status is changed to 'delivered'. Also, it isn't uncommon for an engineer to go on site with a number of items - not knowing whether they will use them all. therefore a number will be returned to stock.

My instinct is telling me that the best thing to do is stick to the inventory in/out process when things are returned, and for engineers to sell things only when they are actually sold. So they would take things to site (via an inventory OUT transaction) and then selling that which is remains on site, and then returning them to stock via an inventory IN transaction.

But then it's important that the purchase cost is included in inventory IN records. Does it make sense for inventory IN to be used for such returns? Would I include a 'returned from site' tickbox - whereby its population automatically pulls in the purchase cost via a slightly modified version of my above FIFO calculation.

But then if Items leave the stock room before they are sold, it is more complex to record the serials in the Line Item.

Sorry for yet another curveball! I think this is the last.

Edited September 19, 2016 by sal88

[sal88]

Unfortunately we've had to continue with a type of mishmash setup. I believe the main issue here is that the organisation in question is not actually a 'seller of stock', i.e. a sold item might not be shipped, a shipped item might not be sold. A shipped item will not find it's way back in to stock via a returns procedure as it might be returned to the warehouse before it has actually been sold. As I mentioned, we have engineers going to the customer with items that may or may not be sold to the customer. Furthermore we do need to stick to selling Items as per their individual purchase costs - there's no need for us to be setting sale prices per product which is what I initially thought.

Therefore all we have effectively done is just add the 'Products' parent table to the transactions (which are actually stock lines) so that the stock manager can more easily see what is going on.

Thanks for your help all the same Wim! I've learned a lot about how it should work!