When building reports, the data is in the child-table. Portals are great for displaying child-table data from the parent record. However, reports should be built from the child-table. The fields that your filter the portals on would be the break fields for subtotaling the data in the report.
Right, the problem is this though:
The portal is the child table, correct, the details is the parent table. All of that works fine. However, I need to summarize data that occurs in the portal as well as summarize those summaries.
Portal shows Time Off Occurrences on new lines like such:
Vacation March 2 - March 4 Total 2 days
Sick Leave March 4- March 6 Total 2 days
Lieu Time March 6 - March 10 Total 4 days.
So i have a summary field which summarizes the data in the total field in the portal: Total Days Off = 8
But I have 48 employees. I need to be able to total up all of the Total Days Off fields so that if I have:
Joe: Total Days Off = 8
Sue: Total Days Off = 20
Bob Total Days Off = 12
I can display a field like Grand Total Days Off = 40 that adds up all the days off everyone has had.
I can envision several possibilities. All 48 employees with amount of time taken by type, or a total of the three types subtotaled. Without seeing your data structure it is a little harder.
Sort on Person on Type.
Even with the followup information provided, this still sounds like a report that can be built on a layout based on the child table. You can perform a find to find all records in the specified date range and all or just some employees.
But it is also possible to set up other approaches that work from your current layout. But we'd need a more detailed understanding of how you record this data in your fields and tables.
It will also be helpful to know whether or not you are using FileMaker 12 or newer as FileMaker 12 introduces a new function:: ExecuteSQL that can be used to compute such a total more simply than some alternative options we might use with older versions.
TKnTexas, that is exactly what I want to do. I want to be able to see all 48 employees with potentially a filter to see each type of time off they've taken, and a subtotal that adds up all the time of taken company-wide.
However I can't seem to do that. I also can't seem to upload an image so you all can see my data structure. It's fairly straightforward though: PersonnelDetails is the parent table, and it is linked to TimeOff by field "pk_PersonnelID"
I have all 48 employees showing up in a "Reports" layout that is based in the PersonnelDetails table and shows the portal to the TimeOff table. I have this layout viewing in list view so you can see all 48 employees' time off. The problem is that I don't know how to get a total of all the days off company wide.
Phil, I'm using Filemaker Pro 13.
Do you use one field for all types of time off with a "type" or "category" field in TimeOff identifying the type of time off taken?
Or do you have separate fields for each kind of time off?
You should be able to upload an image to this thread by capturing a jpg, png or gif format image of manage | database | relationships and then using the upload an image controls found below Post a new Answer to upload it. If you are using windows, you can press Alt - Print Scrn to copy the current window to the clipboard, then you open Windows Paint, paste into Windows paint, crop your image as needed and then save the file as one of the three file formats supported by the upload am image feature of this forum.
I'm on a Mac. But thanks for the info!
There is one field for all types of time off. It's a drop down with the five types of time off there to choose from.
Some of the suggestions from this post might help:
@Phil (Hi again Phil), regarding your suggestion to the original poster about SQL, when you get a sec...how might that query look?
To get a grand total, you can:
a) define a summary field in the TimeOff table that computes the total time off. If you pull up a summary report for this on a layout based on the TimeOff table, Sub Summary layout parts can include this summary field to show sub totals for each employee, each type of time off and with different sorting orders those sub totals can be arranged in a number of different ways. Then the same summary field, when placed in the header, footer or a grand summary layout part, will show the grand total. You can get different results on such a layout be performing a find for different groups of records and by sorting the records by different sort orders to get different groups for the sub summary part controlled sub totals.
See this tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial
b) Take that same summary field from a) and put it on a layout based on a different table. Then the relationship between the two table occurrences will control what total you see in that field. If you set up a relationship using the X operator to match to all time off records, a summary field selected from that table occurrence will show a total based on all records in the table. If you use match fields that match to all records in a given date range, you get a total based on that date range.
c) put the same summary field in a one row filtered portal. Then the summary field displays a total based on what related records also pass the portal.
For some ideas on how to use ExecuteSQL, see this thread: FMP 12 Tip: Summary Recaps (Portal Subtotals)
Option A worked perfectly. Thank you again for all your help!