3 Replies Latest reply on Jun 11, 2010 8:36 AM by philmodjunk

    Automatic Field updates when making changes to another field

    jackib

      Title

      Automatic Field updates when making changes to another field

      Post

      I'm trying to create an inventory of products. I want to enter a qty for each item, and then find out the balance of that particular item after I purchase more of it, or subtract from it. The balance should reflect all purchases and sales of that particular item.I believe my fields will be something like this...

      Product

      Original Qty

      Qty Used (this field will be updated many times)

      Qty Purchased (this field will be updated many times)

      Running Balance (this field should reflect the Qty used and Qty Purchased for a Specific Product, no matter how many times the fields have been altered)

       

      I will also have a field of dates to reflect when the purchase was made or when the product was used.

       

      Does anyone know a simple way to do this?

        • 1. Re: Automatic Field updates when making changes to another field
          philmodjunk

          Search this forum, for "inventory log" and you'll find several threads on what you describe.

           

          You need one more field: cBalance as Qty Purchased - Qty Used.

           

          Then you can define your Running Balance as a summary field that computes a running balance of cBalance.

           

          Instead of continually updating the same record, create a new record for each inventory update. This gives you a ledger style record of how your inventory levels are changing over time. You can sort your records by Product to group them so you can see the Quantity on hand for each product as the running balance of the most recent entry.

          • 2. Re: Automatic Field updates when making changes to another field
            jackib

            Thanks Phil.

            I am almost there. The ledger format work well for me but I need the cbalance and sbalance to reflcect only one item id. Right now they are adding and subtracting all items together. I am using filemaker 6.

             

            You said the sBalance should be (Summary, running total of cBalance, restart totals when grouped by ItemID). I don't know how to get it to restart totals when grouped by itemid.

            Thanks again.

            Jbecher

            • 3. Re: Automatic Field updates when making changes to another field
              philmodjunk

              Hmmm, I'm not sure when they added the "restart totals" feature. It may not be available in filemaker 6.

               

              You can still get your sub-totals by part, but it takes a bit more work. Create a summary report and place your summary fields in a sub-summary part specifying a "sorted by" field that will group your records by item. That sub-summary part will then display your sub-totals when you print or preview your report.

               

              Here's a link to a simple tutorial on setting up summary reports that you may find useful if you've never worked with summary reports before:

              Creating Filemaker Pro summary reports--Tutorial