Jump to content

How to sum records in child table that meet criteria?


Recommended Posts

Posted (edited)

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:

h3cCBI7.png

Here's a glimpse at the source data table:

4xvmCFr.png

And here's a section of the report layout:

TstAlcR.png

Edited by RavingLuhn
Fixed image links
Link to post
Share on other sites

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.

 

  • Like 1
Link to post
Share on other sites

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?

Link to post
Share on other sites

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.

 

  • Like 1
Link to post
Share on other sites
Posted (edited)
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:

5IsoSC6.png

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

OdPKv3m.png

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 by RavingLuhn
Fixed image link
Link to post
Share on other sites
59 minutes ago, comment said:

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

Okay, that did it. Thanks for your help!

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.