Creating Groups on External Tables in Sage CRM (E.g. Sage accounting systems)

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Creating Groups on External Tables in Sage CRM (E.g. Sage accounting systems)

  • Comments 8
  • Likes

The requirement:

The requirement was to be able to use data from an external table in a group definition.  This will allow data from a Sage accounting system to be used to build groups of Person and Companies that can then be used for outbound emails, and within the Interactive Dashboard as data sources for List Gadgets.

I have created a link to tables in the external database.

Administration -> Advanced Customization -> Tables and Databases

My example database is called PanoplyTech, and is based on the Northwind Database. But here it could be any ERP database.

Sage CRM

  • Company (FK: comp_panoplytechcompanyid)


  • Contacts (PK: companyid)

My example is very simple and the Company entity in Sage CRM is linked to the table in the external system (Contact) using the foreign key field.

All groups are based on Views. 

Creating the View

The view is created under the company entity.  External Entities can not have views created for them directly as Sage CRM assumes that all views are created in the Sage CRM database.

Administration -> Customization -> Company

The view correctly joins the Person and Company records.

[code language="sql"]
CREATE VIEW vCompanyANDPanoplyData
SELECT     RTRIM(ISNULL(dbo.vPersonPE.Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_LastName, '')) AS Pers_FullName, 
RTRIM(ISNULL(dbo.vCompanyPE.Comp_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(dbo.vCompanyPE.Comp_PhoneAreaCode, '')) 
+ ' ' + RTRIM(ISNULL(dbo.vCompanyPE.Comp_PhoneNumber, '')) AS Comp_PhoneFullNumber, 
RTRIM(ISNULL(dbo.vCompanyPE.Comp_FaxCountryCode, '')) + ' ' + RTRIM(ISNULL(dbo.vCompanyPE.Comp_FaxAreaCode, '')) 
+ ' ' + RTRIM(ISNULL(dbo.vCompanyPE.Comp_FaxNumber, '')) AS Comp_FaxFullNumber, RTRIM(ISNULL(dbo.vPersonPE.Pers_PhoneCountryCode, '')) 
+ ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_PhoneAreaCode, '')) + ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_PhoneNumber, '')) 
AS Pers_PhoneFullNumber, RTRIM(ISNULL(dbo.vPersonPE.Pers_FaxCountryCode, '')) + ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_FaxAreaCode, '')) 
+ ' ' + RTRIM(ISNULL(dbo.vPersonPE.Pers_FaxNumber, '')) AS Pers_FaxFullNumber, dbo.vCompanyPE.Comp_EmailAddress, 
dbo.vCompanyPE.Comp_PhoneCountryCode, dbo.vCompanyPE.Comp_PhoneAreaCode, dbo.vCompanyPE.Comp_PhoneNumber, 
dbo.vCompanyPE.Comp_FaxCountryCode, dbo.vCompanyPE.Comp_FaxAreaCode, dbo.vCompanyPE.Comp_FaxNumber, 
dbo.vCompanyPE.Comp_CompanyId, dbo.vCompanyPE.Comp_PrimaryPersonId, dbo.vCompanyPE.Comp_PrimaryAddressId, 
dbo.vCompanyPE.Comp_PrimaryUserId, dbo.vCompanyPE.Comp_Name, dbo.vCompanyPE.Comp_Type, dbo.vCompanyPE.Comp_Status, 
dbo.vCompanyPE.Comp_Source, dbo.vCompanyPE.Comp_Territory, dbo.vCompanyPE.Comp_Revenue, dbo.vCompanyPE.Comp_Employees, 
dbo.vCompanyPE.Comp_Sector, dbo.vCompanyPE.Comp_IndCode, dbo.vCompanyPE.Comp_WebSite, dbo.vCompanyPE.Comp_MailRestriction, 
dbo.vCompanyPE.Comp_CreatedBy, dbo.vCompanyPE.Comp_CreatedDate, dbo.vCompanyPE.Comp_UpdatedBy, 
dbo.vCompanyPE.Comp_UpdatedDate, dbo.vCompanyPE.Comp_TimeStamp, dbo.vCompanyPE.Comp_Deleted, dbo.vCompanyPE.Comp_LibraryDir, 
dbo.vCompanyPE.Comp_ChannelID, dbo.vCompanyPE.Comp_SecTerr, dbo.vCompanyPE.Comp_WorkflowId, dbo.vCompanyPE.Comp_UploadDate, 
dbo.vCompanyPE.comp_SLAId, dbo.vCompanyPE.Comp_PrimaryAccountId, dbo.vCompanyPE.comp_intforeignid, dbo.vCompanyPE.comp_intid, 
dbo.vCompanyPE.comp_intlastsyncdate, dbo.vCompanyPE.comp_promote, dbo.vCompanyPE.comp_customerstartdate, 
dbo.vCompanyPE.comp_panoplytechcompanyid, dbo.vPersonPE.Pers_EmailAddress, dbo.vPersonPE.Pers_PhoneCountryCode, 
dbo.vPersonPE.Pers_PhoneAreaCode, dbo.vPersonPE.Pers_PhoneNumber, dbo.vPersonPE.Pers_FaxCountryCode, 
dbo.vPersonPE.Pers_FaxAreaCode, dbo.vPersonPE.Pers_FaxNumber, dbo.vPersonPE.Pers_PersonId, dbo.vPersonPE.Pers_CompanyId, 
dbo.vPersonPE.Pers_PrimaryAddressId, dbo.vPersonPE.Pers_PrimaryUserId, dbo.vPersonPE.Pers_Salutation, dbo.vPersonPE.Pers_FirstName, 
dbo.vPersonPE.Pers_LastName, dbo.vPersonPE.Pers_MiddleName, dbo.vPersonPE.Pers_Suffix, dbo.vPersonPE.Pers_Gender, 
dbo.vPersonPE.Pers_Title, dbo.vPersonPE.Pers_TitleCode, dbo.vPersonPE.Pers_Department, dbo.vPersonPE.Pers_Status, 
dbo.vPersonPE.Pers_Source, dbo.vPersonPE.Pers_Territory, dbo.vPersonPE.Pers_WebSite, dbo.vPersonPE.Pers_MailRestriction, dbo.vPersonPE.Pers_CreatedBy,
dbo.vPersonPE.Pers_CreatedDate, dbo.vPersonPE.Pers_UpdatedBy, dbo.vPersonPE.Pers_UpdatedDate, 
dbo.vPersonPE.Pers_TimeStamp, dbo.vPersonPE.Pers_Deleted, dbo.vPersonPE.Pers_LibraryDir, dbo.vPersonPE.Pers_ChannelID, 
dbo.vPersonPE.Pers_UploadDate, dbo.vPersonPE.pers_SecTerr, dbo.vPersonPE.Pers_WorkflowId, dbo.vPersonPE.Pers_AccountId, 
dbo.vPersonPE.pers_intforeignid, dbo.vPersonPE.pers_intid, dbo.vPersonPE.pers_intlastsyncdate, dbo.vPersonPE.pers_promote, 
dbo.vPersonPE.pers_ConflictResDate, dbo.vPersonPE.pers_departmentcode, dbo.Address.Addr_AddressId, dbo.Address.Addr_Address1, 
dbo.Address.Addr_Address2, dbo.Address.Addr_Address3, dbo.Address.Addr_Address4, dbo.Address.Addr_Address5, dbo.Address.Addr_City, 
dbo.Address.Addr_State, dbo.Address.Addr_Country, dbo.Address.Addr_PostCode, dbo.Address.Addr_CreatedBy, dbo.Address.Addr_CreatedDate, 
dbo.Address.Addr_UpdatedBy, dbo.Address.Addr_UpdatedDate, dbo.Address.Addr_TimeStamp, dbo.Address.Addr_Deleted, 
dbo.Address.Addr_ChannelID, dbo.Address.addr_uszipplusfour, dbo.Address.addr_intforeignid, dbo.Address.addr_intid, 
dbo.Address.addr_intlastsyncdate, dbo.Address.addr_promote, PanoplyTech.dbo.CONTACTS.COMPANYNAME, 
FROM         dbo.vCompanyPE INNER JOIN
dbo.vPersonPE ON dbo.vCompanyPE.Comp_PrimaryPersonId = dbo.vPersonPE.Pers_PersonId INNER JOIN
dbo.Address ON dbo.vCompanyPE.Comp_PrimaryAddressId = dbo.Address.Addr_AddressId LEFT OUTER JOIN
PanoplyTech.dbo.CONTACTS ON dbo.vCompanyPE.comp_panoplytechcompanyid = PanoplyTech.dbo.CONTACTS.COMPANYID

