Background
In the current integration of MAS with Sage CRM, phone extensions created in MAS are not automatically visible in Sage CRM. This article shows how to use the Dummy Field trick in Sage CRM to make the MAS phone extension visible in the Sage CRM Company Top Content screen. The reason for adding the extension to the Company Top Content screen and not the Phone screen is because the Phone screen is not open for editing. The Company Top Content screen makes a good second choice because it already contains the company phone number.
Prerequisites and Assumptions
1) You need to have a SQL connection to your MAS data. One way to do this is by ODBC:
http://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2010/05/10/connecting-to-an-external-odbc-database-e-g-sage-erp-mas-90.aspx, and another way to do it is by creating a linked server: http://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2010/05/10/connecting-to-a-sage-mas-erp-90-database-as-a-linked-server-within-ms-sql-server-2008.aspx
2) This example was tested with MAS integrated with Sage CRM 7.0d. It may need tweaks or modifications to get it to work with other versions of the software.
How to do it
1) Create a view in Sage CRM linking the Sage CRM data with the MAS data.
The phone extension field in MAS is called TelephoneExt and is stored in the AR_Customer table.
The MAS data in this example is in a Linked Server called MAS90_LINK.
The view to create is:
CREATE VIEW vCompaniesTelephoneExtension as select TelephoneExt, vCompany.* from vCompany left join MAS90_LINK...ar_customer on comp_mas_ardivisionno = ar_customer.ARDivisionNo AND comp_mas_customerno = ar_customer.CustomerNo where comp_mas_ardivisionno is not null and comp_mas_customerno is not null
This view joins records on the Company table in Sage CRM with the AR_Customer table in MAS. It uses the CustomerNo and DivisionNo as keys. It only selects customer records that are linked between Sage CRM and MAS. The fields in the view are the same as in the vCompany view except that TelephoneExt has been added, and this is the field that we need.
Edit the view by replacing MAS90_LINK with your correct MAS database name.
To create the view, log into Sage CRM as administrator.
Go to Administration -> Customization -> Company -> Views
Click New to create a new view.
Enter the view in the View Script, and the name of the view as vCompaniesTelephoneExtension, and click Save.
You now have a new view in SageCRM.
2) Create a Dummy Field in Sage CRM, which will be overwritten with the value of the MAS Telephone Extension.
Go to Administration -> Customization -> Company -> Fields. Click New to create a new field with these values:
Entry Type: Text
Column Name: comp_telephoneext
Caption: Phone Ext
Max Length: 1
Entry Width: 1
3) Make the field read only. This stops the field being editable in Sage CRM.
Click on the Field Security button for the new Phone Ext field.
Deselect the Write Access check box and click continue.
4) Add the new field to the company top content screen, and some script to overwrite it with the MAS telephone extension for the customer.
Go to the screens tab, and open the CompanyTopContent screen for editing.
Select the new Phone Ext field and add it to the screen.
With the field still highlighted copy the following script into the create script box:
var Argument = "comp_companyid='"+CRM.GetContextInfo("company","comp_companyid")+"'";
var Record = CRM.FindRecord('Company, vCompaniesTelephoneExtension',Argument);
if (!Record.eof)
{
var FieldTitle = 'Phone Extension';
var FieldValue = Record('TelephoneExt');
if (FieldValue == 'undefined') FieldValue = '';
Caption = FieldTitle ;
Caption += ": ";
Caption += FieldValue;
Caption +"";
Caption +"";
}
The script uses the Phone Ext from the new view that we created to overwrite the value of the new dummy field.
Click Save
Final View
The Phone Extension is now visible in the Company Top Content Screen.
