While working with a customer I found a new way of totaling records when there is a Many-to-Many relationship. This method doesn’t use variables or subreports so it is quicker and simpler than other methods.

Example using the Xtreme data:
A customer linked to orders is a One-to-Many relationship (one customer to many orders). A customer with orders who also gets credits has two different One-to-Many relationships. Combine those 3 tables in one report and you get a Many-to-Many relationship. This is because there is no connection between an order and a credit.

If I only needed to total one of these (orders or credits) it would not be difficult. If you have taken my Advanced class, or if you have read my Expert’s Guide to Totals, you might recognize the method.  First you group the duplicates together (say by grouping on Invoice ID). Then you create a Running Total field that totals the order amount and an ‘Evaluate’ set to be ‘on change of group’. But you can only do one or the other with this method, not both at the same time.

What I realized is that if you group by customer and then by order, each order group of duplicates will contain exactly one set of credits. So, if you take the total credits from the first order of each customer you can also get a total of credits without duplicates.

So I created a summary field that calculates the first (minimum) order number of each customer.  Then I created a running total of the credit amount and set the evaluate to be a formula using this condition:

{Orders.Order ID} = Minimum ({Orders.Order ID},{Customer.Customer ID})

Now the only Credits that get included in the credit running total are from the first order within each customer group. This eliminates all the duplicate credits from the total without disrupting the original running total for the orders.

If you have a similar Many-to-Many situation and need help deploying a method like this, give me a call.