2 Replies Latest reply on Jun 4, 2017 7:26 AM by eabhishek

    inventory balance by lot # and then location


      I have created a database with tables and relationship to link an item with various lot # and location as attached. I want to show running balance for each location for a lot # of an item. Attached is a List View sorted by Item_ID, Lot_ID, and then Location_ID but the balance field doesn't restart summary for each sort group when sorted by Lot_ID or Location_ID. So what is wrong in my setup?

        • 1. Re: inventory balance by lot # and then location

          You show what looks like a standard summary report with sub summary layout parts but do not identify the table occurrence on which it is based. Would it be stock transactions or one of the others?


          I normally do it this way:


          In the transactions table:

          Define a calculation field, cBal as Qty_in - Qty_out


          Define a running total summary field as the Total of cBal. This would then be set to restart totals when grouped by location--the "innermost" group in your summary report.


          Put this field on your summary report and base the layout on your transaction table. The only relationship that your report would need would be one that matches only by product ID and then only to reference data from the products table if there is data there that you need in your report.

          • 2. Re: inventory balance by lot # and then location

            Thanks for your response philmodjunk.


            Yes that is correct, the Summary report with sub summary is based on the Stock Transaction table.


            I realize my mistake after closely reading your response. I was using Summary to total sBalance but sorting restart using Location_ID from Location table instead of Location_ID from Stock Transaction table!!! OMG! Thanks