1 Reply Latest reply on Mar 5, 2012 1:15 PM by philmodjunk

    Using a summary in a calculation then displaying result in a different table



      Using a summary in a calculation then displaying result in a different table


      I have spent several hours trying to solve what should be a simple problem and am finally throwing in the towel and asking for help. 

      I have 3 tables: Jewelry; Bead Inventory; Line Items (used as a join table for the many to many relationships between beads and jewelry)

      I need to summarize the number of beads used for each beadID across several pieces of jewelry, then deduct those beads from the bead inventory. I can succeed in getting used beads into sub-summaries sorded by beadID and then captured them in getSummary for use in calculations within the line items table. What I can't figure out and am most frustrated with is transferring the info into the Bead Inventory table. This should be a simple thing...if you have an inventory you need to deduct items as they become unavailable. I'm using Filemaker Pro 11.

      I will greatly appreciate any help offered.


        • 1. Re: Using a summary in a calculation then displaying result in a different table

          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.