1 2 Previous Next 16 Replies Latest reply on Oct 16, 2013 2:05 PM by pinchvalve

    How to calculate subtotals from data entered into portals?

    pinchvalve

      Title

      How to calculate subtotals from data entered into portals?

      Post

           I have found MANY answers to this question, but none of them seem to work for my situation.  The screenshot shows my situation. 

           The "Quote" layout is based on the table <quote_main>.  Each quote has a an ID and some other relevent fields.  

           Each quote can contain multiple units, so on the left is a portal that displays information from the <units> table. This is a standard portal setup, linked back to the <quote_main> by the quote_ID.  

           Each unit is made up of several line items, each with a different price.  These are displayed on the portal on the right, based on a table called <line_items>.  This works by clicking the "select" button in the units portal, which changes the value of the field in the <Quote_Main> table to which the <line_item> table is related.  The portal on the right only shows what's related to the active unit, it is not filtered.  

           So the question is, how do I enter a field in one table, that goes out and adds up a defined set of fields in a second table? 

      Screen_Shot_2013-10-11_at_3.44.04_PM.png

        • 1. Re: How to calculate subtotals from data entered into portals?
          philmodjunk

               But exactly what are you totaling?

               All the records shown in the portal or just some of them?

               If just some of them, what determines which  portal records are included in the total and which are not?

          • 2. Re: How to calculate subtotals from data entered into portals?
            pinchvalve

                 Each record in the <Line_Item> table includes an "Unit_ID" field that identifies which Unit the line item is related to, and a "total_price" field.  (cost, times quantity less discount).   I want to sum the "total_price" fields for each unit.  

                 Using the example below I want Unit 1111 to display a total price of 200,000 and unit 2222 to display a total price of 100,000, and Quote ABC to display a total price of 300,000.

                                                                                                                                                                                                                                                                                                                                           
                                Quote ABC
                                Unit ID                     Total Price
                                1111                     100,000
                                1111                     100,000
                                2222                     50,000
                                2222                     50,000

                  

                  

                  

                  

                  

                  

            • 3. Re: How to calculate subtotals from data entered into portals?
              philmodjunk

                   And which do you want to see in your portal?

                   Individual line items are still displayed

                   Unit ID                           Total Price
                   1111                                  200000
                   1111                                  200000
                   2222                                 100000
                   2222                                 100000

                   or

                   individual line items replaced with one row for each unit ID

                   1111                                 200000
                   2222                                100000

                   The first option can be set up via a self join relationship that matches LineItems to a new table occurrence of the same but with two pairs of match fields, the quote ID and Unit ID fields. Then Sum ( NewTableOccurrence::Net ) can be used to compute the subtotal for each unit.

                   ExecuteSQL could also be set up to compute and display the same value and this would not require adding the additional table occurrence to your Relationship map.

              • 4. Re: How to calculate subtotals from data entered into portals?
                pinchvalve

                     Ideally, each line in the portal for the UNIT will display one grand total for all of the line items related to it.  

                     Currently, I can get a total for whatever unit is currently selected, but that only works on the screen, and only if I click somewhere outside of the portal. That will not carry over to a printed version of this layout, which I eventually have to do.  

                     I am researching some posts where people have asked for a FileMaker version of SumIf (from Excel) which might be the right direction.  Most things I have found assume that you are able to enter a constant into your calculation (like a month).  I cannot do that because my identifying field is calculated and I may have hundreds of them over time.  

                • 5. Re: How to calculate subtotals from data entered into portals?
                  philmodjunk

                       The method that I am describing will provide a total for every unit you list in Line Items. No SumIF--which really can't be done except via ExecuteSQL--you have to get the same result with a "work around", needed.

                       But it would display that sub total as a column in the portal as you can't add sub summary layout parts as a row inside a portal.

                  • 6. Re: How to calculate subtotals from data entered into portals?
                    pinchvalve

                         I don't think that I have anything in the <Line_Item> table that will allow me to relate it back to the <Quote> table directly.  Line items are related to Units, which are then related to Quotes.   I'll have to stare at it a while and see if I can see somehow to connect it.  

                    • 7. Re: How to calculate subtotals from data entered into portals?
                      philmodjunk

                           While you already have a relationship that links quotes to line items--otherwise your portal wouldn't work, this isn't what I am suggesting. You would link LIneitems to an additional Tutorial: What are Table Occurrences? based on LineItems, not Quotes.

                      • 8. Re: How to calculate subtotals from data entered into portals?
                        pinchvalve

                             Well, the link between the Quote and the Line Items is a field that is set whenever you click the "select" button.  It only shows the current record, so it won't work for all of the records.  

                             If I create a self-join on the Line Item table, I don't have a quote field to use as the second relationship.  I suppose that I could try to set a field with the Quote ID when I create a line item?  

                        • 9. Re: How to calculate subtotals from data entered into portals?
                          philmodjunk
                               

                                    If I create a self-join on the Line Item table, I don't have a quote field to use as the second relationship.

                               I'm afraid that statment does not make any sense.

                               You already have the fields that you need or your portals would not work on your layout as they do now.

                               While the names will be different, you appear to have these relationships now and they are the standard relationships used in most invoicing systems:

                               Quotes----<lineItems>-----ProductsServices

                               Quotes::__pkQuotesID = LineITems::_fkQuotesID
                               ProductsServices::__pkProdID = LIneItems::_fkProdID

                               So if you duplicate the LIneItems table occurrence, you can set up this relationship:

                               LineItems::_fkQuotesID = LineItems|SameUnit::_fkQuotesID AND
                               LIneItems::_fkProdID = LineItems|SameUnit::_fkProdID

                               Then you can define cUnitSubTotal as a calculation field in the LineItems table with "lineItems" specified as the context table:

                               Sum ( LineItems|SameUnit::Total Price )

                               And you will get a different sub total in the cUnitSubTotal field for each unit specified in the portal tot LineItems.

                               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                          • 10. Re: How to calculate subtotals from data entered into portals?
                            pinchvalve

                                 What I have is: Quotes----<Units----<Items...One to many, to many.  

                                 The Quote::__pkQuoteID=Units::__fkQuoteID, and the Units::__pkUnitID=Items::__fkUnitID.  Therefore the Items table does not include a direct link to the Quote.  But I can certainly include the QuoteID on the Items table.  

                                 It's too late to work on it anymore tonight, but I will be breaking down your suggestion in the morning and see what I can make of it. Thanks!

                                  

                            • 11. Re: How to calculate subtotals from data entered into portals?
                              pinchvalve

                                   Can't seem to get anything to total at all, it just puts the first record in the <line items> table into each unit.  I am confused about Sum ( LineItems|SameUnit::Total Price )  What is |SameUnit?   How do I express that?  

                              • 12. Re: How to calculate subtotals from data entered into portals?
                                pinchvalve

                                     Crap, I just found an error on my side.  Still working on it...

                                • 13. Re: How to calculate subtotals from data entered into portals?
                                  philmodjunk
                                       

                                            But I can certainly include the QuoteID on the Items table.

                                       Yes, but it will have to be a stored, indexed field in order for this to work and many methods you might use for that won't be stored/indexed.

                                       An auto-enter calculation can copy the value.

                                       But what purpose is served by placing Units between quotes and Items? Is the layout you show'd based on Quotes or Units?

                                  • 14. Re: How to calculate subtotals from data entered into portals?
                                    pinchvalve

                                         The layout is based on quotes.  A quote can have multiple units, so there is a direct relationship there and a corresponding portal.  

                                         Each unit however, is made up of a series of line items that determine the configuration and price of the unit.  That is the portal on the right.  The relationship there is between whatever unit is currently selected and the quote.  When you click the select button, it sets a field in the QUOTE table as that UNIT_ID.  So that field is constantly changing.  It is a one-to-many relationship, but the one can be manually changed by clicking "select".  

                                         However, whenever you add a line item, the new record includes the current Quote_ID and the UNITI_ID for the active unit.  So I think there is a way to use that to calculate the summary.  

                                         I need a field in the <UNIT> table that says: 

                                    "go out to the <Line Items> table, and sum the "TOTAL COST" field for all records in the table where the "for_UNIT_ID" field matches the "ID" field of this record, and put the total in this field.  And update it as new records are added to or deleted from the <Line ITems> table so that it i a running total." 

                                         That would give me a total cost for each unit that I could display as I am building a unit.  Then I could add all of those units to get a quote total. 

                                          

                                          

                                    1 2 Previous Next