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.
- Example 1: Simple Import of Opportunities
- Example 2: Import Company and Person data, including Address and Phone records
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.
- Using Code to Import Data in a on Premise Installation
- Some thoughts about Importing Outlook Contacts
- The eWareQuery Object (COM API)
- How can I easily import a large list of Selection list values into CRM?
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'));
%>