How will you track inventory? A nightly inventory check or on some other regular interval? (Can't usually do this with sales receipts and shipments received in a bar as this won't count any free drinks poured by your bartender(s).)
You can certainly set up one table to list all products sold, one record for each product and then link it to a table where you have one record for each product for each time you take an inventory count of it. Sorting records on this second table to group them by product and/or by date can then show you current and past inventory levels.
I track my inventory using another program which is connected to the ecrs. This gives me a full report of each inventory item and amount sold. I will insert these myself into filemaker. Do a stock check twice monthly and use a wastage system whereby staff write down drinks wasted or given out free of charge on a nightly basis. This is all stored in a file and with this I will personally add the wastage/free drinks personally to the stock output.
I need a system in which I can simply insert the current stock level (twice monthly) and expected stock from deliveries/invoices and sold/wasted stock. From this I will have a difference of amount expected/amount actual in which I can track discrepancies.
I also would like a report of all stock containing the present stock level and the amount of stock I have had in the past as a stock history report.
I will need to be able to go straight into my inventory to each item e.g VK Orange add a date i.e. 7/12/11 current stock = 452. On entering a full stocklist report it should then show me all items incl: VK Orange 275ml 4.5%abv 1/12/11 - 609 7/12/11 452
then another report that will show me all i.e. VK Orange 275ml 4.5%abv 7/12/11 expected stock 454 actual stock 452 difference -2, Budweiser 275ml 4.5%abv 7/12/11 expected stock 320 actual stock 330 difference +10
Does this seem possible or am I asking for a miracle?
What is an ecrs?
These are all standard inventory control tasks just about any DB system can be setup to manage for you.
Best approach here is a table set up like a check register or bookkeeping ledger. Stock received are deposits. Stock sold/wasted are withdrawels.
Define the following fields in this table:
cBal (calculation field: QtyIn - QtyOut )
sBalance ( Summary field, running total of cBal, restart each total when grouped by ProductID)
Description (text, log "Sale", "received", "wastage", "Inventory correction" or whatever you need to better document each entry).
This table would be linked by ProductID to the products table. If you use FileMaker for invoicing, this table can do double duty as your LineItems table for your invoices, QtyOut would be used for Qty sold on that layout.
I'd use a separate table for recording each inventory count also linked to this table by ProductID. You can than see discrepancy figures on a layout based on this table via a relationship to the above 'ledger' table. A script can then be used to reconcile the counts in the two tables by creating an Inventory adjustment record for each product where there is a discrepancy.
Would I need to have a seperate record for each date and amount in stock? or would I be able to use a portal so that I can view this information when I open up the product record?
Portals list separate records in a related table so you have separate records that way as well.
One record for each Product in the Products Table, you can record a restocking level here.
One record in the ledger table each time inventory for one product changes. Portals from a products layout, invoices layout, shipments received layout can all be used if you want or you can do all of these updates for all products from a single layout. Both methods record data in separate records in the Inventory Ledger table.
BTW, I've recommended this method numerous times to other posters. You can search the forum for "inventory ledger" to pull up additional discussions of this method.