The best way to do it is to redesign your database. Any efforts to manage inventory from your current design will be extremely complex and prone to error.
I'd start over, get a new copy of the system working and then, if there is real data in my current copy, use import records and some scripts to move the data from the existing files into the updated system.
For start, Take a look at this simple demo file created by Comment: http://fmforums.com/forum/showpost.php?post/309136/
This shows you how to set up invoices ("sales" in your current system) with a separate related LineItems table that itemizes each item sold.
Then, you may want to use the "search the forum" box at the top of this screen to search for posts that discuss the "inventory ledger" approach for managing inventory from such an invoicing system. Purchase Order and Invoice tables can use the same LineItems table for logging the purchase and sale of inventory (but use a different field for the Qty sold/purchased) to set up the basic structure needed for this method. This is just the "bare bones" approach as there are refinements possible that enable you to log other inventory changes that can take place. (Items lost/damaged/stolen/discontinued etc.) And other refinements that use script triggers to generate inventory updates to fields in Products table in order to get more responsive layouts.
Thanks for the reply, will look up what you've recommended.
OK, from what I can understand... I should set up the invoice (purchase) as one table, then items bought from the invoice should be another table (line items) with the details/quantity of each item bought (key fields to link these two tables should be invoice no). A portal to display line items that creates new records for each line item entered in the invoice form.
Then in the sales, it would something similar. One table for the sales form with a portal to another table that contains the line items records for items sold.
Is this the gist of it? Then using the tables of the line items I will summarize the totals of each unique item. Then do a new field in a product info on another table, calculation to get total of inventory? Is this the gist of a very basic inventory stock tracking?
Pretty much. I suggest using the same lineItems table for purchases and sales. You'll then be able to create reports based on this table where you can see not only your current inventory levels for each item but how they change over time. If you see inventory levels for a given item reach zero or nearly zero on hand frequently, that's an time for which you may want to re-order sooner and a an item that has 100's in stock all the time is an item for which you might want to reduce the re-order point...
While a calculation field in Product Info can compute the total on hand and is fairly simple to set up, you may find using a script to compute the total on hand and update a number field in Product Info each time you modify a quantity IN or quantity OUT field in Line Items may make reviewing current inventory levels in Product Info something that pops up on the screen more quickly.
Thank you, very good points implement. Now to absorb it all to form the solution. Thanks again
I am figuring out how to import the data from the purchase invoice to the new table. Each invoice is a record and within this record there are 10 line items (say six fields per line, which means there are 6 x10 fields per invoice. If I am to import each line to the new table line items of 6 fields, the import table only allows you to select one field to import from.
I'm thinking a simple manual way is import item line 1 (old invoice), into the new table, creating new records. Then repeat and re-set up the import for line 2 (old invoice), and repeat till line 10 items. Doing the import 10 times.
For the sales invoice, that uses repetitions for each lineitem field I do not see that I can import each reptition separately. Write a script? Any suggestions? Write a script to propogate the repetitions to a new table/records then, import them to the new lineitems table? I'm just throwing some ideas out, haven't worked out if it's feasible though.
Uh, for the last paragraph above, I've figured out how to import repetitions into new records. The dialog box offering the option to import into separate records only opens after you actually select the import button
Yes, you may need to import multiple times in order to put data from different fields in the source table into the same field of different records in the target table.
I've managed to complete the new table in my inventory file. The table (sales and purchases) consists of records/line items with separate "in" and "out" fields. So each time and items are sold via the sales file, a new line/record is created for that item via a portal. Likewise in the inventory file, the invoice table now opens a new line/record using a portal to "sales and purchases table".
To ease the transfer of past data to this new table I've written some scripts to import them. I did import them manually initially but found that trying to connect all the data very tiresome when I wanted to repeat this several times to refine or add stuff to this new table wanted to do. So writing a script made this process easier and would make sure I didn't miss something when I actually do move the data over on the latest solution and go live.
Following that, I put a calculation field in the product info line to calculate the stock on hand. It was: Sum (in) - Sum (out) . Seemed straightforward and seemed to work. But I needed to make a way to update the Stock field to actuals stock in hand after stock take and also to make other adjustments like lost stock, in house use, expired stock and so on.
So I made another table in the inventory file - stock update table. I'm not sure I should do it this way, in each record created, a field is used to change the stock figures. So now the Calculation is this for the stock in hand field. Sum (in) - Sum (out) + Sum (stock update field), this last field number is either a positive or negative to adjust the stock quantities. Seemed to work. I'm wondering which is better, what I did, I should I just get this new table to make a new line/record in the "sales and purchase" table and another field for the update number.
I know you mentioned earlier about using a script to do this, but I went with a calculation to see if it would work first. Then in this same table I added a portal the "sales and purchase" table which would add new lines either to add (in) or deduct (out) the items for the other adjustments. But funnily enough now the calculations all go weird. What's even weirder, in the product info layout the stock in hand field displays an incorrect figure, whereas the "stock up date" table/layout which when a new record is made, it looks up the stock in hand field from the "product info" table and inputs the correct number. Very strange.
Could you point me in the right direction to do the scripting method you earlier mentioned? And any other better ways of doing what I'm trying to do. Thank you.