I'm in the process of designing a report and am running into issues thinking through how to narrow down a record set for the math of the report. My parent report table has records for each week, as well as output per week. These values are entered manually since there's no viable option for a data connection. The child table has detailed records of hours spent on task per department per day.

I've already managed to build the report to only show totals for one department by using the relationship as linked to a global value in the parent table.

My question is: how do I further narrow the calculations to only count hours for specific records in the child table? In the screenshot below, I'd like to limit the math in table R_T01 to include only records in LT_T03 to ones where the TaskTrackNum match one of six values (in addition to SubDeptNum of my choice).

My criteria for TaskTrackNum are 001, 110, 164, 551, 560, 569, 578

Here's the relationship diagram:

Here's a glimpse at the source data table:

And here's a section of the report layout:

Edited May 15, 20205 yr by RavingLuhn
Fixed image links

In general, it is best to produce a report from the most atomic table. In your case, that would be the "child" table. (I am not sure why you even need the "parent" table.) All you - or rather your script - need to do in order to produce a report like the one you show is to find the records you want to include in your report, sort them by year and by week, and show them using a layout with sub-summary parts by year and by week and no body part.

18 minutes ago, RavingLuhn said:

My criteria for TaskTrackNum are 001, 110, 164, 551, 560, 569, 578

 That would be handled during the find portion of the process described above.

 

Thanks. My brain struggles to grasp the atomic-level mode of thinking, but it's starting to sink in. I've got a find set and summary fields working as expected.

7 minutes ago, comment said:

(I am not sure why you even need the "parent" table.)

I have the records for output per week residing in the parent table. Since the child table has detail records to the tune of multiple per day, I need the output stored separately.

It's possible I may need to create more summary fields, right?

If you want to keep the weekly summaries as permanent records, then yes, you do need a table with a record per week. But you should ask yourself if you really do need this. The only good reason I can think of is performance - i.e. you need frequent access to the historical summary data and you have too many records to produce a summarized report on-demand each time. In such case, you would run a periodical process to summarize past weeks and create summary records for them. And of course, such process would be scripted, not manual. A point to keep in mind: if users are allowed to edit past data, you must have a mechanism to update past summaries following such edit.

You need a summary field for each value you want to summarize. The same summary field will display a different value according to its placement - e.g. a weekly sub-total when placed in a sub-summary by week part, a yearly sub-total when placed in a sub-summary by year part, and a grand total when placed in a grand summary part.

 

On 5/15/2020 at 4:18 PM, comment said:

But you should ask yourself if you really do need this.

The reason I have a separate table is that the two data sets come from wholly different processes. Tracking Data contains granular labor hour detail; hours per task per department per person per day kind of thing. The Statistics table holds a summary of output per week. There's no direct correlation between the labor hour records and output; that's why I need to keep the output records separate and then calculate everything based on weekly totals.

I did recreate the report based on the Tracking Data table, the most atomic, and am getting most of the numbers I need. However, I'm running into issues when trying to run calculations off the subtotals.

What I'm trying to do here is divide s_TrackedSeconds by c_PlantCount. I should wind up with 33.97, but get 9839.5:

Here's the calculation configuration for c_Seconds Per Plant Per Week:

I'm guessing part of the issue is that the s_ShippedPlants is coming from a different table... but that field displays the correct value when placed on the layout so I'm a bit confused. Why does the field show the correct value on the layout, but something goes AWOL when trying to use those fields in a calculation?

 

 

Edited May 18, 20205 yr by RavingLuhn
Fixed image link

37 minutes ago, RavingLuhn said:

However, I'm running into issues when trying to run calculations off the subtotals.

You need to use the GetSummary() function for this:
https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Fgetsummary.html%23

 

 

59 minutes ago, comment said:

You need to use the GetSummary() function for this:

Okay, that did it. Thanks for your help!