Use the Search tool at the top of this screen to search for "Inventory Ledger". You'll find a number of threads discussing such a transactional inventory management system. It is intended to work with groups of items identified by serial number where the serial number is specific to the type of item rather than the individual item--which would appear to be what you need here.
After checking those out, feel free to return to this thread with any additional question you might have.
-one of the issues I am encountering is how upon saving a record in one database do you have another record automatically created in a related database.
The method we are discussing does not actually require that, but this can be scripted via the OnRecordCommit script trigger to perform a script that creates the related record. Typically, the primary key of the current record is copied into a variable so that the script can change to a layout based on the related table, create the record, copy the value from the variable into the new record's foreign key field and then returns to the original layout.
Hey Phil Thanks for the response. I read the other posts earlier and the thing that I was having a hard time grasping is how to build in the multi location component. Do you have any thoughts. Would a flat database or a relational database work best for this?
You are almost certain to need related tables to complete your solution, but the basic "ledger" is a single table. I would add one more field to the ledger file to identify the location. You can then log an inventory change by ID, qty, date and location. You can then manipulate your found set of ledger records with finds and sorts to get inventory totals specific to a location, specfic to an item type, or both.
In such a system, a related table might have one record for every type of item (Serial numbers for them are generated there). Another related table might list locations (and have serial numbers for them as well).
Other tables might be needed to facilitate specific types of events that modify inventory such as production reports, purchase orders, sales invoices, Shipments recieved or shipped, etc. In many of those cases, you use a portal to the ledger table (It's the line items table in a typical invoicing system) to log the item type by item type inventory changes.