Once the view has been created it can used to create Groups.

I called my group "Company and Panoply Data".  The view created above allows all fields from the company, person, address and some of fields from the external table.

You can add search criteria.  I did as I wanted my group use the data from the external database to limit the Company records returned.

The group is based on the Company Entity so all actions are assumed to take place against the Company entity.


When carrying out a Mail Merge you can include any fields from the External Entity that are in the view associated with the group. 

  • Great & Thanks Jeff,

    I have some minor questions/comments however:

    a. In case ERP & CRM in same server, I got it up & running without need to declare database connection and table connection. Do you think it's rigth also?

    b. The data coming from this External Entity can be added also to screens & lists? I have tested it but  I cannot find out (I will search more deeply)

    c. The groups and companies will also accomplish Zone access controls?

    Thanks again for this kind of post to make easier integration to other systems!

    Best Regards, and happy new year!

  • Hi Jeff,

    I could add those ERP fields to screen & lists as well, so point b. answer should be YES.

    thanks again!

  • It would be nice to know how to get data from a secondary entity which is located on a different server like promised in the title of this article. Because our customers have the db's seperated and then this trick isn't working anymore!

  • Okay the solution is to make a link in sql server to the other server and use a full qualified name to select data from the other server (also with ODBC Connection possible).

  • Hi Jeff

    Thank you for your help.

    I have try the steps above, and it was OK.

    Next time I want to use the the exactly same view for making a same group with different name it is not available anymore. it's really strange that one time I can use the special view for making a group, but another time I can't.

    My external table is OEORDH in Accpac Database instead of Contacts table.

  • Hi Jeff

    Thank you for your help.

    I have try the steps above, and it was OK.

    Next time I want to use the the exactly same view for making a same group with different name it is not available anymore. it's really strange that one time I can use the special view for making a group, but another time I can't.

    My external table is OEORDH in Accpac Database instead of Contacts table.

  • I am not sure why that should be happening.  It might be that the view is now returning duplicate rows.

  • Thank you very much, your advice was really helpful.