3 Replies Latest reply on Feb 4, 2015 1:28 PM by philmodjunk

    Sub Summary calculation with IF statement?

    bnmohr

      Title

      Sub Summary calculation with IF statement?

      Post

      My situation is somewhat like this thread:

      http://forums.filemaker.com/posts/709fab0962

       

      I am tracking audio signals through sound systems.  If I filter and sort lists of Signals, I can get a report of Devices with the following breakpoints

      Device::Location

      Device::Name/Model etc

      Signals::Inputs

      1  Signal Name

      2 Signal Name

      Outputs

      1 Signal Name

      2 Signal Name

      3 Signal Name

      4 Signal Name

      Next Device Name/Model...

      What I want is for each location, to to summarize the total number of inputs, and the total number of outputs.  I have the summarry field sInput Count that is the count of record ID's which properly sums the inputs but also the outputs for each device, however if I try to total those summaries at the "Input/Output" breakpoint, it's summing all of them together (both inputs and outputs)- what I want is a sum IF Input Output Flag="Input" and another sum IF = "Output"

      Ideas?

      My current solution is to convert the Input Output Flag text field into two number fields cIsInput and cIsOutput which is just the evaluation ="Input" or ="Output" and then total those fields at the end.  It works, I was just hoping there was a more elegant way to do this based on the sub summary lines.  In hind sight I should have defined the input/output flag as a number field knowing it would eventually end up in a calculation.

      Thanks

        • 1. Re: Sub Summary calculation with IF statement?
          philmodjunk

          I don't see why the summary field isn't giving you the two sub totals that you want here.

          If you put in a sub summary layout part to serve as a sub header for Input and output signals, this becomes a "sub heading" where you show the text "input" or "output" and then the individual records in the body list each signal. If you put the summary field inside the sub summary layout part, it will show the total count of input records in the input sub heading and the total count of output signals in the output subheading.

          The GetSummary function, BTW, does the same thing, but at the calculation level so that you can use such a subtotal in a calculation instead of the "grand total" you'd get in the same calculation if you just referred directly to the summary field in your calculation.

          In the case of a sub summary layout part and the GetSummary function, your records must then be sorted by the "break" field that you specify for the layout part or the function.

          • 2. Re: Sub Summary calculation with IF statement?
            bnmohr

            In the summary by Input and Output, yes- the summary does give me what I want- next to the header Input, is indeed the count of inputs and the same for outputs.  But if I want the total inputs at the Location level, it can not differentiate between the inputs and outputs.  So it is basically telling me how many signals are at that location.  Same for Get Summary (if I'm using it properly).

            • 3. Re: Sub Summary calculation with IF statement?
              philmodjunk

              I thought that might be the case, but didn't want to go there if I was wrong....

              This is sometimes called a "summary recap" and there are several different approaches that can be used.

              One is to set up a pair of calculation fields such as:

              cInputFlag:

              SignalType = "Input" //Select Number as your result type

              cOutputFlag:

              SignalType = "Output"

              You can then use summary fields to total or count these added calculation fields.

              It's also possible to use ExecuteSQL() to compute and display these totals: FMP 12 Tip: Summary Recaps (Portal Subtotals)

              With ExecuteSQL(), you only need the one calculation field. (Note: this is NOT the Execute SQL script step.)