I have an inventory database to keep track of parts used to assemble systems. I have the following files. Parts file with all of the machined parts along with descriptions and initial inventory and current inventory.

Product File with the various products built using parts and the qty of each part required to build it.

Update file that allows me to add inventory to any part. Current inventory in part file equals initial inventory plus sum of update quantities.

Invoice File where invoice number and Products Sold are entered. I am using a Portal on this file to display the actual parts being used. This displays the data from the product file but when I try to calculate the total parts being used across several sales records I get errors.

Can anyone point me in the right direction on this? I am trying to simplify the description but I think I am missing a concept on many to many relationships or on Join files. Neither is covered very well in the manuals I have.

Thanks,

Doyle

Looks like I got the problem solved based on some inputs to another user. I have setup a transaction database and am using a script to take the critical data from one portal and place it into another portal which is the Transaction database. I created a couple Global fields and placed them in each of the Portals to allow easy switching between Portals for the script (based on another tip). I can now easily perform the necessary math operations to always have the current inventory for each part on screen as well as the necessary monthly reports to determine usage and min inventory violations. Although I didn't get a direct answer I appreciate the number of answers posted as it helped me change the way I was thinking about the task.

Thanks..Doyle