Avoid duplicates in Summation with Details report

Hi All

To synchronize my MailChimp with SugarCRM leads, I'm using the plugin SugarChimp which works quite well.

One key functionality that I wanted to use is the creation of performance reports about opens and clicks within the SugarCRM report module.

For that reason, I set up a "Summation with Details" report with grouping based on "Activity Type" which is "Open", "Click" and "Send" and display columns are "Name" and "Account". The data runs smoothly into the rows and columns, however, I have a big issue:

The report displays each interaction of "Open" or "Click" as a dedicated entry which means if a lead opens our newsletters 3 times, I get him reported in 3 separated rows.

Is there any way to force the report to only show every entry once? I already contacted SugarChimp support but they can't help since it's a limitation coming from the reports module of SugarCRM.

Best

Nicolas

Parents
  • 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.

Reply
  • 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.

Children
No Data