Creating your own Import Scripts using the COM API

3 minute read time.

Please read the article "Some thoughts on Importing Data" as background.

The previous article discussed the different programming techniques that can be used for importing data into Sage CRM.

  • Web Services
  • COM Based Mechanisms
  • .NET API
  • Direct Data Interaction

The COM Bases Mechanisms are either

  • ASP application extensions
  • External COM applications

Below are two example scripts that were written using ASP pages.

In these examples I have used the Record object rather than the Query Object.

The Record object is much better for the importation of data as the following articles discuss.

Example 1: Simple Import of Opportunities

This is a script that shows how the record object can be used to create the a set of opportunities.

Note: The source data is represented by an simple array.

<%

[code language ="javascript"]
var intCompanyId = CRM.GetContextInfo("company","comp_companyid");
var intPersonId = CRM.GetContextInfo("company","comp_primarypersonid");
var intUserId = CRM.GetContextInfo("user","user_userid");
var intUserChannelId = CRM.GetContextInfo("user","user_primarychannelid");

var arrMyOppos = new Array("one","two","three","four","five","six");

var myRecord
for (x in arrMyOppos)
{
myRecord = CRM.CreateRecord("opportunity");
myRecord.oppo_primarycompanyid = intCompanyId;
myRecord.oppo_primarypersonid = intPersonId;
myRecord.oppo_assigneduserid = intUserId;
myRecord.oppo_channelid = intUserChannelId;

myRecord.oppo_type = "Mix";
myRecord.oppo_product = "Training";
myRecord.oppo_source = "Referral";

myRecord.oppo_stage = "Lead";
myRecord.oppo_status = "In Progress";

myRecord.oppo_description = arrMyOppos[x];
myRecord.oppo_note = arrMyOppos[x];
myRecord.SaveChanges();
}

Response.Redirect(CRM.URL(184));
[/code]

%>

Example 2: Import Company and Person data, including Address and Phone records

This is more complex as it involves creating a company record and for each company the associated person, address and phone records which make up the complete Company entity.

The data source in the example is assumed to be a linked table within Sage CRM. So the 'Suppliers' table exists in another database and has been linked to Sage CRM using the features under

Administration -> Advanced Customization -> Tables and Databases

Furthermore in this example the source data is from a single table.


<%


//Variable and Objects
var supplierRecord;
var companyRecord;
var comp_name;
var HTTPstart;
var firstSpace;
var personRecord;
var personname;
var addressRecord;
var addresslinkRecord;
var businessphoneRecord;
var faxphoneRecord;
 
// The Supplier Table is a linked into Sage CRM from an external database.
supplierRecord = CRM.FindRecord('suppliers','');
 
