There are several options for managing inventory in a database. One method is to set up your LineItems table as an "inventory ledger". This method is documented here in the forum a number of different times so you can search on that phrase if you are interested. Essentially, you add a few more fields to line items and then treat items received (new shipment, returned merchandise, product manufactured...) like a bank deposit and items removed (sold, lost, discontinued, etc...) like a withdrawel and then summary fields compute a running total to show amounts on hand. This can be a usful tool for managing re-order levels as it allows you to review how inventory levels change over time instead of just reporting the current number of items on hand.
Here's a simpler method that does not require the ledger, but is sometimes used in conjunction with it:
Set up a script like this:
Perform find to pull up found set of line items--this can be all items from a specific Jewelry record or for a group of jewlery records.
Use this script step to pull up the Bead Inventory records that match to those line items:
Go To Related Record [Show only related records; match found set/ From table: Bead Inventory; Using layout: "Bead Inventory" (Bead Inventory)]
You can then loop through the Bead Inventory records and use
Sum ( LineItems::Qty ) to update the inventory levels.
If you use this method, be sure to then"mark" your line item records by setting a value in a field and then include criteria that will NOT match to these records in that initial find so that you cannot update inventory for the same line items twice.