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)
PanoplyTech
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
AS
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,
PanoplyTech.dbo.CONTACTS.CONTACTNAME
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
[/code]
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.
Note
When carrying out a Mail Merge you can include any fields from the External Entity that are in the view associated with the group.