13 Replies Latest reply on Jan 5, 2011 12:54 PM by philmodjunk

    Getting A Sum From a Parent Record

    RonCates

      Title

      Getting A Sum From a Parent Record

      Post

      I have a report that is working very well except for one part that I must be missing something on.

      Two tables

      Invoices
      Jobs

      The report is based on the invoices table. It sorts and sums invoices by year, job, status and so forth. Each job has a number of sets. In the report I am trying to add a total number of sets in the Trailing grand summary part. I tried a summary field in Jobs placed in the trailing grand summary but no luck. I tried a calculation field in Jobs with the sum(sets). No good. I tried a calc field with get summary to the summary field in Jobs. Still no good. As far as I can tell it is just pulling the number from 1 job record.

      So what is the trick to pulluing a summary from the parent table in this kind of report?

       

      Thanks
      Ron

        • 1. Re: Getting A Sum From a Parent Record
          philmodjunk

          What do you mean by "sets"? I'd need to know exactly how you've structured the data you want to sum up in Jobs.

          If this data is stored in a single field in separate Jobs records based with this relationship:

          Invoices::InvoiceID = Jobs::InvoiceID

          Then Sum ( Jobs::FieldtoBeTotaled ) defined in Invoices will compute the total for any given invoice and a summary field in Invoices can compute the total of this calculation field to give you a grand total, but I'm not sure what you mean by "sets" here.

          • 2. Re: Getting A Sum From a Parent Record
            RonCates

            The relationship is defined as you mentioned:

            Invoices::InvoiceID = Jobs::InvoiceID

            So the report is based on Invoices where there are roughly 15,000 invoices from 170 jobs. Each job has 1 or more sets ( Just an industry term ) which is recorded on each job record in a number field.

            • 3. Re: Getting A Sum From a Parent Record
              philmodjunk

              Yes, but how do you record more than 1 "set" for a given job? separate records in jobs? In invoices? or do you have multiple fields within the job record?

              • 4. Re: Getting A Sum From a Parent Record
                RonCates

                "Each job has 1 or more sets ( Just an industry term ) which is recorded on each job record in a number field."

                It's just a number field in the Job record that hold a number. I don't understand what more you need for an answer?

                • 5. Re: Getting A Sum From a Parent Record
                  philmodjunk

                  So if a job has three "sets", do you have 3 numbers in three different records, 3 numbers in three fields of the same record or just one number in one record?

                  How you compute the total for a given job record depends on whether that second option describes your situtation or not.

                  Once you have a total for a given job record (Simple if it's 3 number in 3 records or 1 number in one record), A summary field in Jobs should be able to compute the total for you. Yet you indicate that didn't work for you, which is why I'm trying to determine what issue kept it from computing the grand total that you wanted.

                  Also: Summary fields compute values based on what records are present in the current found set. If a summary field does not compute the expected grand total, make sure the correct records are in your found set.

                  • 6. Re: Getting A Sum From a Parent Record
                    RonCates

                    If a job has three sets then there is a number 3 in the sets field of that 1 Job record.

                    The summary field I created to sum the set field works. For insdtance on a layout with a portal showing a number of Jobs, if I put that summary field outside the portal than it shows the total number of sets for those jobs. Bu if I put the summary field in the Trailing Grand Summary of the report in question instead of showing 410 which is the total number of sets for that found set of 170 Jobs in the report, it shows 1.

                     

                    • 7. Re: Getting A Sum From a Parent Record
                      philmodjunk

                      A summary field from Jobs placed on an invoices layout won't be affected by the current found set in jobs. It will report the total of all the related job records for a specific Invoice record. When you place this field in a trailing grand summary part, you'll get the total for the jobs records related to the last invoice--which could be just 1.

                      To get the grand total of all records in the Jobs table, but displayed on an invoices layout, you'd need a relationship that matches any record in Invoices to all the Jobs records. (If you only need to match to some of your jobs records, you'll need a different relationship than what I'm about to post here.)

                      Invoices::anyfield X AllJobs::AnyField

                      AllJobs will be a new table occurrence of Jobs. Using the X instead of = will get the match all records relationship you need and you'd place your summary field on your layout by selecting it from AllJobs instead of Jobs.

                      • 8. Re: Getting A Sum From a Parent Record
                        RonCates

                        "If you only need to match to some of your jobs records, you'll need a different relationship than what I'm about to post here."

                        That's the one I'm after. The report is based on a found set of invoices. In turn, those invoices belong to a set of related jobs. So to run the report I do a find for all the invoices for 2011 for instance. I then sort  by jobs, which gives me all the jobs for 2011 with the corrosponding totals. At the end, the Trailing Grand Summary gives me the total number of jobs for the year and all the invoice totals for the year. That's where I would also like to be able to show the total sets for the year ( Or whatever the found set criteria might be ).

                        So the trailing grand summary should show something like this:

                        170 Jobs      410 Sets   15,000 Invoices for $1,250,000

                        The only part I can't get is the sets.

                         

                        • 9. Re: Getting A Sum From a Parent Record
                          philmodjunk

                          How do you get 170 Jobs? The same method should work for getting the total sets.

                          • 10. Re: Getting A Sum From a Parent Record
                            RonCates

                            Sorry Phil. My day has gotten crazy and I'm being pulled in all different directions. I am going to have to get back to this tomorrow or later if I can break away from other things.

                            By the way, I just checked and realized the total jobs isn't working either, so I actually need to figure out both.

                            • 11. Re: Getting A Sum From a Parent Record
                              philmodjunk

                              This situation isn't as straight forward as it first looks. At first glance, it looks like you could use count and sum functions in Invoices to count the total number of jobs and sum the sets, but since multiple invoices match to the same job record, this would result in counting/summing the same data multiple times within your report. I'd also assume that a given job record may also be related to invoice records that aren't part of the current report. Otherwise, I'd use a "sum the fraction" trick to cancel out the repeated references to the same job record.

                              I keep looking for a clever combination of relationships and calculations that will produce this count and set total, but can't come up with one.

                              I can script it:

                              Define a summary field to total up the sets. I'll call it sTotalSets

                              After performing a find to pull up the invoice records you want for your report, use a script that:

                              Uses Go To Related Records to pull up a found set in a Jobs layout of all related Jobs, using the Match found set option.

                              Then:

                              Set Variable [$$JobCount ; Value: Get ( Found Count ) ]
                              Set Variable [$$TotalSets : Value: sTotalSets ]
                              Go To Layout [Original Layout]

                              If you place these global fields (FileMaker 11 only) on your remort layouts as merge text:

                              Jobs: <<$$JobCount>>  Sets: <<$$TotalSets>>

                              You'll see your totals.

                              • 12. Re: Getting A Sum From a Parent Record
                                RonCates

                                Aaaargh!! I just typed out a long answer and when I submitted something went wrong and it all vanished. Anyway. The script solution will work fine. I had been trying like you to figure out a calc or relationship and hadn't thought of a script. I'm on FMP 10 so I'll have to create a couple global fields but that's no problem.

                                Thanks for your help Phil

                                Ron

                                • 13. Re: Getting A Sum From a Parent Record
                                  philmodjunk

                                  Yes, disappearing posts is a known issue that I've reported to Modman. I've taken to copying a post to the clipboard just before clicking the post answer button so I don't have to re-type.

                                  I've seen two different scenarios where a post is lost:

                                  1) You get a different layout and are instructed to log back into the forum.

                                  2) you get an error message telling you either that the comment could not be posted or that you can't post an empty comment.

                                  Which happened to you? If number 2, did you spell check your post before posting?