Export lead records including comment log

Hello SugarClub.

Does anyone know if its possible to export my records in the lead module including the comment logs.

At this moment I have 11 users who each have 7 filters in their lead module.

Im looking to export all records as shown in the sugarCRM. 

- Record fields as company name, first name, last name, phone, email etc. which i know how to export.

But when i mark all records, click mass update and afterwards export. I dont get any option to customize what data i would like to export. It only give me very few raw data from the list view.

A big part of our process is in our comment log where we have A LOT of data that i would like to preserve. 

Im looking to export all data from each record in any file type so it can be uploaded and merged with another tool we are using. 

Thank you in advance. 

Parents
  • Hi ,

    If you or your company does not have expertise to leverage the API mentioned in 's suggestion, another alternative is to create a custom query in Sugar's Advanced Reports module. Once a custom query is generated, you can export out the results of the comment log entries for associated leads. Here is a query that will give you all comment log entries related to existing leads in your CRM:

    SELECT 
        cl1.date_entered, 
        u1.user_name created_by, 
        cl1.entry, 
        clr1.record_id lead_id 
    FROM 
        commentlog cl1 
    INNER JOIN 
        commentlog_rel clr1 ON cl1.id = clr1.commentlog_id 
    INNER JOIN 
        leads l1 ON clr1.record_id = l1.id 
    LEFT JOIN 
        users u1 ON cl1.created_by = u1.id 
    WHERE 
        cl1.deleted = 0 AND clr1.deleted = 0 AND l1.deleted = 0 AND clr1.module = 'Leads' 
    ORDER BY 
        clr1.record_id ASC;

    Please note that the comment log entry text will contain abstract references if users mention another user or record in the comment log text. For instance, if someone tagged another user in the comment log, the text will have "@[Users:<users database GUID>]" in place of where the visual comment log would show that user's name. The same syntax is followed when linking other records in the comment log (e.g. @[<module name>:<record database GUID>]).

    Chris

Reply
  • Hi ,

    If you or your company does not have expertise to leverage the API mentioned in 's suggestion, another alternative is to create a custom query in Sugar's Advanced Reports module. Once a custom query is generated, you can export out the results of the comment log entries for associated leads. Here is a query that will give you all comment log entries related to existing leads in your CRM:

    SELECT 
        cl1.date_entered, 
        u1.user_name created_by, 
        cl1.entry, 
        clr1.record_id lead_id 
    FROM 
        commentlog cl1 
    INNER JOIN 
        commentlog_rel clr1 ON cl1.id = clr1.commentlog_id 
    INNER JOIN 
        leads l1 ON clr1.record_id = l1.id 
    LEFT JOIN 
        users u1 ON cl1.created_by = u1.id 
    WHERE 
        cl1.deleted = 0 AND clr1.deleted = 0 AND l1.deleted = 0 AND clr1.module = 'Leads' 
    ORDER BY 
        clr1.record_id ASC;

    Please note that the comment log entry text will contain abstract references if users mention another user or record in the comment log text. For instance, if someone tagged another user in the comment log, the text will have "@[Users:<users database GUID>]" in place of where the visual comment log would show that user's name. The same syntax is followed when linking other records in the comment log (e.g. @[<module name>:<record database GUID>]).

    Chris

Children