Creating your own Import Scripts using the COM API

Hints, Tips and Tricks

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

Creating your own Import Scripts using the COM API

  • Comments 4
  • Likes

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.

<!-- #include file ="sagecrm.js"-->

<%

[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.

<!-- #include file ="accpaccrmdpp.js"-->
<%


//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'));

%>

Comments
  • It's great, specially 2nd for fast adding new companies!

    However I guess this file structure is based for version 7 or earlier. We tried with 7.1 and something appears to be wrong.

    Do you have it updated for version 7.1?

    Thanks in advnace

  • I am sorry but I have not updated this for Sage CRM v7.1 BUT there maybe some good example code in a 7.1 component that I wrote that clones person records.

    community.sagecrm.com/.../19815.aspx

    Note:  You will need to be a member of the Developer Program to access this file.

  • Jeff..

    I am using this to import leads in 7.3.  I am having issues with the import stopping or failing because data in the field is too long for the field that the data is being imported into.  In the instances were this is happening, I am OK with the system truncating the data.   (This will not be permanent.  However, I am importing about 14,000 leads and the system does not seem to get past 100 records before it fails.)

    Is there something I can put in the page to allow truncation while this is processing?

    Any assistance you can provide would be greatly appreciated....Thanks!

  • Michele

    You can control how data is truncated.  For example

    addressRecord.Addr_Address1 = supplierRecord.Address.substring(0,40);

    If you are experiencing fails then this may be due to either the web transaction timing out or the SQL transaction timing out.

    You may have to redesign you page to 'chunk' the data - so handling no more than x records at a time.  So eat the elephant one mouthful at a time.