Export notes and attached documents attached to companies, contacts and opportunities

Hi everyone,

My first question - and I promise I searched the forums and saw some related questions, but nothing that I could directly apply.

We would like to export all the notes and documents attached to each and any company, contact or opportunity (not leads) and we are hoping not to have to check each one separately - frankly we can't!

Excel would be the ideal platform for the export.

Is there a custom report that we can make to do this, or some other way to do it?

Thanks

teresa

Parents
  • 0

    Hi Teresa

    For Notes you will need to create a view like the following for use either as a report or direct SQL query. 

    SELECT *
    FROM Notes
    LEFT OUTER JOIN Company ON note_ForeignId = Comp_CompanyID AND Note_ForeignTableID = 5 AND Comp_Deleted IS NULL
    LEFT OUTER JOIN Person ON note_ForeignId = Pers_PersonID AND Note_ForeignTableID = 13 AND Pers_Deleted IS NULL
    LEFT OUTER JOIN Opportunity ON note_ForeignId = Oppo_OpportunityID AND Note_ForeignTableID = 10 AND Oppo_Deleted IS NULL
    WHERE Note_deleted IS NULL
    AND Note_ForeignTableId IN (5,10,13)

    For documents you would need to share out the Library folder for CRM as documents themselves are not held in the database, just the location of where to find the file, with documents rolling up to company level so you will get duplicates, I have taken that into consideration in my script below, else there would be 3 rows per attachment if they were uploaded to an opportunity (one for Company, one for person and one for opportunity itself)

    SELECT 'Company' AS [Record Level],comp_companyid, Comp_name, '' AS [Record Name], Libr_FilePath+Libr_FileName AS [File Name]
    FROM Company
    JOIN Library ON libr_companyid = comp_companyid AND Libr_Deleted IS NULL AND Libr_OpportunityId IS NULL ANd Libr_PersonId IS NULL
    WHERE Comp_Deleted IS NULL
    UNION ALL
    SELECT 'Person' ,comp_companyid, Comp_name, RTRIM(pers_firstname)+' '+RTRIM(pers_lastname), Libr_FilePath+Libr_FileName
    FROM Person
    LEFT OUTER JOIN Company ON Pers_CompanyId = Comp_CompanyId AND Comp_Deleted IS NULL
    JOIN Library ON Libr_PersonId = Pers_PersonId AND Libr_Deleted IS NULL AND Libr_OpportunityId IS NULL
    WHERE pers_Deleted IS NULL
    UNION ALL
    SELECT 'Opportunity' ,comp_companyid, Comp_name, Oppo_description, Libr_FilePath+Libr_FileName
    FROM Opportunity
    LEFT OUTER JOIN Company ON Oppo_PrimaryCompanyId = Comp_CompanyId AND Comp_Deleted IS NULL
    JOIN Library ON Libr_OpportunityId= Oppo_OpportunityId AND Libr_Deleted IS NULL
    WHERE Oppo_Deleted IS NULL

  • 0 in reply to Matthew Shaw

    Interesting, Hi Matthew, I'm new here at Sage 100 and It would be great if you can comment here on the documentation on how to run a report to solve that problem at the top or how to set up that query that you've mentioned. Thanks!

  • 0 in reply to Joshua Broqueza

    Hi Joshua

    Both of my items above are SQL views where you would create them in CRM under Admin | Customisation | Secondary Entity | Notes (for the first one) and Admin | Customisation | Company (for the second)

    Once created you can then go to Reports and create the two reports using the new source views 

  • 0 in reply to Matthew Shaw

    Got it. Is it the same when I need all the communications associated with a company? Especially the Email In/Out and attachments inside of it. 

  • 0 in reply to Joshua Broqueza

    Is this what you're looking for? My original script has been adjusted

    SELECT 'Company' AS [Record Level],comp_companyid, Comp_name, '' AS [Record Name],'' AS [Comms Type],'' AS [Associated With], Libr_FilePath+Libr_FileName AS [File Name]
    FROM Company (NOLOCK)
    JOIN Library (NOLOCK) ON libr_companyid = comp_companyid AND Libr_Deleted IS NULL AND Libr_OpportunityId IS NULL ANd Libr_PersonId IS NULL AND libr_communicationId IS NULL
    WHERE Comp_Deleted IS NULL
    UNION ALL
    SELECT 'Person' ,comp_companyid, Comp_name, RTRIM(pers_firstname)+' '+RTRIM(pers_lastname),'','', Libr_FilePath+Libr_FileName
    FROM Person (NOLOCK)
    LEFT OUTER JOIN Company (NOLOCK) ON Pers_CompanyId = Comp_CompanyId AND Comp_Deleted IS NULL
    JOIN Library (NOLOCK) ON Libr_PersonId = Pers_PersonId AND Libr_Deleted IS NULL AND Libr_OpportunityId IS NULL AND libr_communicationId IS NULL
    WHERE pers_Deleted IS NULL
    UNION ALL
    SELECT 'Opportunity' ,comp_companyid, Comp_name, Oppo_description,'','', Libr_FilePath+Libr_FileName
    FROM Opportunity (NOLOCK)
    LEFT OUTER JOIN Company (NOLOCK) ON Oppo_PrimaryCompanyId = Comp_CompanyId AND Comp_Deleted IS NULL
    JOIN Library (NOLOCK) ON Libr_OpportunityId= Oppo_OpportunityId AND Libr_Deleted IS NULL AND libr_communicationId IS NULL
    WHERE Oppo_Deleted IS NULL
    UNION ALL
    SELECT 'Communication', CmLi_Comm_CompanyId, comp_name, Comm_Subject, (SELECT capt_uk FROM Custom_Captions WHERE capt_code = comm_action AND capt_family = 'comm_action'),
    CASE WHEN CmLi_Comm_PersonId IS NOT NULL THEN '(P) '+RTRIM(pers_firstname)+' '+RTRIM(pers_lastname)+',' ELSE '' END
    +CASE WHEN comm_opportunityid IS NOT NULL THEN '(O) '+RTRIM(oppo_description) ELSE '' END
    ,Libr_FilePath+Libr_FileName
    FROM Communication (NOLOCK)
    LEFT OUTER JOIN Comm_Link (NOLOCK) ON CmLi_Comm_CommunicationId = Comm_CommunicationId
    LEFT OUTER JOIN Company (NOLOCK) ON CmLi_Comm_CompanyId = Comp_CompanyId
    LEFT OUTER JOIN Person (NOLOCK) ON cmli_comm_personid = pers_personid
    LEFT OUTER JOIN Opportunity (NOLOCK) ON Comm_OpportunityId = Oppo_OpportunityId
    JOIN Library (NOLOCK) ON Comm_CommunicationId = libr_communicationid
    WHERE libr_deleted IS NULL AND comm_deleted IS NULL

    and looks something like this in its output 

