Avoid duplicates in Summation with Details report

Hi Nicolas A,

There is not a SELECT DISTINCT option in the reporting tool, so the target record result appears repeated for each related record returned by the database query, just as you have observed.

 The way to avoid the duplicates is not to reference the related module, and one way to avoid this is to pull the data into a reportable field in the target module. This field does not need to be exposes in the record's layout, but does need to be reportable to work for these purposes.

Since you have reports, I assume you also have calculated fields. If you are using Sugar version 7.8 or newer, calculated fields has a countConditional() function that might help in this goal without requiring any code-level development.

What version of Sugar are you using?

Hi Patrick

I'm using Sugar 7.11.1.0 which brings these possibilities.

However, just to be clear: You would recommend changing the "MailChimp Activity" field which is currently storing the number of opens as a calculated field - correct?

Best

Nicolas

Hi Nicolas A,

Would you be willing to share the queries from your current configuration of the report?

Here is how to get those:

http://support.sugarcrm.com/Documentation/Sugar_Versions/7.11/Ent/Application_Guide/Reports/#Show_Query 

I hesitate to make any specific recommendation without first better understanding how the data is stored in your instance.

Also, please keep in mind that SugarCRM's Technical Support team can help you with an issue like this as well if you are interested in filing a support case. Since your instance is hosted by SugarCRM, they can log directly into your user interface and database to gather details and help you with an issue like this.

Hi Patrick

Honestly, I'm certainly not quite comfortable with sharing these publicy…I filed a case at the Support team. 

However, thanks for reaching out and taking the time!

Best

Nicolas

Thank you for filing the support case, Nicolas A. I have provided you a note on that case incorporating the query data and explaining that my initial idea to workaround product behavior would not work in this scenario.

As I mentioned initially, reports do not have a DISTINCT option for queries. This is a documented request in SugarCRM's case portal here: SugarCRM  

Without the ability to add DISTINCT to report queries, which would require custom development that might not be compatible with SugarCRM cloud hosting, I am unaware of a workaround for this specific use case.