8 Replies Latest reply on Apr 20, 2015 12:56 AM by BorisKamp

    Sum calculation field with this type

    BorisKamp

      Title

      Sum calculation field with this type

      Post

      Hi guys,

      I have a ledger table in our DB and all the entries make up for the total balance. For each wire / entry we can select the 'type' this is a popup field using a list as source.

      One type could be 'Renovation', now Im wondering how I can have a field 'total renovation' sum up only the wires of the type 'Renovation' and omit the other types?

      Thanks!

        • 1. Re: Sum calculation field with this type
          philmodjunk

          How to "selectively sum" data is a common request here. The problem with answering it is that there are multiple ways to do that and the method that works best for you will depend on both the design of your tables and the format on your screen or printed page that you want to produce.

          Here's the simplest method, assuming that you have one record per each entry as I interpret your reference to a "ledger".

          You can create a report layout based on the ledger table and set up a summary field that shows sub totals for each type with a grand total included at the bottom and/or top of the report. You'd perform a find for the records you want, sort the records by "type" (other fields can be included in the sort order) and the same summary field can display both the sub totals by type and the grand total for the entire report. The trick is to add a sub summary layout part "when sorted by type" to your layout in which you place this summary field. The report can be set up to list the individual items below or between such sub summary parts or you can set it up to "condense" the report down to one line for each type.

          See this thread for a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

          Note: "ledger" set ups often have a "debit" and a "credit" field. In such cases, you may need to add a calculation field, if you haven't already defined as Debit Field - Credit Field to show the net change for that entry. You'd then set up your summary field to compute the total of this calculation field to show the balance total.

          • 2. Re: Sum calculation field with this type
            BorisKamp

            Hi Phil,

             

            thank you for your thorough explanation.

            However, I already have a layout like in the attached picture and was wondering if I could just add a field that would sum / calculate all the renovation ledger entries? please note the "type" fields in the ledger.

             

            Thanks!

             

            • 3. Re: Sum calculation field with this type
              philmodjunk

              To repeat: "The problem with answering it is that there are multiple ways to do that and the method that works best for you will depend on both the design of your tables and ..."

              What I see here is data that is most likely stored in at least three tables. The layout's table and then you have two portals. What you describe is indeed possible, but I'd need to know a bit more about these tables and how they are related to each other.

              Plus, if you have FileMaker 12 or newer, you could replace this entire portal with a single large calculation field that produces the columns and totals shown. See this thread: FMP 12 Tip: Summary Recaps (Portal Subtotals)

              But using  a portal to list such sub totals is indeed possible with the correct set of relationships and a table where you have one record for each such "type".

              • 4. Re: Sum calculation field with this type
                BorisKamp

                Thank you for sharing Phil.

                the three tables you mentioned are indeed there:

                       
                1. Property table (layout) (with __pkPropertyID)
                2.      
                3. Property Ledger table (first portal) (with _fkPropertyID)
                4.      
                5. Partner Ledger table (second portal) (with _fkPropertyID)

                table 2 & 3 both contain a field called 'type' which is a text field that uses a value list.

                please let me know if you need more information on my structure!

                Thanks!

                • 5. Re: Sum calculation field with this type
                  philmodjunk

                  Therefore, in your "renovation" example, you want a sub total that sums all renovation entries with the same _fkPropertyID value?

                  and the data to be summed is in which table? Partner Ledger? or is there a fourth table with this data?

                  • 6. Re: Sum calculation field with this type
                    BorisKamp

                    Sorry for my late response Phil!

                    you're right, I need a field that sums all the renovation entries with the same _fkPropertyID value. the date to be summed is in the Property Ledger Table.

                    Thanks!

                    • 7. Re: Sum calculation field with this type
                      philmodjunk

                      And you need the same sub totals for the other types shown as well, correct?

                      Properties---X---<Types------<Property Ledger

                      Propterties::AnyField X Types::anyField

                      Types::Type = Property Ledger::Type AND
                      Types::gPropertyID = Property Ledger::_fkPropertyID

                      You have one record in Types for each type for which you want such a sub total calculated. gPropertyID is a field with global storage specified. You'd set up a script performed by the OnRecordLoad trigger that updates it with the ID of the current Property record:

                      Set Field [ Types::gPropertyID ; Properties::__pkPropertyID ]

                      A calulation field in Types can then calculate the sub total: Sum ( Property Ledger::Amount ) and you'd use a portal to Types to list the types and the associated sub totals.

                      A single calculation field using ExecuteSQL and no scripting or added relationships could be set up to compute and display the same list of of sub-totals:  FMP 12 Tip: Summary Recaps (Portal Subtotals)

                      • 8. Re: Sum calculation field with this type
                        BorisKamp

                        Thank you for your answer Phill.

                        Yes I would like this for other types as well.

                        To achieve it the way you describe it needs a new table and re-assigning the types for each ledger entry (since they now use a value list) 

                        I just need a field with the total repairs underneath the portals as shown above in the screenshot. I don't need a portal with subtotals for each type since we're only interested in some, I would just like to keep the layout as show above.

                        Can you confirm that this is the smartest approach and I need to do some serious modification to those tables in order to achieve this one field data (total repairs)?