1 Reply Latest reply on Mar 11, 2010 7:43 AM by vicvega1972

    Using Related Information in a Chart

    johnwall

      Title

      Using Related Information in a Chart

      Post

      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.

       

      Thanks.