Sage CRM 2020 R2: How can I easily import a large list of Selection list values into CRM using the External COM API?

This question occurs when setting up CRM; you may find yourself with a very large number of selection list values that need to be added to the system. For example you may want to have every county or even major city to be loaded into CRM in the same way the addr_country field has it's country list displayed.

What I have used in the past is a data load table. Imagine that the data has been prepared in Excel. Then load the Excel spreadsheet into the data load table using Data Transformation Services (DTS). This table is not part of CRM so we don't have to work about how it's primary key is maintained.

Once the data is in the data load table I would run a script file that used the CRM API objects to read from the data load table and add the records into the custom_captions table.

For example the initial spreadsheet. I have assumed this is structured with the columns:

Capt_FamilyType,Capt_Family,Capt_Code,Capt_US

Here the last column is actually the language that is being used. Include extra languages where necessary.

The data that is in the Capt_code should not have spaces in the data, so "InProgress" not "In Progress".

I have assumed that I have copied the data into a table load table called 'NewCaptions' and that the columns are called:

Capt_FamilyType
Capt_Family
Capt_Code
Capt_US

Below is the code that you can use to carry out the import. I created this as a script file and ran this from the desktop.

var username = 'Admin'; 
var password = ''; 
var eWare = CRM= new ActiveXObject("eWare.CRM"); 
eWare.FastLogon = 3; //this prevents the meta data from loading. 

eWare.Logon(username,password); 

var strQuery = "select * from newcaptions"; 
var myQuery = CRM.CreateQueryObj(strQuery,""); 

myQuery.SelectSQL(); 

var captionRecord ;

while (!myQuery.eof) 
{ 
captionRecord = CRM.CreateRecord("custom_captions"); 
captionRecord.Capt_FamilyType = myQuery.FieldValue("Capt_FamilyType"); 
captionRecord.Capt_Family = myQuery.FieldValue("Capt_Family"); 
captionRecord.Capt_Code = myQuery.FieldValue("Capt_Code"); 
captionRecord.Capt_US = myQuery.FieldValue("Capt_US"); 

captionRecord.SaveChanges(); 
myQuery.NextRecord(); 
} 


Once the code has run, you will need to refresh meta data and then you will be able to use the newly created caption family as a selection list.

Anonymous
  • Nice. I've used a db table like that before and then written a SQL script to do the insert. We'll gloss over the "grey area of support" in doing that. On older systems I called the stored procedure to manage the creation of the ID. In newer systems, just an insert into the table works. I guess doing it the way shown here is supported as it is using the API in a supported way?