8 Replies Latest reply on May 4, 2014 7:47 AM by SteveMartino

    Trying to total a list of calculation fields.

    SteveMartino

      Hello Forum. I have a multiple report layout, everything works fine for me. The one thing I cant seem to figure out is how to total a column of numbers that are based on calculation fields. I tried everything I could think of (which isnt much ) with no luck. I think this may be a job for Execute SQL, but even with everything I read online, it's just beyond my basic skill set.

      This report takes all the (in this case) fuel filters that are on the customers equipment (Amount in Use). I enter a number in the 'Amount in Stock' field. The 'Re-order Calc' then subtracts 'in use' from 'in-stock' times a multipler (to keep extra in stock) and generates an"Amount to Re-order'. This is the column I cannot figure out how to total.

      Currently I take this report, export it to an Excel Spreadsheet, total it there, then print/email from Excel to place an order.

      Here are 2 screenshots, (layout mode & browse mode).I'm sure I need alot more information, which I will happily provide.

      Any help would be greatly appreciated.

      Thanks

      Steve

        • 1. Re: Trying to total a list of calculation fields.
          erolst

          It is entirely possible to summarize a calculation field (if the result is Number … ) – if I read your screenshot and explanations correctly, the summary field would be defined as Total of Amount to Re-order

           

          If you not merely want to display the result, but also need it in a calculation, use GetSummary ( sTotalOfAmountToReOrder; yourBreakField).

           

          To get the summary result for a Grand Total of the found set, use GetSummary ( sTotalOfAmountToReOrder; sTotalOfAmountToReOrder ), i.e use the summary field itself as the function's break field argument.

          • 2. Re: Trying to total a list of calculation fields.
            SteveMartino

            Thanks erolst for responding.  Just can't get it to work.

            Total of Amount to Re-order is a calculation, result is number.  I get completely wrong numbers--instead of getting a result of 142, im getting a number of 13,353 (or something like that).

             

            This part I don't quite understand....."

            To get the summary result for a Grand Total of the found set, use GetSummary ( sTotalOfAmountToReOrder; sTotalOfAmountToReOrder ), i.e use the summary field itself as the function's break field argument....

            Are you saying to change the calculation field to a summary field, or create a new summary field?

            Any continued help is always appreciated

            Thanks

            Steve

            • 3. Re: Trying to total a list of calculation fields.
              beverly

              Steve, a summary fields depends on:

               

              1. Location (part) - header, body, sub-summary(s), grand total(s), footer. The same summary field will be different based on location.

               

              2. Sort - the sort of a sub-summary will make all the difference in the result, for example.

               

              So...

              We have a calculation function which probably does matter by location, but you specify what field would be sorted to make the "summary" as you need. GetSummary() is that function. Research it in the help topics.  You will even see a SORTING progress bar if the calc needs to do so before displaying.

               

              -- sent from my iPhone4 --

              Beverly Voth

              --

              1 of 1 people found this helpful
              • 4. Re: Trying to total a list of calculation fields.
                erolst

                SteveMartino wrote:
                Total of Amount to Re-order is a calculation, result is number. Are you saying to change the calculation field to a summary field, or create a new summary field?

                That won't work (as you have seen). You want to summarize across a found set, and calculation functions (Sum, Count, Avg …) can't do that – they work (basically) with fields from related records, or multiple fields from the same record, or both.

                SteveMartino wrote:

                Are you saying to change the calculation field to a summary field, or create a new summary field?

                The latter: create a new field (to be placed where your red circle is now) of type summary, definition: Total of, field: Amount to Re-order.

                 

                Imagine a vertical and a horizontal dimension – columns and rows. Apply calculation fields to columns to get results for a record (horizontally within a row; e.g. Table::amountInUse - Table::amountInUse); then use summary fields to summarize those rows vertically – to get (to) the bottom line, if you wish …

                 

                And as Beverly has noted: a summary field always summarizes a specific field; across which records that happens depends on the placement of the field (sub-summary, grand total etc.); this means you can use the same summary field to summarize different groups, or even the entire found set (but always the same target field …).

                 

                And GetSummary() is the function that you can use in a calculation to get at the value in a summary field, given a specific break (sort) field.

                 

                Maybe read up on summary fields; if you're creating reports, it's really important to know how they work: http://fmhelp.filemaker.com/fmphelp_12/en/html/create_db.8.18.html#1029235

                • 5. Re: Trying to total a list of calculation fields.
                  SteveMartino

                  Thanks to both for responding.  I guess it's just not my day .  Following erolst advice, I end up with a very large number.  Following Beverly's advice, FM stuck in sort mode for about 10 minutes...had to force quit.

                  I have a feeling I'm just not explaining the layout and the calculation fields properly.

                  Thanks both again, I'll take another stab at it at another time

                  Steve

                  • 6. Re: Trying to total a list of calculation fields.
                    erolst

                    SteveMartino wrote:

                     

                    Thanks to both for responding.  I guess it's just not my day.

                     

                    Had your coffee yet?

                     

                    Anyway, make sure you're putting this on a layout where you only display a controlled subset of your records. The big number and the loop are probably symptoms of too large a dataset.

                     

                    See if the attached sample helps you to Get ( BasicIdea ) …

                    1 of 1 people found this helpful
                    • 7. Re: Trying to total a list of calculation fields.
                      SteveMartino

                      I don't drink coffee ☕️. Maybe that's the problem  . Thanks for the sample file. I'll take a look at it tomorrow when I'm back at my office.

                      Thanks again.

                      Steve

                      • 8. Re: Trying to total a list of calculation fields.
                        SteveMartino

                        Thanks again to erolst. I really appreciate you taking your time and lending your vast experience to try to help.  My lack of experience in my "learn one/build one/use one" database for my small business is working against me.

                        I did look over your sample file, but I just cant get it to work for me. My database structure just doesnt line up with yours, ie. I don't have a body part in my report, and I don't understand sorting on the dummySorter field, or what that number represents.

                        When I remove the body part in your file, and put those fields in the sub-summary part (to try to replicate what my DB looks like), some of the calculations end up being incorrect (or looking more like the problems I have in my DB), drawing the conclusion that I need a body part in my report to make your solution work for me. 

                        Once again, thank you very much for responding and trying to help, but I think I'll let this go for now