AnsweredAssumed Answered

Using Related Information in a Chart

Question asked by johnwall on Mar 10, 2010
Latest reply on Mar 11, 2010 by vicvega1972


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.