5 Replies Latest reply on Mar 6, 2015 12:34 PM by keywords

    Use two summary fields in a calculation

    twelvetens

      Hi,

       

      I have a table of stock transactions.

      Each record has a quantity, and a type:

      Stock IN

      Stock OUT

      Loan IN

      Loan OUT

      ....And a depot

      ....and various other fields.

       

       

      I've got two summary fields:

       

      Summary of StockIN (total of quantity of any record which is of type Stock IN)

      Summary of StockOUT (total of quantity of any record which is of type Stock OUT)

       

      I want another calc to show the "Summary of Stock IN" less "Summary of Stock OUT", so that I can show the "Summary of Stock Current", and I want to be able to show this in multiple summary parts, depending on what fields the table has been sorted by.

       

      I can't get the summary fields to work in calculations, and I'm banging my head against the GetSummary function.

       

      Can anyone help?

        • 1. Re: Use two summary fields in a calculation
          keywords

          You could try:

          GetSummary ( Summary of StockIN ; Summary of StockIN ) - GetSummary ( Summary of StockOUT ; Summary of StockOUT )

           

          But … this will be an unstored calc, and the summaries themselves are unstored, so beware of performance issues if the dataset is large.

           

          You might be better to look at another method for tracking, e.g. replacing the summary fields with number fields that are set to auto enter by calculation and update as stock transactions occur.

          • 2. Re: Use two summary fields in a calculation
            mikebeargie

            I recommend you make your inventory table "transactional", storing inventory in/out "transactions" in a table related to your products table with a +/- value depending on the direction. Ideally you would want your "amount in inventory" to be in the products table, not in the inventory transactions table, to avoid tricky summary issues like you are running into.

             

            Setting it up with the following relationship:

            products::primaryKey --<- inventory::productKey

             

            And configuring this auto-enter calc on a number field:

            inventory::inventoryValue = if ( inventory::type = "outgoing" ; -1 * inventory::quantity ; inventory::quantity )

             

            That way you could easily use this to get "Amount in stock":

            products::amountInInventory = sum(inventory::inventoryValue)

             

            This type of setup allows you to place a portal on your products detail view as well to see a complete transactional history of all inventory coming in and out, and makes audits and inventory checks a breeze. It also helps avoid duplicate products in your system. And you don't have to divide two summary fields, resulting in a faster calc as well.

            • 3. Re: Use two summary fields in a calculation
              twelvetens

              Thanks Mike, I think I've sorted it now, by ensuring that stock in a positive integer value, and stock out is represented by a negative value (despite what's show to the user in the interface) the summaries have become much easier.

               

              Thanks for all your help!

              • 4. Re: Use two summary fields in a calculation
                twelvetens

                I tried this approach, but I just couldn't get the summary's to show the correct data given their placement on the specific part in the layout. Solved it another way now, but thanks for the idea...

                • 5. Re: Use two summary fields in a calculation
                  keywords

                  No worries. The system proposed by Mike is much better anyway.