Referencing sub-summary values from related table?

[RavingLuhn]

It's me again. This time my question is centered around how to reference a summary value from table A in a calculation in table B. The end goal is to calculate a running average of seconds per plant in the 'TrackingData' table by dividing time in seconds 's_Cumultaive Seconds' by 's_Cumulative PlantCount' from the 'Shipping Volume' table.

After doing a bit of research I know that it's not possible to use the values of a summary field from table A in table B. So I made a field in 'TrackingData' to try and pull over the values from the 'Shipping Volume' table.

That field does return the correct number of plants for each line in my subsummary, but it's not a running total. When I tried to make a summary field based off c_Cumulative Plants in 'Tracking Data'; it returns huge values and slows down the layout significantly.

Here's the layout view:

How can I get a cumulative plant count in my TrackingData table?

Thanks!

Edited May 22, 2020 by RavingLuhn
Image links

[RavingLuhn]

To help illustrate what I'm trying to do, I've uploaded a demo file: https://drive.google.com/open?id=1-knf0y7rMgBFI8loBAX0RQW1EGvJ9TpM

My report layout has no body parts, so that might be what makes this more difficult to accomplish. Here's a view of the layout:

And here's what the report itself looks like:

The field labeled 'c_Cumulative Plants from Trackingdata' is a calculation field that's pulling a value from a summary field in a related table. Placed on this layout, the calculation field only shows the weekly total instead of the running cumulative total. My aim is to be able to divide s_Cumulative Seconds by that running total of plants. At the moment I don't have any idea how to get the running total of plants displayed on this layout. Any help would be appreciated!

[comment]

Still no clue what this is about. Why don't you simplify the question to the minimum necessary to understand the problem? 

I think you have a Parent and a Child tables (cannot tell which is which). I think you have some Value field in the Child table that is being summarized by a summary field. And your report is grouped by some value (cannot tell which) and showing only sub-summary values for each group. All of this is quite simple and clear.

What is entirely unclear is what role a summary field (I think it is a summary field, cannot tell for sure) defined in the Parent table is supposed to play in this. A summary field from a related table returns the summary of the related set. In a parent/child relationship, the related set contains only the parent record - so a summary field will always return the value of the summarized field in the parent record - which you can get directly, without going through a summary field.

Note also that any calculation that requires a sub-summary value of a summary field (defined in the Child table) as its input, must use the GetSummary() function - and in order for the GetSummary() function to work, the break field too must be defined in the same table.

 

[RavingLuhn]

32 minutes ago, comment said:

Still no clue what this is about. Why don't you simplify the question to the minimum necessary to understand the problem?

Sorry. My experience in asking help from a community gravitates between two extremes. People either want all the details or none of them; trying to work on communicating more clearly. Trying again: How do I compare cumulative output from one table to cumulative output in a different table?

The background: I have a database to track employee time spent on tasks per department, as well as metrics for different departments. This data resides in two different tables. My goal is to create a report that shows a weekly summary of total hours per department, whilst comparing those hours to the related outputs in a different table. I can do 95% of what I intend. The problem is that I want to compare cumulative hours to cumulative output; this will give me a running work pace for each week throughout the year. Since the summary fields that would generate those cumulative calculations reside in different tables I'm having difficulty figuring out how to lay everything out.

In the sample file from my post above, the parent table is 'Shipping Volume'. There's one record for each week that contains week number, year, output, and so on. It is related to the child table, 'TrackingData' by Year, week number, and department. I have a report layout based on the information from Tracking Data. It displays information in sub summary parts only; no body parts. I've tried many combinations, but I can't figure out how to display a cumulative output from the shipping volume table on the corresponding weeks of the tracking data layout.

The end goal here is to be able to divide cumulative seconds on the 'tracking data' table by cumulative output from the 'shipping volume' table to give me a running pace of seconds per unit.

Edited May 26, 2020 by RavingLuhn

[comment]

Ideally, you would provide a minimal but complete example. We are still a long way from there.

I don't understand what you mean by "cumulative" when it refers to a related table. As I said, referencing a summary field in a related table will return the summary of the related set. If you want a different result, you will need to start by establishing a found set in the parent table and setting a global field or variable to the value of the summary field in this table. Then move to the child table and produce your report, using the global field/variable for calculations that require the "cumulative" value obtained earlier.

This is assuming that the "cumulative" value is supposed to be the same for all records included in the report from the child table. 

 

Edited May 26, 2020 by comment

[RavingLuhn]

Point taken on the minimal complete example. I'll reference that for future instances.

I think this is where the biggest gap in communication is coming from:

4 minutes ago, comment said:

This is assuming that the "cumulative" value is supposed to be the same for all records included in the report from the child table.

What I'm trying to display is the cumulative total up to the current week of that year. Here is the pertinent data from my parent table:

'PlantCount' is the weekly output. I have a summary field in this table to calculate the annual cumulative output each week. So week 2 is the sum of weeks 1 + 2. Week three is the sum of weeks 1 through 3, etc.

My child table has detail records for hours per day:

I can use a calculation field to convert hours to seconds, and then a summary field to give me a running total of seconds per week on the report.

What I'm trying to do is get the cumulative total of seconds divided by the cumulative total of plants for each week of the report. It might wind up looking something like this:

Year	Week	PlantCount	Seconds	C_plants	c_seconds	Seconds per plant
2020	1	35,584	345,600	35,584	345,600	9.7
2020	2	103,508	418,212	139,092	763,812	5.5
2020	3	20,048	498,276	159,140	1,262,088	7.9

 

[comment]

I think you will need to define another relationship between your tables, a relationship that would allow each child record to "see" all the weeks between the report start date and up to the week of the child record. 

Alternatively, you would need an unstored calculation field in the child table to "copy" the value from the parent record and divide it by the number of records in the current group. Then use a running total summary field to get the "cumulated" value you seek. I think this may be similar to what you attempted to do, but found to be too slow. 

 

Edited May 26, 2020 by comment