6 Replies Latest reply on Feb 9, 2011 11:19 AM by philmodjunk

    Grand total Date range

    AlexGodines

      Title

      Grand total Date range

      Post

      What I'm trying to do is print out a report layout of a customer with the last 3 jobs they have done with my complany. i got that done now what i want to do is to have a grand total but only pulling the grand total of the invoices within a certain timeframe. how would i do that.

        • 1. Re: Grand total Date range
          philmodjunk

          How did you accomplish the first part? a report layout of a customer with the last 3 jobs they have done with my company.

          How you did that will tell us what method might be possible to compute your totals. If you do this by pulling up a found set of the three job records, for example, summary fields can be defined to compute any totals you need and these can be placed in the header, footer, or grand summary layout parts.

          • 2. Re: Grand total Date range
            AlexGodines

            Well in the portal you can setup so it shows certain amount of lines(jobs) its gonna print out normaly it was setup to print the first invoices made so instead of accending the dates it would decend and it would show the last 3 jobs basicly fliped it top to bottom to bottom to top. now im trying to set it up so i can pull the grand total of a certain timeframe instead of what i have that pulls the total of ALL invoices done by that person. for example

            Contact John Doe
            last 3 jobs

            Grand total 4,000           Total for 2009-2010  1,500

            • 3. Re: Grand total Date range
              philmodjunk

              So you need the Grand Total of the first three rows in the portal. Are you filtering the portal in any way? (I don't think you are but need to be sure)

              The 2009-20010 total is straight forward. You can set up a relationship that uses two fields for start and end dates (or years) and use a calculation field with the sum to get that total.

              Define a pair of global fields: gDate1 and gDate2 in your report's table.

              Use this relationship:

              YourReportTable::gDate1 < JobsByDateRange::DateField AND
              YourReportTable::gDate2 > JobsByDateRange::DateField

              JobsByDateRange is a new table occurrence of your jobs field. You create it in Manage | Database | Relationships by clicking jobs and then clicking the button with two green plus signs. Now define this calculation field in your report table: Sum ( JobsByDateRange::YourField ) to compute the total of all records falling within the date range you specify by entering dates into the two global fields.

              The "last three jobs" total is more of a challenge as they really don't have any value in common on which to base a relationship. I did figure out a way to do this if you are using FileMaker 11 that's pretty simple. Please confirm whether or not you are using FileMaker 11 and I'll describe this method for you. With older versions, I think you'll need a script for this total.

              • 4. Re: Grand total Date range
                AlexGodines

                I dont need the grand total of the last 3 jobs but thanks anyways. Main goal is to pull a grand total for a year's range or specific date range.

                • 5. Re: Grand total Date range
                  AlexGodines

                  Are gDate1 and gDate2 New fields? and are they under the JobsByDateRange? and is Datefield under the JBDR table.

                  • 6. Re: Grand total Date range
                    philmodjunk

                    gDate1 amd gDate2 would be global date fields defined in your main table--not the jobs table.

                    I used DateField as the name of the field in your Jobs table that contains a date for that record. You would subsitute the name of your date field for that field name.

                    If table occurrence is a new term, you might want to read this tutorial:

                    Tutorial: What are Table Occurrences?