while (!supplierRecord.eof) 
{
 
// Add company data
///////////////////////////
companyRecord = CRM.CreateRecord('company');
companyRecord.Comp_PrimaryUserId = CRM.GetContextInfo('user','user_userid');
 
comp_name = supplierRecord.companyname;
 
companyRecord.Comp_Name = comp_name;
companyRecord.Comp_Type = 'Supplier';
companyRecord.Comp_Status = 'Active';
companyRecord.Comp_Source = 'Import';
companyRecord.Comp_PhoneNumber= supplierRecord.Phone;
companyRecord.Comp_FaxNumber = supplierRecord.Fax;
companyRecord.SaveChanges(); //SaveChanges to have the record persist in database
 
//Add person data
////////////////////////
personRecord = CRM.CreateRecord('person');
personRecord.Pers_CompanyId = companyRecord.comp_companyid;
personRecord.Pers_PrimaryUserId = CRM.GetContextInfo('user','user_userid');
 
//break name up and ensure correct handling of apostrophes in name
firstSpace = supplierRecord.ContactName.indexOf(' ');
personname = supplierRecord.ContactName;
personRecord.Pers_FirstName = personname.substring(0, firstSpace);
personRecord.Pers_LastName = personname.substr(firstSpace+1);
 
personRecord.Pers_Title = supplierRecord.ContactTitle;
personRecord.SaveChanges(); //SaveChanges to have the record persist in database
 
// Add link from person to company
////////////////////////
person_linkRecord = CRM.CreateRecord('person_link'); // either an CRM table or externally referenced table
person_linkRecord.PeLi_PersonId = personRecord.pers_personid;
person_linkRecord.PeLi_CompanyID = companyRecord.comp_companyid;
person_linkRecord.SaveChanges(); //SaveChanges to have the record persist in database
 
// Set Person and Default Company Contact
//////////////////////
companyRecord.Comp_PrimaryPersonId = personRecord.pers_personid;
 
// Add Address Data
////////////////////////////
addressRecord = CRM.CreateRecord('address');
//cheap and dirty way of getting the data in to avoid truncating
addressRecord.Addr_Address1 = supplierRecord.Address.substring(0,40);
addressRecord.Addr_Address2 = supplierRecord.Address.substr(41);
addressRecord.Addr_City = supplierRecord.City;
addressRecord.Addr_State = supplierRecord.Region;
addressRecord.Addr_Country = supplierRecord.Country;
addressRecord.Addr_PostCode = supplierRecord.PostalCode;
addressRecord.SaveChanges(); //SaveChanges to have the record persist in database
 
// Add Link from Address to Person and Company
//////////////////////////
addresslinkRecord = CRM.CreateRecord('address_link');
addresslinkRecord.AdLi_AddressId = addressRecord.Addr_AddressId;
addresslinkRecord.AdLi_CompanyID = companyRecord.comp_companyid;
addresslinkRecord.AdLi_PersonID = personRecord.pers_personid;
addresslinkRecord.AdLi_Type = 'Business';
addresslinkRecord.SaveChanges(); //SaveChanges to have the record persist in database
// Add Link from Address to Company
//////////////////////////
addresslinkRecord = CRM.CreateRecord('address_link');
addresslinkRecord.AdLi_AddressId = addressRecord.Addr_AddressId;
addresslinkRecord.AdLi_CompanyID = companyRecord.comp_companyid;
addresslinkRecord.AdLi_Type = 'Business';
addresslinkRecord.SaveChanges(); //SaveChanges to have the record persist in database
 
//Add Business Telephone
////////////////////////////
businessphoneRecord = CRM.CreateRecord('phone');
businessphoneRecord.Phon_Number = supplierRecord.Phone;
businessphoneRecord.Phon_Type = 'Business';
businessphoneRecord.Phon_PersonID  = personRecord.pers_personid;
businessphoneRecord.Phon_CompanyID = companyRecord.comp_companyid;
businessphoneRecord.SaveChanges();
 
//Add Fax Telephone
////////////////////////////
faxphoneRecord = CRM.CreateRecord('phone');
faxphoneRecord.Phon_Number = supplierRecord.fax;
faxphoneRecord.Phon_Type = 'Fax';
faxphoneRecord.Phon_PersonID  = personRecord.pers_personid;
faxphoneRecord.Phon_CompanyID = companyRecord.comp_companyid;
faxphoneRecord.SaveChanges();
 
// Update Company and Person Records with Address and Phone Data
/////////////////////////
companyRecord.Comp_PrimaryPersonId = personRecord.pers_personid;
companyRecord.Comp_PhoneNumber = supplierRecord.Phone;
companyRecord.Comp_FaxNumber = supplierRecord.fax;
companyRecord.Comp_PrimaryAddressId = addressRecord.Addr_AddressId;
 
personRecord.Pers_PhoneNumber = supplierRecord.Phone;
personRecord.Pers_FaxNumber = supplierRecord.fax;
personRecord.Pers_PrimaryAddressId = addressRecord.Addr_AddressId;
companyRecord.SaveChanges(); //SaveChanges to have the record persist in database
personRecord.SaveChanges();
////////////////////////////
supplierRecord.NextRecord();
}
Response.Redirect(CRM.URL('supplierlist.asp'));

%>