There are several ways to set this up. One method is described in the following thread:
See if it helps give you some ideas.
I'm not sure what exactly your going for but your record tracking seems almost redundant. You say your records are set up by date. Seems to me you should set up your records by "Item Type". Instead of having to create a new record every day to track the difference in inventory set up a few fields to track outgoing shipments, and some for incoming shipments. Then you could set up a final field as a calculation. Have it add up all your incoming merchandise and then subtract your outgoing fields. The end result should be an updating field of your leftover inventory for each record. From there you could set up a report to look display all you merchanise and remaining inventory. Hope that wasn't to confusing. :)
Phil - I understand the concept of a log file, I'm just not sure how to apply it in my situation. I'll already be filling in one record of my sales, and don't want to have to go to another table to make another record just to track inventory. Can you show me some other methods?
Sater - I can see why it looks redundant. Let me explain a little further: The merchandise is sold at concerts, not in a store. Each record is a different venue/show date. If I was just dealing with a few sales a day, or only a few items, your method would make perfect sense. However, I'm dealing with lots of different pieces and lots of sales every night. I keep track of the inventory so I know when to reorder, but I also have to be able to send management a breakdown of each show date so they can see what we sold. I appreciate your response though!
I think you can do it just by simplifying the technique described in that thread. It doesn't sound like you need price list or invoice tables, just a running log of items on hand.
Just put in separate fields for "Items In" and "Items Out", Then a summary "total of" field summarizing cBal (Items In - Items Out) can give you the total on hand for all your items. You get this last result by labeling each item consistantly (Use a value list and a drop down or pop-up menu), then sort them first by Item then by date.
Ok, that can work. Is there a way to set it up so that when I make a new record, it automatically pulls the ending inventory number from the last record and puts it as the starting inventory number for the new record?
Define the summary field as a "running total" and "restart summary for each sorted group".
If you set up your layout as list view, you can get a layout that looks much like a check register with the summary field running down the right to show current quantity on hand.
For a new record, add a a OnObjectSave Script trigger to your item_type field. There are a couple ways you can probaly do this but The first way i thought of i will say even though it might be longwinded.
SetVariable $$END_INV, 0)
SetVARIABLE $$ITEM_TYPE, YourTable:Item_type)
If ($$ITEM_TYPE notequal YourTable:Item_Type)
Goto Record(Next, Exit after Last)
SET_FIELD(YourTable: START_INV, $$END_INV)
Something like that anyway, after you enter in the item type into your new record it will automatically run your script for capturing that data
With the running total approach, a script is unecessary. The totals for each item type will compute automatically as long as you keep the records properly sorted.
Could you walk me through how to set up that script? I haven't worked with them before, and staring at the screen hoping it'll make sense doesn't seem to be working. =/
No script is necessary--what I describe is all set up with field definitions and how you design your layout. You could set up a script to sort the records instead of selecting sort from the Records menu and you could add a button for creating a new record for the same item as your current record I suppose...
But you can also do it all without any scripts.