8 Replies Latest reply on Mar 7, 2015 7:52 PM by philmodjunk

    Help with Sub-totalling data

    PeterHolstein

      Title

      Help with Sub-totalling data

      Post

      I have data in a table structured like this:

                                                                                                                                                                                                                                                                                                                                                                               
      Deal IDAmountType
      1$10F
      1$12F
      2$16F
      2$30N

      How do I write a formula for use in a portal that totals Amount by Type?

        • 1. Re: Help with Sub-totalling data
          philmodjunk

          There are several ways to do that, but how do you want the portal as a whole to look?

          Do you want the rows shown but with an added column for the sub total? (which will then appear on every row.)

          Or do you want one row for every Type--reducing your example data to two portal rows?

          • 2. Re: Help with Sub-totalling data
            PeterHolstein

            I want to use each sub-total in a formula, one formula for the data type F and one for the data type N, each of the two formula's will be in the same portal row, and the portal is already set up to look-up each row by the first column (DealID)

             

             

            • 3. Re: Help with Sub-totalling data
              philmodjunk

              That makes no sense to me. There would be only one "formula" used and putting both subtotals in the same portal row doesn't make any sense to me given the data you show.

              Here's what I am asking:

              Do you want this:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
              Deal IDAmountTypeSubtotal
              1$10F$36
              1$12F$36
              2$16F$36
              2$30N$30

               or this?

                                                                                                                                                                                                                                           
              Deal IDSubtotalType
              1$36F
              2$30N
              • 4. Re: Help with Sub-totalling data
                PeterHolstein

                First off, thanks for engaging on this!

                Maybe if I give you more detail on the use we can zero in on a solution.

                The actual table has 69 rows.  There are 14 "DealID" types, each row has the value 1-14 in that field.  Within each of the 14 DealID cases, there are anywhere from 2 to 16 occurrences of a dollar amount, and each dollar amount is flagged as either of F or N in the "Type" field.

                In the portal, each row starts with DealID.  Within each row I am trying to create two fields:  one takes the sub-total of amounts type N for each DealID and the other takes the sub-total of amounts type F for each DealID and does some math on them.

                Does this help narrow the possible solution?

                • 5. Re: Help with Sub-totalling data
                  philmodjunk

                  That definitely provides info not found in your original post. I am interpreting your last answer here to mean that you want the first of my two examples but with two columns of sub totaled data.

                  If you are using FileMaker 12 or newer, there are two basic approaches that come to mind: a) self join relationships to two other occurrences of this table that match to just the records to be summed or b) a pair of calculation fields with executeSQL function call that uses SQL to define the same relationships and calculate the same subtotals.

                  If you are using an older version, a) is your only practical option.

                  Currently, you have this relationship in order to have a portal:

                  LayoutTableOccurrence-------<PortalTableOccurrence (----< means "one to many" )

                  By adding two more occurrences of the portal table, we can set up relationships that match to records with the same ID but with a specified type of either "F" or "N". I will demonstrate how to do this with "F" types and leave it to you to generalize the approach to match to "N" types to get the same type of sub total.

                  Tutorial: What are Table Occurrences? of the PortalTable, I'll name it "PortalFTypes" and define this relationship:

                  PortalTableOccurrence::ID = PortalFtypes::ID AND
                  PortalTableOccurrence::constF = PortalFTypes::Type

                  constF would be defined as a calculation field that always returns the text constant: "F".

                  Now you can define a calculation field that evaluates from the context of PortalTableOccurrence with this expression: Sum ( PortalFTypes::amount ) to compute the sub total of all records with the same ID and which are labeled as Type "F".

                  • 6. Re: Help with Sub-totalling data
                    philmodjunk

                    Forgot something that may help you understand my answer. For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                    • 7. Re: Help with Sub-totalling data
                      PeterHolstein

                      thanks

                      got it working using a summary field and then calling totals in the summary field in the portal

                      your guidance was extremely helpful!!

                      • 8. Re: Help with Sub-totalling data
                        philmodjunk

                        I prefer sum, it often updates more smoothly, but in this context the summary field will work as well and produces an identical sub total.