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.
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
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.
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.
Are gDate1 and gDate2 New fields? and are they under the JobsByDateRange? and is Datefield under the JBDR table.
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: