Conditional summing with multiple contacts

[davidmo]

I am using CR 8.5 with Mas90 to send out a letter to all our custmers showing their payment history over the past 8 quarters.

The letter shows the individual payments in the detail section and the total in the group footer section.

In the group footer section used a conditional sum to show the total: sum([invoice.detail], [vendorhistory.contact])

Problem is for those customers that have multiple contacts it gives the incorrect total.

Example:
Detail Section
Invoice1 $100
Invoice2 $100
Invoice3 $100

Should show $300 in group section.

Instead it shows $600 because that customer has 2 contacts.

Any thoughts?

thanks.
davidmo

 

[CR85user]

This looks like a linking issue - Assuming you have for example to tables you are workign from.

customersinfo
invoicesinfo

If these are linked on a unique value such as customerID - Again i'm using an example only, then an equal join between these tables will pull all invoices in the 2nd table relating to that ID.

What you need to do is use a unique primary key to connect the two tables and use a left outer join between them in the visual linking.

Try it out and let us know how you get on.

If you are still receiving multiple records then we can review it again - Please state if possible {table.field} names (even if sample ones) or example data so we can assist further in finding the problem.

Cheers

J

 

[Madawc]

If you aren't suppressing any detail lines, then you should be able to right-click on the field, choose Insert and then Summary to get the right total.

If you are suppressing details, then do the same but choose Running Total. Make the running total conditional on whatever you use to suppress the details.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Abslag]

I am guessing because you have contact info you are pulling address/contact data from your customer master and joining that information with your payment history and then summing. So if there are 3 contacts all with the same customer number then it will join the payment history to all 3 and triple your results. You need to define the DEFAULT on the customer and exclude all other contacts so when you do your join, it does not include multiples.

Your other option is if you join more specifically like cust # = cust# and contact id = contact id then you should also avoid duplication. That way though, you would get payment history sum for cust 1 contact 1 and payment history sum for cust 1 contact 2 which is probably not what you are looking for.

does that help?

 

[lbass]

What kind of suppression are you using to avoid displaying the duplicate rows? What is confusing is that your formula seems to indicate that you are grouping on the contact field, not the customer field, so that if there were no suppression and the contact field was responsible for the duplication, you would see something like:

contact 1
Invoice1 $100
Invoice2 $100
Invoice3 $100
$300
contact 2
Invoice1 $100
Invoice2 $100
Invoice3 $100
$300

And the summary would be the same (and correct for your purposes) in each contact group footer. So what is your actual group field?

If you show us a sample of unsuppressed detail level data, we can show you how to use a running total for the summary.

-LB

 

[davidmo]

thanks everyone.

It was a linking problem. Used a different link between the two tables and it worked.