Need some simple answers Please

[Halfhidden]

I have FM10 Advanced and I extended the sample inventory that comes with this version and included several other fields. I'm quite happy with this except when it comes to inventory on hand.

You see I have inventory on hand (being the amount we purchased) The next three fields are for the three locations the stock will be sent to.

So field 1 shows the total stock purchased:

Field 2 shows the amount of stock in warehouse A and deducts from the total field, Field 3 shows the amount of stock in warehouse B and deducts from the total field and so on.

My problem is..... when I make a sale from one of the warehouses how do I get that field to deduct it from the total and the warehouse field whilst keeping a record of sale.

Example:

Total in stock 30 items split between 3 warehouses (10 each).Location A sells 5. How do I make a field that deducts 5 from the total in that warehouse and keeps a record for tracking.... so that I can see how many have been sold.

Has anyone an example I can see to help me with this project.

Thanks for reading this.

Edited July 30, 2009 by Guest

[fseipel]

There are many possible approaches to this, I'll suggest one. I'd recommend assigning each inventory item a unique stock keeping unit (SKU) identifier -- if the solution doesn't already do this. This could be as simple as serialization (e.g. start at, say, 0, increment for each new inventory item). Later these could be used for barcodes, if applicable to your scenario.

You might try two tables: One for inventory, one for transactions. That may simplify financial and inventory report generation. The key here is setting up a relationship on SKU between the inventory and transactions tables.

For inventory table, you might try recording both location and quantity at that warehouse in separate fields.

The advantage of this is, if you add a 4th warehouse later, no recoding is necessary.

Pushbuttons on the transactions layout could trigger scripts to 'post' the transaction for customer orders, finding the related record in inventory, and updating (decrementing) the quantities, then marking the transaction as 'posted'. 'Posting' may be a multi-step process, e.g. a phase for receipt of order, picking order, shipping order, etc within transactions, each generating paperwork, e-mails, etc i.e. a daily 'picklist' of items to be picked from warehouse for shipment to customers.

For instance, when you sell 5 units of item A from warehouse B, you would input the item name, warehouse name, quantity, price, etc into transactions. Clicking a button would run a script to update the quantity in inventory and mark the order 'posted' (a field could be used to indicate whether it had been 'posted'). Prior to accepting, the logic could check if you had sufficient quantity to fill order, and at which warehouse(s), if order had to be split across 2 warehouses, etc.

The transactions table would include transaction date, quantity ordered from supplier, price, PO number if applicable, expected delivery date, and which warehouse it was going to. This way, reports could later be generated on late/never received items for follow-up. You might want a second transaction table for resupply vs orders.

Later, upon receipt, another transaction would be generated by a script when item was logged in; at this point, actual 'on-hand' quantities would be incremented. This way you could also monitor supplier performance to generate reports of how long delivery took.

You may also want a minimum reorder quantity field to permit generating a report of items to reorder when warehouse quantity becomes low. Re-order quantity could also be calculated based upon past sales or rate of sales.