Reply
  • 0 in reply to Joshua Broqueza

    Is this what you're looking for? My original script has been adjusted

    SELECT 'Company' AS [Record Level],comp_companyid, Comp_name, '' AS [Record Name],'' AS [Comms Type],'' AS [Associated With], Libr_FilePath+Libr_FileName AS [File Name]
    FROM Company (NOLOCK)
    JOIN Library (NOLOCK) ON libr_companyid = comp_companyid AND Libr_Deleted IS NULL AND Libr_OpportunityId IS NULL ANd Libr_PersonId IS NULL AND libr_communicationId IS NULL
    WHERE Comp_Deleted IS NULL
    UNION ALL
    SELECT 'Person' ,comp_companyid, Comp_name, RTRIM(pers_firstname)+' '+RTRIM(pers_lastname),'','', Libr_FilePath+Libr_FileName
    FROM Person (NOLOCK)
    LEFT OUTER JOIN Company (NOLOCK) ON Pers_CompanyId = Comp_CompanyId AND Comp_Deleted IS NULL
    JOIN Library (NOLOCK) ON Libr_PersonId = Pers_PersonId AND Libr_Deleted IS NULL AND Libr_OpportunityId IS NULL AND libr_communicationId IS NULL
    WHERE pers_Deleted IS NULL
    UNION ALL
    SELECT 'Opportunity' ,comp_companyid, Comp_name, Oppo_description,'','', Libr_FilePath+Libr_FileName
    FROM Opportunity (NOLOCK)
    LEFT OUTER JOIN Company (NOLOCK) ON Oppo_PrimaryCompanyId = Comp_CompanyId AND Comp_Deleted IS NULL
    JOIN Library (NOLOCK) ON Libr_OpportunityId= Oppo_OpportunityId AND Libr_Deleted IS NULL AND libr_communicationId IS NULL
    WHERE Oppo_Deleted IS NULL
    UNION ALL
    SELECT 'Communication', CmLi_Comm_CompanyId, comp_name, Comm_Subject, (SELECT capt_uk FROM Custom_Captions WHERE capt_code = comm_action AND capt_family = 'comm_action'),
    CASE WHEN CmLi_Comm_PersonId IS NOT NULL THEN '(P) '+RTRIM(pers_firstname)+' '+RTRIM(pers_lastname)+',' ELSE '' END
    +CASE WHEN comm_opportunityid IS NOT NULL THEN '(O) '+RTRIM(oppo_description) ELSE '' END
    ,Libr_FilePath+Libr_FileName
    FROM Communication (NOLOCK)
    LEFT OUTER JOIN Comm_Link (NOLOCK) ON CmLi_Comm_CommunicationId = Comm_CommunicationId
    LEFT OUTER JOIN Company (NOLOCK) ON CmLi_Comm_CompanyId = Comp_CompanyId
    LEFT OUTER JOIN Person (NOLOCK) ON cmli_comm_personid = pers_personid
    LEFT OUTER JOIN Opportunity (NOLOCK) ON Comm_OpportunityId = Oppo_OpportunityId
    JOIN Library (NOLOCK) ON Comm_CommunicationId = libr_communicationid
    WHERE libr_deleted IS NULL AND comm_deleted IS NULL

    and looks something like this in its output 

Children
No Data