I have the following two tables (simplified) which are a one to many relationship by 'order_no'

1) Order with fields order_no, date, purchaser

2) Items with fields order_no, fruit, variety, boxes, box_wt, lb_cost

A single order could have multiple items such as 2 varieties of oranges and 2 varieties of apples. I would like to sum the number of boxes, total weight and total cost by 'fruit'. I have tried to add the fields to the 'Order' table and using the IF() function but it calculated the overall totals for both oranges and apples, not by 'fruit'. I am relatively new to FM 10 (started learning several months ago but got pulled away). What is the simplest way to accomplish this? Create a new table with the subtotals?

Thanks a bunch.

What is the simplest way to accomplish this?

Summarize the line items by fruit.

Do you want #boxes per fruit across orders or just within one order?

I think if you just want a report you can create a summary field in items=total of #boxes. Then when you sort your items by fruit, each group will be just one fruit, and the summary will be total of # boxes for each group (ie fruit).

If you want results that are more dynamic than that (ie don’t require the sort/preview), the only way I know how is to have yet a third table that is called “Fruit” and matches items by fruit::fruit=item::fruit. In fruit you would have a calc that =sum(items::#boxes)

I need to summarize by fruit (oranges or apples, in this case) within an order, not across all fruits in the order.

Thanks for the suggestions. I'll try both.