Using Related Information in a Chart
I am having a problem understanding how the new charts work with totaled summary data. I have them working in a layout with their own data but I can't get it to work correctly with related data.
I made a simple database with two tables: aisle and fruit.
Aisle only has an AisleNumber field (for IDs).
Fruit has a AisleNumber field, class, and quantity. Fruit also has a summary field, TotalQtybyClass: Total of Qty. When sorted by Fruit::Class. Running Total, Restart summary for each sorted group. Summarize repetitions: Individually.
Aisle is related to Fruit by the AisleNumber to AisleNumber fields. In the relationship, Fruit is set to always sort by Class.
So, one record in aisle: AisleNumber =1.
Three records in Fruit:
Aisle Number=1, class=banana, Qty=2
Aisle Number=1, class="orange", Qty=1
Aisle Number=1, class="banana", Qty=1
If I make a new blank layout associated with fruit, then set up a bar chart with x axis Fruit::Class and y axis Fruit::TotalQtyByClass, and tell it to Use Data from Current Found Set, and check Show data points for groups of records when sorted. I get a bar chart. Unsorted I see three bars: bananas at 2, orange at 3, and bananas at 4. When I sort the table by Class, I get what I want: bananas shows 3 and orange shows 1.
What I want to do is to have this chart show up in form view in the layout for the Aisle. But in that case, I have to alter the chart:
The x and y axis are the same but I say Use Data From: Related Records. Related Table: Fruit.
When I look at the chart it see each related record in its own bar: bananas shows 2, bananas shows 3, and orange shows 1. This happens even when I tell the chart to Sort related records by Class.
Is there no way to have the chart show summary data from a related table with a Total summary field? It would be really handy to have a chart on each Aisle page showing a total quantity of all items in the aisle. But I can't seem to get the chart to show me that.
Any help would be great.