6 Replies Latest reply on Nov 1, 2014 2:51 PM by madmike6537

    Summary Fields

    madmike6537

      Hi All,

       

      This seems easy, but for some reason I cant wrap my head around how I need to use either summary fields, or a calculation to get the results I am looking for.

       

      Basically I want show to a user a simple layout in FMGO13, that will show them how many jobs they have completed and how much those jobs were worth total, by month. I suppose at some point they might want to see what all the jobs were but right now I am just trying to get the basics down. Each job has a room (these are houses) and we bill by room so my tables are as follows:

       

      So my tables are: Customer -< Room -< Line Items

       

      How can I show this information on one layout? I am sure I am just overthinking this..

        • 1. Re: Summary Fields
          Stephen Huston

          You can use calculations in the Parent table (Customer) to return Sums and Counts, such as

               Count (Room::foreignKeyField)

          and

               Count (Line Items::foreignKeyField)

          to return counts of records in related tables.

           

          You have to be a bit creative with placing time ranges on the relationships to get summaries of amounts within a period, but the methodology is the same using calculation fields and the Count or Sum functions in FM's calculation engine.

          1 of 1 people found this helpful
          • 2. Re: Summary Fields
            madmike6537

            Ok that helps. Could I count up the total dollar figure of all the line items in my grandchild table from the customer table?

            • 3. Re: Summary Fields
              Stephen Huston

              Yes, using a calc field in the Customer table:

                   Sum (Line Items::TotalDollarField)

              That one won't break it down by Room record, but will give a single value from the target related table.

              • 4. Re: Summary Fields
                madmike6537

                Nice, so I guess that just leaves me to figure out how I can be creative with my filtering of dates as you mentioned. Would I be looking at using something like an IF block to filter out results or is there some way to omit records being included depending on a date field within the record?

                • 5. Re: Summary Fields
                  Stephen Huston

                  You could use global fields in the parent record as PART of the actual relationship definitions to the grandchild record's date, and then the SUM calculation field will actually update as you change the globals.

                   

                  If you use filtering at a portal level, where it's not really part of the relationship definition, the calc'd SUM function won't update based on filters not built in to the relationship definition.

                   

                  Add relationship conditions for RecordDate ≥ globalStartDate

                  and RecordDate ≤ globalEndDate

                  along with the basic key fields.

                   

                  You may need to setup a special relationship just for this calculation.

                   

                  You could also use a Get SQL calc result for the Sum of records with the correct foreignKey and the date ≤ and date ≥ (as above) without having to set up a special relationship, if you're willing to tackle the SQL option, which avoids having to modify the relationship graph at all.

                  • 6. Re: Summary Fields
                    madmike6537

                    Thanks I think this should get me started.