Report on Combined Note records for Custom Entity

By | January 10, 2020

As we all know Sage CRM provides lots of functionality to users to track their business data. For this type of scenario, most useful feature is “Reports”. The reports are very useful for mangers to see growth of work in a certain period of time. User can design their reports as per user preference. Recently, one user wanted their custom entity’s notes added in reports but in one row.

New Stuff:Data Resource unavailable for Gadgets of Custom Entity

Suppose there is one custom entity name as Machine under Company. There is multiple notes for the same machine record. 


If user wants to see all the notes against the machine record. But he end up creating a view for the report that gets generated based on each note record instead of displaying combined notes for the machine record.

In order to combine all the notes against the machine record, user need to create below SQL statement.

select *, STUFF((
SELECT top 20 ', ' + note_note
FROM notes where Note_ForeignTableId = 10243 and Note_ForeignId = mach_MachineID
), 1, 1, '') as Notes
from Machine where mach_Deleted is null

Now, refer the above SQL statement and create a view for the report, In order to create source view for report navigate to  path Administration -> Customization -> Machine,  also don’t forget to check a checkbox name as “Reports View“as shown in below screenshot.

To create Source report in Sage CRM, refer below link:
Create Simple Sage CRM report to view Opportunities generated from Outbound Call list

Once you are done run the report now you will see that all the notes are combined into one report column, which is easy to understand.

Updated Report
Updated Report

Hope this helps.

About Us

Greytrix – a globally recognized and one of the oldest Sage Development Partner is a one-stop solution provider for Sage ERP and Sage CRM organizational needs. Being acknowledged and rewarded for multi-man years of experience and expertise, we bring complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third party add-on development and implementation competence.

Greytrix has some unique integration solutions developed for Sage CRM with Sage ERPs namely Sage X3Sage IntacctSage 100Sage 500 and Sage 300. We also offer best-in-class Sage ERP and Sage CRM customization and development services to Business Partners, End Users and Sage PSG worldwide. Greytrix helps in migration of Sage CRM from Salesforce | ACT! | SalesLogix | Goldmine | Sugar CRM | Maximizer. Our Sage CRM Product Suite includes addons like  Greytrix Business ManagerSage CRM Project ManagerSage CRM Resource PlannerSage CRM Contract ManagerSage CRM Event ManagerSage CRM Budget PlannerGmail IntegrationSage CRM Mobile Service Signature and Sage CRM CTI Framework.

Greytrix is a recognized Sage Champion Partner for GUMU™ Sage X3 – Sage CRM integration listed on Sage Marketplace and Sage CRM – Sage Intacct integration listed on Sage Intacct Marketplace. The GUMU™ Cloud framework by Greytrix forms the backbone of cloud integrations that are managed in real-time for processing and execution of application programs at the click of a button.

For more information on our integration solutions, please contact us at We will be glad to assist you.